One possible solution:
Create a Type with enough Attributes (variables) for the Maximum Number of Columns that appear in the spreadsheets you are extracting. I would suggest these be Short Text.
(For example, I have 3 spreadsheets to consolidate. One has 8 columns of data, one has 12 columns, another has 10 columns. I need a Type with 12 variables).
Add an Attribute (variable) to the Type, one for each Column (Column A, Column B, Column C, etc. until you have enough Attributes (variables) for the maximum number of columns that appear on any of the spreadsheets you will be looping.
Include an Additional Attribute FileName and another Attribute Row Number.
Save Type and Create a Database Table.
Open your 1st spreadsheet. Bring in the Type you created above using the Add Variable Step. Assign FileName to FileName variable. Loop Rows, extracting Column A to Variable A, Column B to Variable B, etc. until you have extracted all data from the 1st row. Extract RowNumber to Variable RowNumber. Insert a Store In Database step selecting the Database where you created you Table in the step above and Select the Type as the Variable after the row has been extracted.
Loop through all rows in 1st spreadsheet, extracting and storing the Data.
When the 1st spreadsheet is completed, perform the same steps for the remainder of your spreadsheets, extracting and storing all information. You could use separate branches, one for each Spreadsheet to consolidate or use Repeat Next step with a Loop For Each File step to process all spreadsheets to consolidate,
Once all the data has been extracted, open your blank spreadsheet and insert a Query Database step, selecting * from the Database table you created.
Order by FileName and Row Number.
Map the Variables to your Type (Column A to Type Column A, etc).
Use a Set Content of Cell Step to Transfer data from your Database into the blank spreadsheet. Content would be either Variable (Column A, Column B, etc) or Converter if you need to change Short Text to another Format (number, integer, date, time, etc).
Once all rows from the 1st FileName, insert a new Sheet and repeat the process until all of the Files have been inserted into their own Sheet.
Use a Write File step or Send Email step to output the new consolidated spreadsheet.
Important things to consider:
Excel is a memory hog. Depending on the number of spreadsheets you are processing, it may be a good idea to break the steps up:
Create 1 robot to extract each of the individual spreadsheets.
Create an additional robot to create the new consolidated spreadsheet.
Run in sequence such that 1 robot must finish before the next robot starts.
hth.
Home
