Adding a row to spreadsheet which has 130 columns

[Aparna_Panchagnula]

Hi all,

Following is the scenario am trying to work on;

1. I have an input file, excel1, with 19 columns. For example, let us use column names ColA to ColS.
2. I have another output excel, excel2, which has 130 columns. For example, let us use column names Col1 to Col137.
3. I have to copy the values from excel1 to excel2 based on the column names (For example, matching ColJ to Col23) and leave the rest of the columns in excel2 empty.

If I had a simpler scenario like;
Scenario:
a) Header of excel1 would have Fruits, Code, City
b) Header of excel2 would have Fruits, Vegetables, Books, Street Name, City Name, State, Pin Code, Country.
Solution: a) In this case, I can read both excels, use for each activity for excel1 and add row in excel2 using an add row activity with following properties;
ArrayRow: {row(“Fruits”).ToString,“”,“”,“”, row(“City”).ToString,“”,“”, row(“Code”).ToString,“”}
DataTable: excel2DT
b) And finally add a write range activity.

My question is, what is the best possible way to add a row in the scenario I mentioned where I have to add a data row of 130 columns, of which only 19 have values and rest are supposed to be left empty. Apart from using Add data row activity (and trying to enter all the 130 values in the properties field), is there a better way?

Thanks a lot in advance for taking time and for helping me solve this.

[alin.c.mihalea]

Hey @Aparna_Panchagnula,

Hope this helps.

1st Step:

I would create a Config file (.xlsx or .json) which maps the columns names from excel1 to excel2, for all the columns that I would like to bring from excel1 to excel2. Example:

2nd Step:

Rename the required columns from excel1 according to their corresponding name from excel2. (using the Config from Step 1).

3rd Step:

Merge excel1 (Source) to excel 2 (Destination) with MissingSchemaAction = Ignore.

That’s all At this point, you will have all the additional rows from excel1 in excel2, under their corresponding columns.

[Aparna_Panchagnula]

Hi @alin.c.mihalea,

Thanks a lot for your help. I didn’t follow the exact steps you mentioned as the excels that I was working on had something like one to many mapping, but your suggestion helped a lot. I used a config file to match the column names and from there updated a template that I created for my output excel rather than merging excels.

Again, thanks a lot for your help.