I would like to ask for help as I’m stuck with my task related to excel documents merging.
I’m trying to merge multiple excel documents, which mostly contain same column headers, but some columns names and position vary from one excel document to another and I’m trying to put all document data in one single excel file.
In this case if document 1 contains column with name “AAA” and document 2 also contains column “AAA” then just add rows with data one after another, but if document 3 doesn’t contain column “AAA” then leave those fields blank and move to next column.
Also if document 4 contains column with name “DDD” which wasn’t in document 1,2,3 then add data to that column(not from the beginning(*1 cell) but from the new row to not mix data from previous documents) as all columns are known and already is added to temp table.
And this is the issue, I don’t know how to perform this check/search for column name and don’t know how to paste information there as these columns in each document could be in different position(I mean in first doc column “AAA” could be in column A but in doc 2 this column “AAA” could be in column F).
Steps I’m performing now:
By “read range” activity I’m copying column headers and all rows with data from first document then I’m adding other headers manually with “add data column” to temp table as I know all column names from all documents.
After this step I’m performing “append range” activity from temp table to my created document with already added headers in same order as in temp table.
All those steps are in “for each” activity until all files from folders are copied
In addition I have last column with file name from which all data is taken for later filter in excel, may be it could be for a some sort of pointer.
Could you please suggest me the way I could solve this issue ?
As you mentioned you have already created a temp data table with all the known column headers.
I would suggest you to use the Merge data table activity with the missing information schema property to set as Add.
Iterate all the files from the directory using for each loop
-Use Read range inside excel application scope within the loop for each file item.
-Merge the datatabkle obtained from the read range activity and then merge it with the temp table.
-Since you have set the missing schema information to add so if there is any column which is not present in temp table it will be added automatically.
I hope this would help you to drive through the automation.
Previously I was thinking about merge data table activity, but in this case I need 2 data tables, first one with data from all previous documents and second with current data from current document. As I’m trying to do it in single for each activity I’m taking file by file and writing to single table and afterwards pasting it to result table.
If I’ll figure it out and do with two tables, then general “temp” table should be pasted to resulting Excel document by “append range”.
Will I be able to have temp table with previous rows and headers available in iteration with taking new “next” file ?
-Keep Temp Table outside the for each loop.
-Inside for each using the read range you will populate a datatable let’s call it the dt_records.
-Inside the for each use the merge datatable activity with the missing schema information set to add .
-In the merge datatable activity source table should be the dt_records(populated from the read range) and the destination table should be Temp Table.
@amarasto
Tried with multiple tables and the result is strange.
Result table is in format:
ABC
------ABD
------------BAF
and data with same Header have random effect, it could be under same column or on first free column after previous document.
How to create something like
ABCDF headers and data under it. If in different documents data under same header it will be put under same column and if header is different it will be put into new column with that different name.
sharazkm32
V_Roboto_V
Darshan_Sable
Bart_Simpson