Reading From Text File and Writing in Excel

Help
1

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)

2

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.

3

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

4

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

5

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

6

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)

7

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

8

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.

9

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--------------------------------------

10

Got it…will try and execute this…

Thanks a lot

2 Likes
11

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

12

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

13

No Bro…its still. not working …

14

I will send you the workflow after a while.

15

ok no issues…

Thanks in advance

16

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

17

Sorry. I will send you today.

18

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)

19

Hi reda

20

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