How to Read Data From Excel columns Having Delimiters

i have excel file in that excel file only one column and many rows i want to read data from that excel file i want to make first row data as header and all other rows data put in another excel file according to the header name. Here is my Excel FileTest.xlsx (10.6 KB)

Build a datatable with that particular header. https://activities.uipath.com/docs/build-data-table
Read the excel data, for each row use Add Data row activity, with the previous build datatable and the row split by it’s delimiter in the ArrayRow property (https://activities.uipath.com/docs/add-data-row) .

hi RobertD please see the excel file once

The header will always be First Name;Last Name;Company Name;Role In Company;Address;E-Mail;Phone Number? If so, build the datatable with those columns, when you read the excel ignore the 1st row, and the split by “;” of simply by ; and trim the " .

Hi @sachin_sharma,

Please find the following steps :->

read range activity -> from the properties check the add header property
now make the output of read range as (DTop) so u will get all the data with header in DTOP

now create a datatable with your heading as Firlstname, lastname, email so on.
and give the output as DTFinal (activity would be BuilD Datatable)

for each row item in DTop (make the type as string so u will get the string value in item)
assign a variable ->
assign actvity -> varialbe as splitedarray() (create a variable with array of string)
splitedarray -> item.Split(";") (make it in assign activity)
now create some variables as strting -> FirstName, LastName,CompanyName
assign activity -> FirstName -> splitedarray(0).ToString
LastName -> splitedarray(1).ToString
assign -> CompanyName -> splitedarray(2).ToString
AddDataRow activity -> property -> in array property -> {FirstName, LastName, CompanyName}
output would be DTFinal datatable
now write range activity and create an excel file and give that path to your write range
activity (excel application scope) and give the datatable as DTFinal

hi @hemal I am facing problem to doing the following step

Hi @sachin_sharma where are you facing exactly ?

@hemal i don’t know why split function is not working correctly every time it shows error

can you please add a screenshot for error showing ?

hi @hemal can you please check my code and tell me where I can change my code Main.xaml (23.1 KB)

Hi @sachin_sharma,

Sorry actually I don’t have UiPath for a week atleast (as of now).
So that’s why I was asking for the screenshot and so that I had mentioned the steps instead of xaml file. So it would be great if you send a screenshot.

Thanks,
Hemal

thanks @hemal for your response actually can you give me split funtion code because i am trying many code but not working

@hemal my split code problem

hi @hemal I try another method it working but data is coming in other formats bracket is coming with data. I split the data with a semicolon but the bracket is coming how I can split with both!
here is my output file please check it data|690x267

Thank you very much for your time

Hi @sachin_sharma,

Let me know if still issue is coming.

Regards,
Hemal

Thanks For Reply but the problem is solved

1 Like

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