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

# csv # oracle