Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Multiple delimiters with flex

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

Comments

  • 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)
  • 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?

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.