Re: Help with copy (loading TSV file into table as text)

On Mon, Oct 24, 2011 at 11:29 PM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
> On 24/10/2011 20:23, Allan Kamau wrote:
>> Hi,
>> I have a tab delimited file with over a thousand fields (columns)
>> which I would like to import into postgreSQL.
>>
>> I have opted to import the entire record (line) of this file into a
>> single field in a table, one table record per file line. Later split
>> the contents of the field accordingly into an array for further
>> processing and decomposition.
>>
>> I am using the COPY command as follows
>> #COPY a.t(raw_data)FROM '/data/tmp/t.txt';
>>
>> and it reports "ERROR:  extra data after last expected column".
>
> I'd guess that there's a character in the input which PG is interpreting
> as a column delimiter. Use the WITH DELIMITER option to set the expected
> delimiter to something that won't appear in the input.
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod(at)iol(dot)ie
>

Yes PG senses the tab and assumes it to be a field delimiter. I have
now used the '=' character as my "csv" delimiter after grepping the
file on '=' and seeing no lines were returned.

Out of curiosity, what would be the correct use of "FORMAT text"
option in COPY command. The command below does give me an error as
indicated in my previous email.

COPY a.t(raw_data)FROM '/data/tmp/t.txt' FORMAT text;

Allan.