CODEWITHSUNDEEP
databasecmdcommand-linevertica
Xkid
Xkid

Reputation: 409

How to copy csv file to a table in Vertica Data Base by using command line?

I was wondering if there is any way to copy a data from csv file by using command line (CMD) to a table from Vertica Data Base. I've searching for a while and I don't find something that works for me. I want to create something similar to this I've made, is a batch file to copy a data from CSV file to a table in postgreSQL Data Base:

C:
cd "C:\pgsql\bin"
psql -h suggestedorder.postgres.database.azure.com -d DataAnalytics -U dev_ext@suggestedorder -c "\copy planning.sap_mx_env_me2n_zaaptr_zpaitr_envase_transito (planta, folio, des_proveedor, fecha_carga, hora)from 'C:\Users\geradiaz.MODELO\Desktop\Envase\Transitos\Outputs\Transitos.csv' with (format csv, header)

(To avoid CMD asked for my password I created The password file)

Do you know guys, if there is any way to make something similiar? do I need to create The Password file like in posgreSQL first? I hope you can help me or tell me where I can find info about how to create this properly.

Have a nice day, Best regards!

Upvotes: 0

Views: 433

Answers (1)

marcothesane
marcothesane

Reputation: 6721

So you are in a DOS box of a Windows system.

Add an environment variable to your account, by hitting the magnifying glass next to the Windows button at the far low left corner of your Windows screen, and type "Environment variables". You will see a list of hits, among which "Edit Environment Variables for your Account" and "Edit the system environment variables". Click on either of the two, depending on whether you want it for yourself or for the whole Windows system. Add 4 variables with the [New ...] button, for example:

Variable         Value
VSQL_USER        dbadmin
VSQL_PASSWORD    dbadmin_s_password
VSQL_HOST        <ip address or host name>
VSQL_DATABASE    <database name, for example vmart>

and edit the variable:

Variable         Value
PATH             <what is already there>;C:\Program Files\Vertica Systems\VSQL64

then go:

cd /d <the directory where your data file resides, not necessarily vsql.exe>
vsql -c "COPY planning.sap_mx_env_me2n_zaaptr_zpaitr_envase_transito ^
(planta, folio, des_proveedor, fecha_carga, hora) ^
FROM LOCAL 'C:\Users\geradiaz.MODELO\Desktop\Envase\Transitos\Outputs\Transitos.csv' ^
DELIMITER ',' SKIP 1 EXCEPTIONS 'con' REJECTMAX 3"

Upvotes: 2

Related Questions