Re: Need help for import of text file

On Dec 15, 2012, at 1:06 PM, Andreas wrote:

> Hi,
>
> I need to import textfiles that have 5 columns but there is just blanks as delimitors.
> I could use COPY to read them but there is a time column that shows times as " h:mm.ss,ms" in the morning and "hh:mm.ss,ms" in the afternoon.
>
> Problem here is in the morning the first digit of the hour is shown as a blank so there are 2 blanks before the time so COPY misstakes this as an empty column and gets confused.
>
> Can someone point me in the direction of an COPY option I'm not aware of, or alternativly to some console tool that I can put in the batch before the import step and replace the 2 blanks with 1 blank.
>
> I use an OpenSuse server so some linux tool would do.
>
>
> regards
> Andreas
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

sed and awk are your friends.

You might consider some text processing prior to import. I do this a lot because I work with external datasets that require all kinds of massaging.

For example:

sed -e 's/^\s{2}/ /g' filename | psql DATABASE -c 'COPY table_name from STDIN'

the above will replace 2 spaces appearing at the front of the file with one space, then pipe the result to psql copy command that expects input from STDIN.

Hope that is helpful