Create Excel Datatable out of txt file and filter data

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

@re95 Did you check UseRowHeader under TableOptions?

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


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

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

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

1 Like

@re95

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

changed to this but still same error

maybe something in my datatable is wrong?

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

test1 this is what i get

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

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

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

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

thank u very much :+1:

1 Like

Great
Cheers @re95

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