Copy and edit data from excel file to another

[Nafissa_Al_Abida]

Hi All,

I’m currently working on a workflow where I need to copy data from an Excel file to another Excel file.

Here I get the employee data from scraping the company web.

Then I need to copy that data to another Excel file but the first and last names need to be separated.

How can I do that?

Thank you for your help!

[Matt67 (Matthieu LEVAL)]

Hello there,
if I understood correctly,
you scrap web datas to fill a dataTable and then write in an Excel file.
From that given datatable, you want to have datas presented in another way, and write in another Excel file.

I would build another dataTable with the columns you want (Employee ID,First Name, LastName) and create row for each row of first datatable, with good informations, using a simple split(" ") to separate fullName.
Then Write Range workbook on your other Excel file.

Hope this will help you

[Melroy_Dsa]

Hi @Nafissa_Al_Abida,

• Read Range the file to get a dataTable,

• Using LINQ, you can write it to another dataTable(Assuming dt_output has the defined schema):
  dt_output = dt_inputData.AsEnumerable.Select(Function(row) dt_output.Rows.Add({row(“Employee ID”).ToString, row(“Name”).ToString.Split(" “)(0),row(“Name”).ToString.Split(” ")(1)})).CopyToDataTable()

• Use Write Range to write the dataTable to excel

Hope this helps!

[Akash_Javalekar1 (Akash Javalekar)]

Hi @Nafissa_Al_Abida

Please try below linq query
(From row In dt_1.AsEnumerable()
Let empID = row(“Employee Id”).ToString().Trim()
Let fullName = row(“Name”).ToString().Trim()
Let firstName = If(fullName.Contains(" “), fullName.Split(” “c)(0), fullName)
Let lastName = If(fullName.Contains(” “), fullName.Split(” "c)(1), “”)
Select dt_2.LoadDataRow(New Object() {empID, firstName, lastName}, False)).CopyToDataTable()

CopyData.zip (2.2 KB)