How to read multiple sheet’s from excel
How to read multiple sheet’s from excel
Step 1: Take a script and define a global variable $G_SHEET_NO and assign it as 1 and a sql command to delete the data from table before loading.
$G_SHEET_NO = 1;
sql(‘SOURCE_DB’, ‘delete TBL_TARGET_EXCEL’);
Step 2: While loop condition will be $G_SHEET_NO <= 3 (In my case there are 3 sheet’s you can give any number here or to make it dynamic use global variable and pass the value to that variable at run time.
Step 3: First script in above picture will have code like below:
$G_LIST_SHEET = ‘Sheet’||$G_SHEET_NO;
print( ‘LOADING DATA FROM :’|| $G_LIST_SHEET);
Note: we’ll take another variable here ‘$G_LIST_SHEET’ and print function here is to print the sheet no in trace log that which sheet we are loading right now.
Step 4: Then Workflow ‘WF_SHEET_LIST’ will have a Dataflow
Step 5: Here SHEET_LIST EXCEL file properties are –
Step 6: Now the last script in while loop will have code like –
$G_SHEET_NO = $G_SHEET_NO + 1;
So this will just increment the sheet no and next it will load data from second sheet then third and so on.
Tip: Format of data should be the same for all sheets you want to load data from.
Small and crisp, Good job Gokul !!!
Hi Gokul Gawande
Good explanation but i'm getting only last sheet data
have 4 sheets each have 3 records ,total 12 records but i'm getting only 4th sheet data
Hi Dilip,
Check if your target table is truncate and load or drop and recreate.
Regards,
Gokul
Hi Gokul,
I have a requirement to read mutliple excel files. I tried using wildcard but am getting error. Can you advise how to achieve this requirement.
Thanks
Hi Vinay,
I have the same requirement as yours. can u please explain how to read multiple excel files from a shared directory?
thanks in advance.
Hello Suneel,
Use wait_for_file function to get the name of files and process each file one by one using a loop. Pass a parameter in file name section of format editor. Your parameter will contain the name of file to process.
Let us know if it helps, if not then will help you with screenshots.
Thanks,
Rajan
Hi Rajan,
thanks for your quick reply.
i want to load the files all at a time using wild characters * in file name and I want to put the shared folder path in directory place. does it work? if not please provide me the alternate solution .
Thank you.
Hello Suneel,
I cannot replicate your scenario as MS office isn't configured on my job server right now. But I think you can use *.xls method to extract the list of files having the same structure and load the data in from all those files in one go.
If this doesn't work then go for this method:
This is another way through which you can process your files present in the shared path.
Let us know if it works.
Thanks,
Rajan
Sounds Interesting to me. I will try this!
thank you
I am rare to SCN, so sorry to late reply.
I noticed reading multiple excel files does not seem to be possible in BODS, but text and csv files works for wild card file extension.
Hi Gokul,
Short and clear explanation. Nice one 🙂
Hi Gokul Gawande,
Helpful information. I have the same requirement but my worksheet is having names such as sales, Promotion, TimeCluster and so on. Here you provided the extract from sheet1, sheet2 etc.
How to load if we have a worksheet having different names?
Thanks,
Murali
Excellent Post Gokul!! Keep it up
Hi Gokul,
It is an informative blog. I have a doubt that what if there are number of sheets available.
So in that case we can not count manually and assign it in while condition.
Will you please share how can that be achieved by declaring the variable and assigning that variable a value of total number of sheets presents?
This will automate the process instead of counting the sheets manually,
Thanks.