I want merge data table inside do while loop

Hi guys,

I have working in a use case like
I’m trying to extracting tables say maximum (25) which may vary in sizes of rows and columns. I’m using do while to picking one by one table from the web. I want to add all the 25 data tables in one datatable. And that final datatable I will write in a csv or Excel.

Tell me guys how can I do it…
Can I use merge datatable ? How can I merge one with another in a do while loop?
Regards,
Sriram

How are you merging them? Do you want to JOIN the tables on a primary key, or do you want to UNION them so that rows from table2 - table25 are all added as new rows to table1?

I dont believe a do while loop is required in either case. Generally, you would extract all tables to a dataset or a list of datatables. Then iterate through each table and union or join it into your “master” table. After iterating through each table, either write range to an excel file, or write to CSV file

Hi @Sriram07

If you just want to scrape data and dump everything in a single excel sheet (I am assuming) then it is quite easy.

Scrape data (multiple data tables from multiple websites like you mentioned) and use ‘Append range’ using the different data tables activity which will keep on adding the tables to your existing workbook (or it will create a new one for you if it is the first time you are writing the data)

PS : Dont forget to ‘clear data table’ after appending the range to a workbook

Tell me whether this helped or you need more steps :slight_smile:

Merge datatable works if there is a common column across the datatables you are trying to merge, i.e. a primary key columns where unique values match each other.

If there are different (additonal) columns, you can still use merge but you will need to ignore the schema to allow additional columns to be added.

@TimK @Dave @Raghavendraprasad
I’ll tell you clearly what I’m working on
See
Assume that

In a webpage,

1|a
2|b
3|c
4|d
5 |d
Here 12345 are hyperlinks which will navigate to webpage which having table content.
I’m accessing this 12345 with dynamic selectors by changing “table row” as counter variable in Do While.
I did this part and writing in CSV file individually as 5 Excel files.
So I need your help here like how can I get all the tables (5) which I extracting from web into a one table (like merging or anything )and writing into Excel finally.

Important is all the table has same column names…

Help me guys how can I do this …

Regards,
Sriram

Since all tables have the same column names, you should follow the advice from @Raghavendraprasad and simply use append range. That is built to do exactly what you’re asking (it’s the same as UNION ALL from a SQL query)

2 Likes

Append range hands down, you can clear data table after every extraction and write it. Simple as that.

PS : Merge also works if you want to keep everything in memory and write it at one go.

1 Like