Issues loading. txt file into Vertica via command shell



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


  • Options
    Navin_CNavin_C Vertica Customer

    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.

  • Options

    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.

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator

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




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

  • Options
    Navin_CNavin_C Vertica Customer

    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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file