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
