Skip to main content

Issues loading. txt file into Vertica via command shell

  • December 28, 2016
  • 4 replies
  • 20 views
DaveDB

Hello,

 

I am trying to load a simple tab delimited file into a Vertica table using cmd line. The text file is on my local machine. When I execute the following command (see below), no rows are loaded. The message just says "0 rows loaded".

 

Is it possible that this command is looking on the C drive in the Vertica environment and not my local machine? If that is the case, how would I load a text file from my local machine to the Vertica box? Instead of saying "C:\text.txt", what would the code be?

 

Also, is there a way to BCP a text file into VERTICA table? If so, could someone please provide the code for me? I am new to Vertica and I am under a stiff time constraint.

 

Thank you all in advance!

 

VERTICA MACHINE=> copy TABLE from local 'C:\test.txt' WITH DELIMITER 't';

    4 replies

    Navin_C
    Forum|alt.badge.img+2
    • Navin_C
    • Participating Frequently
    • December 30, 2016

    Use this :

     

    copy TABLE from local 'C:\test.txt' WITH DELIMITER E'\t';

     

    For loading tab delimited files we need to use special delimiter represented by ASCII value E'\t'

     

    Addintional options for Ascii values range from E'\000' to E'\177' for different delimiters.

    DaveDB
    • DaveDB
    • Author
    • New Participant
    • January 3, 2017

    Ok thanks, but I am now getting a different error. It seems that I need to enter a source somewhere.

     

    VERTICA=> copy VERTICA BOX.SCHEMA.TABLE from local 'C:\'test.txt' WITH DELIMITER E'\t';

     

    ERROR 2754: COPY requires a data source; either a FROM clause or a WITH SOURCE
    for a user-defined source

     

    Any help would be appreciated.

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • January 3, 2017

    Get rid of the "VERTICA BOX." text and that extra apostrophe in the file name.

     

    Try:

     

    copy SCHEMA.TABLE from local 'C:\test.txt' WITH DELIMITER E'\t';

     

    But make sure to replace SCHEMA with the schema name and TABLE with the table name ...

    Navin_C
    Forum|alt.badge.img+2
    • Navin_C
    • Participating Frequently
    • January 4, 2017

    You can't be using DB reserved words like VERTICA in your SQL, unless its a valid SQL.

    Vertica's Query planner will mis interpret it as another command.

     

    Also, make sure your tables_names if have spaces, you quote it in Double quotes.

    Vertica will not understand a table name if it has spaces.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement