Csv2db
Easy way to convert an excel file into a table in oracle database.
The process is following:
-
Download the tool csv2db and unpack it You should end up with:
csv2db1.4 ├── csv2db.exe ├── Data │ └── test_file.xlsx └── dbconf.yaml
-
Prepare the file -> Save as -> CSV UTF-8 -> give it a name (it will be the table name when finally created in database)
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:
You should see what kind of delimiter was taken by Excel:
In our case it’s a semicolon.
-
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.
-
right-click on the parent folder containing csv2db.exe program:
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: