> Jan Christian Dittmer wrote:
>
>
> > Thank you very much!
> > You have remind me that the our server runs under Linux and not under Windows as our clients :-)
> > So indeed I can use a sed-pipe construct to switch '.' and ','.
> > But wait, there is just another problem then. Our date format is also german :-( "DD.MM.YY" or
> > "DD.MM.YYYY". So if I just exchange '.' and ',' the date will be unreadable for the import :-(
> > The (current) file is 1.4 GB so it will take ages to let awk chew on it I guess.
> >
>
>
> If all fails you could copy into a another table where you use
> text columns and then translate afterwards via to_date() and
> to_number() / replace().
>
For my tuppence worth, this is the approach I take. Copy into a
sandbox table first, as the only reliable thing about external input
is it won't be as it's supposed to be.
This way you can copy everything into varchar columns, then run any
number of regexes over the data to ensure it's perfect, then do:
insert into livetable select * from temptable (with the appropriate
casts in place).
Bit of a tangent, but Is there any possibility of SQL injection via
data provided to copy?
Will T
-
