Reading From Text File and Writing in Excel

Hi,
I having this text file and i want to write in excel but the values in the first column should be trimmed and the + Sign should be removed, Kindly help mw as i m new to UIPath

sample.zip (515 Bytes)

Once you read the data,
Read the first value into a string : say strValue

In the assign activity, strValue = strValue.Replace("+","").Trim

Replace + with an empty and Trim for the trimming the spaces.

Hi @Abhishek14

so I tried to make something quickly because this is kind of a frequently asked question, so I suppose you want to write the data a keep the columns the shape in the text file, so you need to do some string manipulation.

Here is a workflow that you can use as a starting point, I did a split on lines, then used RegEx to detect each value and finally add this value to an excel sheet.

there is a lot of room to improve it, feel free to do that.

TxtToExcel.zip (11.2 KB)

enjoy :slight_smile:

Reda

If I have two columns in two different excel sheets with same column names
like Sheet 1:- Sr.No and Salary
Sheet 2:-Sr.No and Salary
I have to add third column in sheet 2 as “Status”

i want to mach the salary of sheet 1 and Sheet 2, if it is equal then i have to set the Status column of sheet 2 as Matched else Unmatched

One question - How you uniquely identify the row for comparing salaries in both sheets?

For example:

Sheet1:
ID Salary
100 10000

Sheet2:
ID Salary
100 20000

Here ‘ID’ is the unique column. Like that is there any Unique column to compare the salaries?

Regards,
Karthik Byggari

No there are no other columns other than these 2 column
sheet one is standard one while data in sheet 2 is scrapped data from application.

demo data.xlsx (8.5 KB)

If the salary from Sheet1 is also present in Sheet2, then status would be matched irrespective of which row it is. Am I right?

For example:

image

Not Exactly…
I have to make sure that

For example:
row Number 1 in sheet one and sheet two salary should be same then the status should be match.
for time being we can consider that Sr. No. Unique in sheet 1 and sheet 2.

Okay. Got it.

Follow these steps -

  1. Use Read Range Activities to read the data from Sheet1 and Sheet2.

Sheet1 data will be output to DataTable1
Sheet2 data will be output to DataTable2

  1. Create a variable of integer type - Say intCounter with a default value 1

  2. Use for each loop activity to loop thru the data table.

For Each row in DataTable2

----------------------------Body of For Loop ---------------------------

intCounter = intCounter + 1
datarows = DataTable1.Select(“Sr.No=” + row(0)) [if Sr.No is Integer]
OR
matchrow = DataTable1.Select(“Sr.No=” + “’” + row(0) + “’”) [if Sr.No is String]

<<If matchrow count is greater than zero means, the sr.no is found in Sheet1>>

If matchrow.Count > 0 Then

If row(1) == matchrow(“Salary”) Then

Use Write Cell Activity to update the status in the Sheet2

Write Cell Range should be “C” + intCounter

Else Part, set status to unmatched

Else Part, set status to unmatched

------------------------------------End of For Loop--------------------------------------

Got it…will try and execute this…

Thanks a lot

2 Likes

Hi,
datarows = DataTable1.Select(“Sr.No=” + row(0))

this line is showing me error.
and datarows variable is of type datarow.

can u please send me the workflow

datarows variable is of type datarow.
It should be array of datarow.

No Bro…its still. not working …

I will send you the workflow after a while.

ok no issues…

Thanks in advance

Hi ,
Can u send the work flow of yesterday for better understanding…

Sorry. I will send you today.

hi,
I have tried number of ways to do it, but it is not working…
kindly help me i m new to uipath…

thanks

demo.zip (8.6 KB)

Hi reda

Your workflow separating decimal numbers
ie 145.56 by 145 and 56 can u provide solution for this
because your workflow is working osm so