Read data from mutiple xls files and load data in DB table

[Rohan2020]

Hello Everyone,

I need one suggestion on implementing one functionality.  I am reading data from multiple excel  file using macros and loading data in table. The header of the of the the of log files are changing but need to concatenate data at row level and store in table. Getting issue when trying to map these excel column to DB table as it is changing for each file.

E.g Sample 1 excel input file

Column 1	Column 2	Column 3	Column 4
a	b	c	d
e	f	g	h

Sample 2 excel input file

Column 5	Column 6	Column 7	Column 8
i	j	k	l

Structure of database table to load data from above two sample file.

Log_Filename	Value 1	Value 2
Sample 1	a|b|c|d|e
Sample 1	e|f|g|h
Sample 3	i|j|k

[Qiu]

@Rohan2020 
Not the Macro part though, just an idea may help.

[Rohan2020]

As I have multiple sheet using macros to read the excel file. Need to concatenate at the row level and also same need to be stored in DB but as the column names are dynamic it is not getting mapped. In total i have 65 files to process but file have different header.

[Qiu]

@Rohan2020 

In my workflow, all the data header will be forced to be Value 1, 2, 3 and so on.

[Rohan2020]

In DB tables need to load data in this format in separate rows as below . Please suggest on this.

Log_Filename	Value
Sample 1	a|b|c|d|e
Sample  1	e|f|g|h
Sample 3	i|j|k

[Qiu · Accepted solution]

@Rohan2020 
Seems I did something not necessary😁

[Rohan2020]

 I am getting this error message.

[grazitti_sapna]

Hi @Rohan2020 ,can you give this a try, if it works for you?

As you are reading the data using macro and facing an issue of different column names. Try using a directory tool by keeping all the files at same location unless you are using a different source to read them.

Also change the location of the directory tool to your directory.

If this works kindly mark this post as solution.

Thanks.

[Rohan2020]

I am using macro as the directory is changing based on the batch run. Also when storing data in Oracle DB need to unique number generator. Also while appending the value in case last column is null can we append NULL.