Skip to main content

Multiple delimiters with flex

  • November 25, 2014
  • 2 replies
  • 14 views
anmol_koul
Forum|alt.badge.img+1
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

    2 replies

    Prasanta_Pal
    Forum|alt.badge.img
    • Prasanta_Pal
    • Participating Frequently
    • November 26, 2014
    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
    Forum|alt.badge.img+1
    • anmol_koul
    • Author
    • Participating Frequently
    • November 26, 2014
    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?
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement