How to copy data from a CSV file to a table in Vertica Data Base by using command line (CMD)?

xkid

Hey folks!

I was wondering if there is any way to copy a data from csv file by using command line (CMD) to a table in 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 remotley:

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, so i don't know if I can create something like that to use it in Vertica.

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!

marcothesane

Cleanest way would be:
A batch file:

REM copyexample.bat
@ECHO OFF
set VSQL_USER=dbadmin
set VSQL_PASSWORD=password
set VSQL_HOST=marcos-ubuntu
set VSQL_DATABASE=sbx
"C:\Program Files\Vertica Systems\VSQL64\vsql.exe" -f copyexample.sql

and the copyexample.sql sql script you're calling in the batch file:

-- copyexample.sql
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
;

marcothesane

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"

xkid

Thanks for reply @marcothesane
Seems a nice way to try, just one more quick question, do you know if I can create a notepad file or something like that instead of create Environment Variables? The other problem here is that I'm on a server and don't have administrator permission and that requirement could take so long.

I hope you can help me.

marcothesane

You should be able to set your personal env variables as described.
Script will follow

xkid

thanks a lot @marcothesane I'm gonna try it, your solution makes sense for me, best regards!