Write two datatables to a single excel row

[charles.bain (Charles)]

I have a process that scrapes personal information about job candidates (First Name, Surname, National Insurance Number) and then scrapes the last three years of their employment history.

The personal information is scraped as a single row. The employment history is then scraped as it’s own datatable and can be multiple rows.

I need to write this to a spreadsheet, one row per candidate.

e.g. Datatables

NINumber, First Name, Surname
AB123456A, Joe, Bloggs

Employer, Date From, Date to
Employer1, From1, To1
Employer2, From2, To2

Desired format:
NINumber, First Name, Surname, Employer1, Date From1, Date to1, Employer2, Date From2, Date to2
AB123456A, Joe, Bloggs, Employer1, From1, To1, Employer2, From2, To2

Hopefully this makes sense. I’ve had a look online but none of the answers seem to match what I need.

[Anil_G (Anil Gorthi)]

@charles.bain

you can write the first table directly

then loop through second table and write each row separately

for that you need to make sure column values change for each loop

you can use this class to change the number to excel column name …and the number you want can be derived from loop counter…for example use 4+(currentIndex*3) as the number so for first time the number would be 4 for second it is 7…so tat first time column would be D and second time it is G and so on

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(34)

cheers

[charles.bain (Charles)]

Thanks! I think this should work. The columns are named “Employer 1, Employer 1 From, Employer 1 To” etc. so hopefully I can target the column name and increment the number.