Hi, I have multiple excels in a Folder.
I each of the excel I have three sheets I have to read the data from the three sheets and feeding in a ExcelTempate where once feeded the value I have to convert the same template in .PDF format. Kindly suggest.
screenshot of the folder.
same for rest of the excel file.
Like in Excel101 I have
Note: In each excel I have sheet1, sheet2 & sheet3 (3 sheets) I have to read the data from all 3 sheets and feeding into an exceltemplate then save that template in .pdf format.
@Palaniyappan @HareeshMR @Lahiru.Fernando
does the template has got the same column structure as these excel sheets have
Yes, template will have the fixed structure only bro.
Just confuse to read the data from the three excel sheets, because my data is in three sheets where I have to read and feed into the template then save as pdf.
@balkishan Guess you need to iterate sheets as well, by dynamically adding a incrementing variable.
Yes I have three sheets. like Sheet1, Sheet2 & Sheet3.
And I have to read all sheets then feed the data into Excel template then save that template in pdf format.
Can I use the three Read Range in single excel scope,
Like above, right me if I am wrong.
then store these in dt1, dt2 & dt3.
So sorry for the late replies bro… just held up at long meetings time to time…
Right, so from the point you are now at,
You need to read three sheets of each excel sheet. I have one question here…
- Are the data in these three excel sheets are similar structurally? Or is it different?
No problem bro. No same structure only.
So, let me show you a workflow for this… hold on and give me a minute.
@ If its the same structure, you can use two merge data table activities to merge three datatables into one
sorry didn’t understand bro
After reading these three sheets I have to feed the data into ExcelTemplate and then save that template in a .pdf format.
same I have to do for all the excel files in a folder.
here is a template which you can use for this…
MultipleSheets.xaml (9.9 KB)
You know how to iterate through the files @balkishan
Then use Get Workbook sheets to get the sheets and iterate through them, within the loop , use read range to read the data of that sheet and then
coming to the template, I hope the template is same always, so, if it doesn’t have any images, save it as a text file and place the values from the data tables looping everytime
then use write text file activity to save the text as PDF
Just forgot to mention one thing… in the place where I added the comment,
use a for each row loop to loop throuogh the MergedData table because it has all the data in all three sheets of the file.
Within the for each row loop, add the adding stuff to your template…
This is probably the best one i feel, but why have you used Clone() ?
Good question… why used clone is:
in the initial run, the MergedDT i have introduced, does not know the the structure of the RawData data table right? so if you do a direct assign or merge DT, it would probably fail because the structure of the two are different
The clone, will clone the structure of the RawData to MergedDT so you can easily merge the data from one to another…
hope these steps would help you resolve this
–create a datatable with BUILD DATATABLE ACTIVITY with same column structure as this excel has and get the output with a variable of type datatable named Finaldt
–use a clear datatable activity and pass the variable Finaldt as input
–now use a excel application scope and pass the file path of that excel
–from that scope activity we can get the output with the property workbook named out_workbook
–now use a assign activity next to this scope and mention like this
arr_sheets = out_workbook.GetSheets
–now use a for each loop and pass the above variable arr_sheets as input and inside the loop use READ RANGE ACTIVITY and mention the sheetname as item and get the output with a variable of type datatable named outdt
–now use a merge datatable activity and in source mention as outdt and in destination mention as Finaldt in the property panel of merge datatable activity
–now we can use this Finaldt to write into the excel template with Append range activity
–next to this for each loop use a clear datatable activity and mention the input as Finaldt
so the structure would be like this
–build datatable activity
–clear datatable activity
–excel application scope
–inside that use a assign activity to get the sheetnames as array
–use a for each loop and pass the array as input
–inside the loop use a read range activity
–use merge datatable activity
–use a clear datatable activity and pass Finaldt as input outside this For each loop
hope this would help you
kindly try this and let know for any queries or clarification
I have one a column which has the same name in Sheet2 & Sheet3.