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

xkidxkid Vertica Customer
edited February 2021 in General Discussion

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!

Best Answer

  • marcothesanemarcothesane - Select Field - Administrator
    Answer ✓

    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
    ;
    

Answers

  • marcothesanemarcothesane - Select Field - Administrator

    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"
    
  • xkidxkid Vertica Customer

    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.

  • marcothesanemarcothesane - Select Field - Administrator
    You should be able to set your personal env variables as described.
    Script will follow
  • xkidxkid Vertica Customer

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file