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@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 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






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to