Csv2db

Csv2db

2021, Feb 19    

Easy way to convert an excel file into a table in oracle database.

The process is following:

  1. Download the tool csv2db and unpack it You should end up with:

     csv2db1.4
     ├── csv2db.exe
     ├── Data
     │   └── test_file.xlsx
     └── dbconf.yaml
    
  2. Prepare the file -> Save as -> CSV UTF-8 -> give it a name (it will be the table name when finally created in database)

    01.excelCsvUTF8.png

    02.excelCsvUTF8.png

    The file needs to be saved in subfolder Data\ under your csv2db location.

    Depending on regional settings such CSV file can have either semicolon (as in my example) or comma as list separator (delimiter).

    You can double check if proper delimiter is chosen when you open newly created CSV file with Notepad:

    03.notepad.png

    You should see what kind of delimiter was taken by Excel:

    04.notepad.png

    In our case it’s a semicolon.

  3. prepare config file -> dbconf.yaml (you can edit it with Notepad, it’s just plain text file)

     server: cxdprd
     username: kcierpis  # replace with your username
     password: password
    
     delimiter: ";"
    
     sqlldr_options: direct=true
     #standardize: false
     encoding: utf-8
     handle_new_lines: true 
    

    Important values are server, username, password and delimiter

    server is your tns_ora entry for the server you want to target.

    05.tns.png

  4. right-click on the parent folder containing csv2db.exe program:

    06.cmd.png

    where you can execute the program:

     C:\csv2db1.4>csv2db.exe Data\test_file.csv
    
     csv2db release1.4
     "using dbconf2.yaml"
    
     delimiter: ";"
     "handling of new line characters enabled"
     "converting to utf8.... from utf-8"
     "converting into multiline capable..."
     Sql/ directory does not exist, creating...
     Ctl/ directory does not exist, creating...
     Log/ directory does not exist, creating...
     Dsc/ directory does not exist, creating...
     Bad/ directory does not exist, creating...
     encoding: utf-8
     standardize: true
       -> if you do not want to change the column names use
          standardize: false
          in your dbconf.yaml file
    
     duplicated column COL2 changed to COL2_2
    
     Table TEST_FILE with following columns (see if on separate
         lines) will be created:
     "COL1"
     "COL2"
     "COL2_2"
     "VERY_LONG_COLUMN_NAME_THAT_WON"
    
     Processing database: CXDPRD as KCIERPIS
    
     Finished:
     Run now test_file.bat
    

    From the above output you can see couple of things:

    -> there was a duplicate column with name COL2 which was replaced into COL2_2

    -> one column was too long (30 characters limit in oracle db) and hence was cut to meet that requirement

    -> couple of folders were created (one time only if not exist before) that are used by sql loader that will upload the prepared file into database

    Finally test_file.bat file was created that can be now executed.

     C:\csv2db1.4>test_file.bat
    
     C:\csv2db1.4>sqlplus kcierpis/password@cxdprd @Sql/test_file.sql
    
     SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 19 09:08:27 2021
     Version 19.9.0.0.0
    
     Copyright (c) 1982, 2020, Oracle.  All rights reserved.
    
     Last Successful login time: Thu Feb 18 2021 17:53:42 +01:00
    
     Connected to:
     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    
     Table created.
    
     Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
     C:\csv2db1.4>sqlldr userid=kcierpis/password@cxdprd control=Ctl/test_file.ctl log=Log/test_file.log bad=Bad/test_file.bad skip=1 direct=true
    
     SQL*Loader: Release 19.0.0.0.0 - Production on Fri Feb 19 09:08:31 2021
     Version 19.9.0.0.0
    
     Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
    
     Path used:      Direct
    
     Load completed - logical record count 3.
    
     Table KCIERPIS.TEST_FILE:
       3 Rows successfully loaded.
    
     Check the log file:
       Log/test_file.log
     for more information about the load.
    

    Now the table with name TEST_FILE was created in your username schema.

    You can now query your table to double check if data was properly inserted:

    09:18:18 SQL> select * from test_file;
       
    COL1                 COL2                 COL2_2               VERY_LONG_COLUMN_NAM
    -------------------- -------------------- -------------------- --------------------
    äößłóś               äößłóś               großartig            großartig
    żźćöäü               żźćöäü+              Łódź                 Łódź
                         +48 17 23456789
       
    +123                 +123+4                                    +123456789
    想 xiǎng. 小         position is タ行     メリークリスマス     何かNanika
                         ウ段
       
       
    Elapsed: 00:00:00.49
    09:18:19 SQL> 
    

    It looks good. Multibyte characters and newlines were properly processed!

ATTENTION If you get ORA-12541: TNS:no listener while executing .bat file it means there is a problem with your tns entry. Maybe tunnel is not set properly.

You can establish tunneling with this command in cmd:

ssh -N -L1522:mktcx5bi-scan.us.oracle.com:1521 kcierpis@nacismktl212.us.oracle.com

replace with your username. You will be asked for your enterprise system account password:

07.tunnel.png