Create Excel Datatable out of txt file and filter data

Help
1

I need help creating a datatable out of a txt file.
The file looks like this:

KID DID Currency Limit
0400 40060631 EUR 3000
0400 40060629 EUR 3000
0401 40060627 EUR 10000

I managed to read the data and generate a datatable. My problem is that if I use the activity “Generate Datatable” the new datatable doesn’t copy the headers. Therefore i can’t filter the Data for “Limit”<10000. Any idea?

1 Like
2

@re95 Did you check UseRowHeader under TableOptions?

3

Hi
welcome to UiPath community
–we got an option like use row headers in the property panel like this

image
image381×553 20.2 KB

kindly enable this and try once

–are even we can access the column index where the column index for first column is 0

For more info on this

Cheers @re95

in filter datatable activity
we can mention the column index as well like ths (0 for first column)
image

4

Yes i tried to use the row Header Option and Set the filter Option in the “Filter data table” according to Ur picture but i get an error message : "value of argument ‘columnindex’ is Not Set or valid

1 Like
5

Fine
can i have a view on your filter wizard a screenshot if possible
Cheers @re95

7

Unbenannt
Unbenannt800×559 12.4 KB

1 Like
8

Unbenannt9
Unbenannt9552×740 17.4 KB

9

@re95

Specify the Column name instead of Column Index in double quotes and then try again.

10

Unbenannt2
Unbenannt2779×541 10.7 KB
changed to this but still same error

11

Unbenannt1
Unbenannt1383×860 18.2 KB
maybe something in my datatable is wrong?

12

@re95

After Generate Datatable activity, pass that Datatable to Output Datatable activity to convert Datatable into string. And then print it and check once. Show me screenshot of it.

14

test1 this is what i get

15

ok it seems like the third column doesnt work properly. I checked the txt file and the formating there looked right

16

The reason is usually COLUMN INDEX starts from 0 for first column
Here we have mentioned as 3 which refers to fourth column but the Limit column is a third column in the table that’s why buddy
As we were discussing we won’t be able to access column name Limit it was suggested to use columnindex and you were almost done
No worries
By the way that was the reason why it gave full records without any filter as we mentioned as 3

Cheers @re95

17

Thanks for the help i found my mistake. I didn’t check the box for automatic detecting of variable types in the data table. Thats why i couldn’t filter the Limits properly. But I still have one last question. After filtering the data table it should paste the information into an excel spreadsheet. It works properly the first time but if i run the operation again it doesn’t clean the spredsheet. So if my first filtering results in 3 rows and the second one only results in 2 rows then it won’t delete the third row from the first run. Anyway to make sure everytime i run the robot it uses an empty excel spreadsheet/ completely overwrite the old one?

1 Like
18

Fine in that case let’s do one thing
For each iteration let’s save the excel file with a time stamp so that it will create a new file without overwriting the old one
—this helps us to have a back up file of previous one as well and this can be done by mentioning the time stamp along the file path of excel in excel application scope
“Yourfolderpath\Output_”+Now.Tostring(“hh_mm_ss”)+”.xlsx”

This will save the file like
Output_04_22_30.xlsx

Hope this helps you
Cheers @re95

19

thank u very much :+1:

1 Like
20

Great
Cheers @re95

21

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