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?
howto bulk load from CSV file with float field delimited by comma?
i have text file with next format
+2Hi 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,
Eugenia
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 TABLEHope this helps,
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)
Eugenia
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
