Thanks for the question, sona.
Asked: July 08, 2016 - 6:22 am UTC
Last updated: July 08, 2016 - 12:09 pm UTC
Version: oracle 10.1.2
Viewed 1000+ times
You Asked
How to load a xlsx file in to a table,
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 Connor said...
That depends on what you mean by "load" ?
- store the whole thing as blob ?
- uncompress it and store the XML ?
- extract the rows and columns ?
=========================
The database doesn't know *natively* how to interpret an xlsx file.
So I'd suggest the following:
1) grab a 3rd party tool that lets you extract the data into csv from the command. Some examples here
http://stackoverflow.com/questions/5256733/convert-xlsx-file-to-csv-using-batch 2) Rather than use sql loader, use an external table definition, because then you can use the pre-processor option. This way you present the xlsx file, the preprocessor converts the content to csv, and then the external table can read that.
Hope this helps.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment