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

On 24/10/2011 22:39, Allan Kamau wrote:
> 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;

I honestly don't know - it looks fine to me. The docs suggest that it'd
give an error on pre-9.0 Postgres; but you said you're on 9.1...

<shrug>.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie