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

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?


  • Options
    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, 


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file