Multiple delimiters with flex

anmol_koul

Hi All,

I am having problems with data having two delimiters. To give you a background, i am using data exported from mongodb (repository) into vertica (analyticsdb).

So the data is basically csv but mongodb attaches double quotes to any string type columns.
My sample data looks like:

"hp","vertica","analyticsdb"
"ibm","DB2","RDBMS"
"Mongo","mongo","nosql"

Heres the thing,

when i am applying fdelimitedparser with delimiter ',', i get the data safe and sound but the double quotes are still there.
If i use delimiter ", the data is deformed to no extent.
Further i tries to remove the double quotes manually in vi but that results in addition of columns as the data was getting skewed.

This whole process was done using flex tables only.

Can some one help me out on this? i basically want to rid the data of the comma and the double quote.

Many thanks

Prasanta_Pal

vi /tmp/cpoy.txt

"hp","vertica","analyticsdb"
"ibm","DB2","RDBMS"
"Mongo","mongo","nosql"

<save>
Then login to DB

dbadmin=> create table copy_test ( a varchar(30), b varchar(30), c varchar(30));
dbadmin=> copy copy_test FROM '/tmp/copy.txt' DELIMITER ',' ENCLOSED BY '"' NULL as '' direct;
 Rows Loaded
-------------
           3
(1 row)

dbadmin=> select * from copy_test;

   a   |    b    |      c     
-------+---------+-------------
 ibm   | DB2     | RDBMS
 hp    | vertica | analyticsdb
 Mongo | mongo   | nosql
(3 rows)

anmol_koul

Hi Prasanta, this is quite helpful. Thank you.

Is there a way of doing it via flex tables? I have 25 tables and each table has a minimum of 50 columns so it would be pretty hectic defining those table manually.

Any ideas?