Table merge in for each

Help
, ,
1

Hi everyone,

I had excel files like 01012019 to 30012019 for each days of a month. And what i want is to combine a specific range in excels and paste into consolidated excel.

  1. Method i tried is

In for each, for 30 days

Read range for specific ranges

Write range but there is a issue that i have to write to next column (or next clear column) for every data,

I used Convert.ToChar(Convert.ToUInt16("A"C)+index)+“1” which i found here for location of column.

The outcome is, it shifts the data to next column but it clears items written before. I can only see the last one.

  1. Method i tried is

For each

Read range

Create data table for all reads (in for each read range only keeps last item!!)

Then, combine tables ( tried merge but could not)

And write consolidated

Please help :slight_smile:

2

I think your 2nd method might be a little easier, but you might have a mistake.
It should be something like this:

For each f In files
   Excel Scope: Read Range

   If File.Exists(outputFilePath)
       Workbook Append Range
   else
       Workbook Write Range

This would assume that your columns are consistent on all files so the columns are aligned, but if not, you can always make some manipulations to align the columns.

The pseudocode example, loops through each file within your date range, then reads it to a data table, then appends or writes it to a new spreadsheet depending on if the new file already exists or not. I chose to use the Workbook activities to output just because I know it works (minus formatting), but I’m sure the Excel ones would work too. If you choose, you can add a retry scope around the Append and Write to prevent any issues where a user or robot is using the file.

so that’s just an idea

Regards.

3

Thank you a lot. But i need tables to be in a same sheet with different columns.

exmp
column A from excel workbook of first day, column B from second… I could not make columns move to the next.

4

So what you are saying is that you really need to join the tables. You could look into using .GroupBy() which is a .net linq solution and fast, however can be difficult understand and transfer knowledge to others.

The other way would be to match the rows up to a specific column (so you can put the column values into the correct row in your first table that is created). Then, using an Assign activity, you can place the value of the column to that row you matched with. For unmatched rows, you would need to add a new data row (if that scenario is possible).

You would also need to check if the file exists already, so the first table can be written to a new file.

psuedocode example of the logic might look like this:

For each f In files
    Excel Read Range // let's store to newData

    If File.Exists(outputFilePath)
        Excel or Workbook Write Range // newData to outputFilePath

    Excel Read Range // let's store to existingData

    If Not existingData.Columns.Contains(newColumn)
        Add Data Column // newColumn to place new values

    For each newRow In newData
        Assign: matchedRows = existingData.AsEnumerable.Where(Function(r) r(matchColumn).ToString.Trim = newRow(matchColumn).ToString.Trim).ToArray
        If matchedRows.Count > 0
            Assign: matchedRows(0)(newColumn) = newRow(newColumn).ToString.Trim
        Else
            Add Data Row // ArrayRow: {newRow(matchColumn).ToString.Trim}
            Assign: existingData.Rows(existingData.Rows.Count-1) = newRow(newColumn).ToString.Trim
    
    Excel or Workbook Write Range // existingData

Not sure if I did that all right, but essentially, you would follow these steps:

  1. Get data from new file
  2. Write the data to a new file if it doesn’t exist yet
  3. Get data from existing file to add new data to
  4. Add new column for new data
  5. Loop through new data to add to existing data
  6. Find row that matches with new data using specific column (I used .net for this to an array of rows)
  7. If match was found (.Count > 0), assign the new value to the new column
  8. If match was not found, then add new row and assign the new value to the last newly added row
  9. Finally, write the existing data with new data back to spreadsheet

Hopefully, this helps you to finding your answer.

Regards.