Issues loading. txt file into Vertica via command shell

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';

Comments

  • 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.

  • 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_KnicelyJim_Knicely Administrator

    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 ...

  • 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.

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.