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