Skip to Main Content
  • Questions
  • How to load a xlsx file in to a table

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, sona.

Asked: July 08, 2016 - 10:44 am UTC

Last updated: April 28, 2017 - 10:51 pm UTC

Version: oracle 10.1.2

Viewed 10K+ times! This question is

You Asked

I have a xlsx file ,and i am using sql loader in toad to load in to table.I also have created control file for the same.

the file content is given below:
ID Name Employee name Designation Status
0000700 John,Sam John sam (000070) Sr.Developer, SLF Yes

I have more than 50000 of records of the same pattern whichi mentioned above

I need to load this in to table manually.

what delimeter should i put in the control file .
OPTIONS (skip = 1,ROWS=10000, READSIZE=1000000, BINDSIZE=1000000)
LOAD DATA
INFILE "sample.xlsx"
APPEND INTO TABLE tablename
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS

and Chris said...

If you want to load it using SQL Loader, you'll need to save it as a CSV first. Then the delimiter will be a commas, which you have.

Or you can load it as Excel using SQL Developer:

http://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 27, 2017 - 11:49 am UTC

Is there any way to load xls and xlsx file into oracle table.

i can't change the file to csv from xls or xlsx.
Connor McDonald
April 27, 2017 - 11:42 pm UTC

A more recent tool

Stew Ashton, April 28, 2017 - 9:17 am UTC

Marc Bleron wrote a PL/SQL routine to read .xlsx files. I have used it quite a bit since then.
https://odieweblog.wordpress.com/2016/06/21/reading-an-excel-file-xlsx-as-an-external-table/
The interface lets us work with files or LOBs! I think the external table interface really missed the boat here: having a LOB interface to an external table would allow us to upload the file using SQL*Net and use database security to control access, without having to worry about file system security issues. As far as I know, people who need to access LOBs have to work around this with DBFS.

Best regards, Stew
Connor McDonald
April 28, 2017 - 10:51 pm UTC

Yes, its a nice tool.

You know where the enhancement request page is Stew :-)

https://community.oracle.com/community/database/database-ideas