Hi,
Good Morning!
DB Version info.
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
PL/SQL Release 12.1.0.2.0.
I have to come up with a Solution for the following scenario
I have an Excel File which we Open and Parse and generate Insert Statements Dynamically into tables based on each Excel Sheet. (1 Worksheet data will be parsed and inserted into an Equivalent Table)
The Excel Parser produces the data structure like this below.
Sheetnum
| Sheetname | Row_nr
| Col_nr
| Cell
| Cell_type
| Data_val | Formula
|
|---|
| 1 | employee | 1 | 1 | A1 | CHAR | emp_id | |
| 1 | employee | 1 | 2 | B1 | CHAR | firstname | |
| 1 | employee | 1 | 3 | C1 | CHAR | middlename | |
| 1 | employee | 1 | 4 | D1 | CHAR | lastname | |
| 1 | employee | 1 | 5 | E1 | DATE | hiredate | |
| 1 | employee | 1 | 6 | F1 | CHAR | email | |
| 1 | employee | 2 | 1 | A2 | CHAR | emp001 | |
| 1 | employee | 2 | 2 | B2 | CHAR | Andy | |
| 1 | employee | 2 | 4 | D2 | CHAR | Jones | |
| 1 | employee | 2 | 5 | E2 | DATE | 01-JAN-2018 | |
| 1 | employee | 2 | 6 | F2 | CHAR | ajones@somemail.com | |
| 1 | employee | 3 | 1 | A3 | CHAR | emp002 | |
| 1 | employee | 3 | 2 | B3 | CHAR | james | |
| 1 | employee | 3 | 3 | C3 | CHAR | conan | |
| 1 | employee | 3 | 4 | D3 | CHAR | doyle | |
| 1 | employee | 3 | 5 | E3 | DATE | 01-FEB-2018 | |
need your help in getting the logic to populate these data into an Oracle table ( say Employee) dynamically.
The Row number 1 is always header info, hence used LISTAGG function to create Column names as comma separated fields, wanted to useLISTAGG function for values list as well, but i could not as my Values may contain null, also the Excel Parser will not list Null values( its a Limitation).
the challenge is I have to create a comma seperated values based data_Val field from Row number 2 as they hold the value for the column headers.
emp_id,firstname,lastname,email cannot be null
The sample data above has 3 rows of data, but I may get more than 1000's of rows to loop.
I am not able to get a logic where magically I can build the insert statements as
INSERT INTO Column_list VALUES (values_list with Null as values at appropriate places where they are missing)
Say for example the Above three rows should yield 2 insert statements,
INSERT INTO (emp_id,firstname,lastname,middlename,hiredate,email) VALUES ('emp001','Andy',NULL,'Jones','01-JAN-2018','ajones@somemail.com');
INSERT INTO (emp_id,firstname,lastname,middlename,hiredate,email) VALUES ('emp002','James',Conan,'Doyle','01-JAN-2018',NULL);
Thanks in advance