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.
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.
