Importing an .xls file into Oracle can be achieved with sqlldr.exe, a tool provided with the Oracle installation. Sqlldr stands for SQL Loader and it accepts a .csv file as an argument for importing into an Oracle table. MS Excel data is easily saved as .csv, so you can use this file in conjunction with a required custom control file to coordinate the import into the Oracle table. You must also have the target table present to import into to.
- Skill level:
Things you need
- File editor (ex: Notepad)
- Microsoft Excel
Convert .xls to .csv. Open .xls in MS Excel. Delete any Column Headers. Go to "File, "Save As" and change "Type" to .csv format. Provide a new path and name and click "Save."
Create the Control File. Open Notepad. Type the following and save as "...\filename.ctl."
OPTIONS (ROWS=1, LOG='...\filename_load.log') LOAD DATA INFILE '...\filename.csv' INTO TABLE TABLENAME FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (FIELD1, FIELD2, FIELD3, FIELDx TERMINATED BY WHITESPACE)
Tablename is the name of your Oracle table, "....\" represented by the fully qualified path where your files reside, "filename_load.log" is the log file to confirm success or indicate processing errors and "FIELDS1...x" are the names of the .csv columns in order from left to right.
Create an Oracle table, if necessary. At the Oracle SQL prompt, type or execute a file containing:
CREATE TABLE TABLENAME(
FIELD1 (plus field format ex: VARCHAR2(30),
FIELD2 (plus field format ex: VARCHAR2(30),
FIELD3 (plus field format ex: VARCHAR2(30),
FIELDx (plus field format ex: VARCHAR2(30));
"FIELDS" here must match in name and order the fields in the Step Two Control File. The control file will match each .csv column into it's corresponding Oracle table column.
Execute the Sqlldr.exe. Find Sqlldr.exe. (Ex: C:\Oracle\product\10.1.0\Client_1\BIN\sqlldr.exe.) Open Notepad, type the path: .../sqlldr.exe user/password@service_name control='.../filename.ctl.'
Save as filename.bat and double-click to execute.
This process assumes your Oracle logon credentials are known (ex: user/password/service_name). The instructions passes to sqlldr.exe are contained in the Control File, as indicated in StepTwo.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for