Excel/Text File Obstacle

Help Studio
, , , , , , ,
1

Hi All,

I am in need to create an automation for the following below:-

  1. Import the latest up to date .txt file into an excel (.csv - with column headers). Always choose the latest date of .txt file to import from.
    *Eg. Import Today().txt from folder “C:\Users\FolderPath”

  2. After importing is successful, generate into a new .csv file with "+Now.ToString(“ddMMyyyy”), save and relocate to “C:\Users\FolderPath2”

  3. Open the .csv file, read range “A:Z” and VLOOKUP the info using the latest .csv file
    Eg. =VLOOKUP(A2,‘ExampleCSV’!A:Z,10,0)

Currently, I am stuck in trying to “Read text file” function where it keeps stating - “Absolute paths not recommended.Use Relative Paths etc…”

I have searched the forum for similar topics and tried the solutions. But when i create and assign strExample = Directory.GetFiles(“C:\Users\FolderPath”,".txt")*, I do not know how to read the text files from here.

2

Hi ,
you are getting like this ?

image
image730×151 5.63 KB

To solve this ,
assign the path of text file into a variable .
and give that variable in read text file activity
image

3

@PVSneha_Nair ,

Hi, thanks for feedback. It works but i wish to read the latest date of the .txt file instead of specifying the exact file.

Eg. Read todays “demo_13032024.txt” instead of the other text files inside the folder such as
demo_10032024.txt
demo_11032024.txt
demo_12032024.txt

4

@Jackson_Hew

use the below expression

Directory.GetFiles("Provide your folderpath here","*_"+now.ToString("ddMMyyyy")+".txt").First
5

To read the file with the latest date in its name in UiPath, you can follow these steps:

->Use the “Get Files” activity to retrieve all files in the directory.

image

->Use linq to get the latest file.

image

(From file In array_files
Let fileName = Path.GetFileNameWithoutExtension(file)
Let dateString = fileName.Substring(fileName.IndexOf(“_”) + 1)
Let day = Integer.Parse(dateString.Substring(0, 2))
Let month = Integer.Parse(dateString.Substring(2, 2))
Let year = Integer.Parse(dateString.Substring(4, 4))
Let dateValue = New DateTime(year, month, day)
Order By dateValue Descending
Select file).FirstOrDefault()

this LINQ query extracts the date from the file names, converts it into a valid date format, orders the files based on these dates, and then selects the file with the newest date.

->Read the text file
image

6

Hi @Shiva_Nikhil ,

It work!

So just for my understanding,

Directory.GetFiles(“Provide your folderpath here”,"_“+now.ToString(“ddMMyyyy”)+”.txt").First*

Directory.GetFiles(“”) ← this here is to find and locate the directory of the folder paths
"_" ← what is this line for?
.First ← means it will always take the first file listed in the folder?

1 Like
7

Hi @PVSneha_Nair ,

Thanks for the reply,

But i am not familiar with LINQ. I have gotten the solution for reading the text file already.

Thanks for your help. :slightly_smiling_face:

8

@Jackson_Hew

Yes first means it will take first file in the provided folder

You can use _ or not it will not impact any thing.

  • indicates what ever is present before to the date it will accept
9

I see, thanks for the assistance.

But when I tried to run the activity I have this error.
image
image

I am trying to read the text file > Generate data table > write to excel and save as .CSV

10

@Jackson_Hew

can you share the expression which you have used and also check whether you are having any files with the today’s date

11

Hi @Shiva_Nikhil

Thanks for the reply.

It seems reading a text file with 14 million rows is not ideal.

I have found an alternative using Excel macro to perform.

Thanks for the help and codes :+1:

1 Like
12

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