CODEWITHSUNDEEP
plsqloracle11gplsql-package
Adi
Adi

Reputation: 309

How to open, read and store to DB content of XLSX file with PLSQL?

I am trying to Open, Parse and save to Database the content from .xlsx file using PLSQL. Is this possible ? I have my files on the server, created the object for that path and gave rights rw for my user. Should I convert from XLSX to CSV using PLSQL? Is this possible or is there other way to deal with XLSX files ?

What I tried so far was this but I get exception

ORA-22288: file or LOB operation FILEOPEN failed

Tried to give rights on my object but it still not working.

Upvotes: 0

Views: 502

Answers (1)

MT0
MT0

Reputation: 167962

Assuming you only want to upload a handful of spreadsheets manually (as opposed to requiring regular or high-volume automated uploads) then the simplest way to insert rows from an excel file into a database is add a column to the table which generates as SQL insert statement.

For example:

If the excel sheet has data in columns A, B and C and a header line:

ROWS A        B       C
1    id       value1  value2
2    1          23.0  Smith
3    2          42.7  O'Brien
4    3          59.6  Jones

Then in cell D2 put:

="INSERT INTO table_name ("&A$1&","&B$1&","&C$1&") VALUES ("&A2&","&B2&",'"&SUBSTITUTE(C2,"'","''")&"');"

Then copy-and-paste the formula down into D3-D4 so that the table looks like:

ROWS A      B       c        D
1    id     value1  value2  
2    1        23.0  Smith    INSERT INTO table_name (id,value1,value2) VALUES (1,23,'Smith');
3    2        42.7  O'Brien  INSERT INTO table_name (id,value1,value2) VALUES (2,42.7,'O''Brien');
4    3        59.6  Jones    INSERT INTO table_name (id,value1,value2) VALUES (3,59.6,'Jones');

Finally, copy the generated SQL statements and run it in SQL Plus.

Upvotes: 2

Related Questions