Merging 3 excel files with non continuous records into 1 excel with continuous records

Hi All,

I am working on a problem wherein i have 3 excel files with certain information, i am interested in 2 columns only, one more thing is that the records are not continuous as in the first record is in 1st row and second record will be in 4th row as shown in the images below

1
First Excel

2
Second Excel

3
Third Excel

And as a final result i want an excel sheet with continuous records as shown below

4

@shekhawat.arjun1307
give a try on following flow:

  • read range activity - excel data readin into datatable

  • assign activity: arrTables - Datatype Datatable array - arrTables = {dt1,dt2,dt3}

  • assign activity: dtData - DataType: DataTable - dtData = dt1.DefaultView.ToTable(False, {“Name”,“No”}

  • for each activity - Typeargument: Datatable - Values: arrTables

    • merge activity: dtX to dtData - dtX.DefaultView.ToTable(False, {“Name”,“No”})
  • Filter datatable - remove empty lines

1 Like

@ppr

Was typing same :slight_smile:

Hi @ppr it’s quite difficult for me to comprehend what you are trying to do in assign activities could you maybe show in a workflow and also while using the read range activity to read each files can you also tell me how to only select 2 columns as my orignal files has other column values as well.

My actual file will have range from A-CV and i would want A and G columns only

@shekhawat.arjun1307
find some visuals:
grafik

grafik

grafik

1 Like

Hi @ppr

In the merge Data Table activity when i enter the source value i get this error, could you help me here.

Thanks

Looks Like an issue with the datatypes.
Can you Check if the typeargument for the for each Activity ist Set to datatable

2 Likes

Could you please share excel file

Hi @ppr
yeah it was object type, changed it to datatable works now.
My output still comes not the way i want it, there is a lot of spcae bw each record and i want it to be continuous without space,could you help me i am providing the as is and expected output.

AS IS

EXPECTED

Capture100

@shekhawat.arjun1307
Give a try on Filter Afterwards the result with Filter datatable Activity and remove the empty Lines by condition is not empty for both cols

1 Like

Hi @ppr

I tried this, it behaves very weirdly. For first 18 records it has removed the empty lines but then when i checked the entire excel sheet i found one record at 250th row and above it all were empty lines until 18th record.

keep in mind if in excel is within a cell a blank (space) it is rated as not empty. So an only visual check on an excel will not be sufficiently for analysing the data.

The filter datatable with the right filter configuration should remove those empty lines. Otherwise we can also use a LINQ statement for this

i am attachig a snip of my filter data table activity, can you let me know whether it is correct if yes, then how can i use LINQ statement for sorting this issue.

Capture

as it is unclear which column has some unneeded spaces within the empty rows so we would suggest to try it with an or conjunction instead of and

The issue still remains. I am getting something like below-

Continuous values until row 19th and then suddenly at 251st row i am getting one value

have a look here on starter help for getting rows where no col exists with an empty value:
GetRowsWithNoBlanksInAnyCol.xaml (7.9 KB)

well actually the data table you are using is a bit different than my scenario.
In the below case the missing values are random where in my case if a value exist in first column then there will be a corresponding value for it for sure( in this example there will be a value corresponding to B cause these values exist in pairs), Will it still work for my case, the workflow?

Capture

its a starter help. The main part for filtering is working with any datatable

Doesn’t help i still face the same issue.