How to write last column during merging excel

Help
1

Hello Guys
I am merging excel file but during merging wanted to write state name at last column of merged file.

image
image1366Ă—768 60.4 KB

process will be
For each{
1st excel+state name into last column
2nd excel+state name into last column
3rd excel+state name into last column

}
merge data table activity

1 Like
2

You can get the range as datatableMerged.Rows.Count and then you can use add data column to add the data you want to within the for each row so that it will add the required column before merging the next datatable @smita.mobifly

3

@smita.mobifly,

Please answer the following questions:-

  • How many excel files do you want to merge?
  • Assuming that you have 4 excel files to be merged, is it possible to merge all the 4 excel files into the single datatable and then add the “Merged file”?
  • what exactly is status name in the scope?

Regards,
@hacky

4

File count are not fixed… merging excel file task has been done but wanna write(state name) last column during merging

5

@smita.mobifly

After merging the Excels (or datatables)
Use (Add Data Column) activity for the merged datatable before pasting into the resultant MergedExcelFile.

6

Hi @hacky,

its working, but while adding data into last column it replace with new data
while merging 1st excel, added state code TN
and 2nd excel having state code AP, so AP Replaced with TN into last column

Actual Excel:

image
image1366Ă—768 56.5 KB

Expected:
image
image1366Ă—768 108 KB

1 Like
7

@smita.mobifly,

As per my understanding, this is an overwrite issue.
While adding a new column in the second excel, the value is been overwritten.
In this case, please be very cautious and alert regarding the order of the flow and the executing sequence.
Also please ensure that adding new column range is in place.

Let me know how it works out.

Regards,
@hacky

8

Fine

In this case we need to add datacolumn while taking value from each excel as a datatable

Hope this steps could help you resolve this
—inside the for each loop after getting the datatable from excel with read range and getting the output with a variable of type datatable named outdt
—next to this use a activity called ADD DATACOLUMN where mention the datatable where we want to add the new column and mention the columnname within double quotes so that it will get added at the last with that name
—now we can add value to that column
And doing this for all
—Final all we can merge so that we won’t miss any value of a datatable

Kindly correct me if I m wrong with question and try this, let know for any queries or clarification

Cheers @smita.mobifly

1 Like
9

Thank you so much for your help…:slight_smile:

1 Like
10

No worries
Cheers @smita.mobifly

11

Hi,

Could you tell me regular expression for following string:Debit_Credit note- Details check_GSTR-2_AP_PIH_2019_6
Wanted to extract : AP

1 Like
12

Fine
i have a small query
will the highlighted region be constant throughout
2_ AP _PIH

Cheers @smita.mobifly

13

its not constant

14

count of (_) will be vary

15

It will be : 1_AP_
2_AP_
2_TN_

1 Like
16

Fine try with this
image

Cheers @smita.mobifly

1 Like
17

Thank you so much–:slight_smile:

1 Like
18

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.