Copying columns from multiple excel files to one master excel file dynamically

Help Studio
, ,
1

Cheers guys,

My project looks like this: My robot receives, via an API call, multiple .xlsx files that contain 2 columns: A and B. Column A stays the same for all Excel files, but Column B changes for each file. The number of rows stay the same for both columns. What I need to do is: Copy the entire Column B out of each file received via the API call and dynamically paste it within the master excel file. The master excel file looks like this : Column A stays the same (it’s the same as in all received files) and starting with Column B, this file needs to get populated with each of the received files’ Column B. So, the first received file will paste it’s column B to the master’s column B. The 2nd received file shall paste its Column B contents to the master’s Column C, and so on.

I appreciate this might be a bit confusing, so I am going to attach a screenshot. I apologize in advance for my paint skills :slight_smile:

Columns
Columns1903×1037 81.7 KB

Going back to the problem. I have managed to actually paste information dynamically, using a variable that stores the column index and increases it by one after each column is being copied.
The code I used in the ‘Range’ : (Convert.ToChar(64 + int_ColumnIndex)) + “1” where int_ColumnIndex starts with a default value of ‘2’ (so as to start with the ‘B’ column)

The issue I have is the robot throws an error when reaching Column AA of the master file. It copies information to the ‘Z’ column and then throws an error. I suspect this has something to do with the fact that the conversion (within the range) doesn’t work properly anymore when the alphabet ends.

Do you guys have any idea on how to handle this? Is there a better way to dynamically copy the columns?

Thanks in advance,
Alex

2

Hi @alex_T ,
You can read each file, then merge data table
or check number of columns then write cell with index
regards,

3

Hi Albert,

Thanks very much for your input, it sounds really good :slight_smile:
How would I go about implementing the 2 methods? I am already reading each file, basically storing column B as a DataTable variable, yet how do I merge it with the master excel file without doing column index trickery? I’m not sure how to do this.

Also, how would I go about checking the number of columns? As in if I have 100 files to process, I know by default that means 100 columns?

Thanks very much,
Alex

4

Hi,
We can count columns
dataTable.Columns.Count.toString

5

@alex_T

MasterDt = DT1.AsEnumerable.Select(function(a,i) MasterDt.Clone.LoadDataRow({a(0).ToString,a(1).ToString,DT2(i)(“Name_Dt2”)},false)).copytodatatable

image
image1082×654 10.2 KB

6

Thanks guys but I really don’t get how I’m supossed to implement any of the solutions. Isn’t there a way to convert the column index (which would be 27 for the ‘AA’ column) from ‘27’ to ‘AA’ ? and ‘28’ to ‘AB’ and so on? I’ve been going at it all day with no luck so far it’s driving me mental