howto bulk load from CSV file with float field delimited by comma?

Eugene_Klimov

i have text file with next format

project;31.10.2013 23:54;83616133;1448474;;1277;Visa / Mastercard (RUB);RUB;384,00;384,00;3,84;1,00;8,45;2,20;0,00;371,71;96,80;0,00;0,00;

how i can convert 384,00 to 384.00 in Vertica COPY statment?

[Deleted User]

Hi Eugene, 

You have to declare the source as filler and then transform it to a numeric with .. Maybe there is a simpler way to do it, but it works just fine. Look the example, 

dbadmin=> create table test( c1 numeric, c2 numeric, c3 numeric); CREATE TABLE 
dbadmin=> \! vi /tmp/test.txt
dbadmin=> \! cat /tmp/test.txt 1233,34;123123,55;1231,23 12312,2342;1231;123,1231233,34;123123,55;1231,23
 dbadmin=> copy test  (v1 filler varchar(20),  v2 filler varchar(20),  v3 filler varchar(20),  c1 as to_number(replace(v1, ',','.')),   c2 as to_number(replace(v2, ',','.')),  c3 as to_number(replace(v3, ',','.') ))  from '/tmp/test.txt'  delimiter ';' ;   Rows Loaded -------------            2 (1 row) 
dbadmin=> select * from test;           c1           |           c2           |          c3           -----------------------+------------------------+----------------------   1233.339999999999918 | 123123.550000000002910 | 1231.230000000000018  12312.234200000000783 |   1231.000000000000000 |  123.123000000000005 (2 rows)

Hope this helps, 

Eugenia