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
soCOPY 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
canput 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@postgresql.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
requireall 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
commandthat expects input from STDIN. 

Hope that is helpful