The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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


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

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

  • 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