Thread: Problems importing data from plain text file

Problems importing data from plain text file

From
Mário Gamito
Date:
Hi,

I have this plain text file with about 5000 lines.
Each line may have 4 or 5 fields, all delimited with a tab.

I've made a table named t_zip_codes with 5 fields.

When i run (in postgres command line) the command

COPY t_zip_code FROM zip_codes.txt;

it aborts as soon as it reaches a line with only 4 fields in the text
file, because the table t_zip_codes have 5 fields.

How can i solve this annoyance ?
All my fields are character varying.

Any help would be appreciated.

Warm regards,
Mário Gamito

Re: Problems importing data from plain text file

From
"gnari"
Date:
From: "Mário Gamito" <gamito@netual.pt>

> I have this plain text file with about 5000 lines.
> Each line may have 4 or 5 fields, all delimited with a tab.
>
> I've made a table named t_zip_codes with 5 fields.
>
> When i run (in postgres command line) the command
>
> COPY t_zip_code FROM zip_codes.txt;
>
> it aborts as soon as it reaches a line with only 4 fields in the text
> file, because the table t_zip_codes have 5 fields.
>
> How can i solve this annoyance ?

by adding the 5th field where missing ? with something like:
  perl -pi.bak -e's/$/\t/ if tr/\t/\t/<4' zip_codes.txt

should be easy with sed, as well
(or just with your favorite editor)

gnari