How to read Excel file dynamically?

Wants to read excel file dynamically as the file name is not fixed.

today, If we get the file as abc06022017.xlsx, Tomorrow it will be abc06032017.xlsx

2 Likes

Hi,

Try this.
var todayFiles = Directory.GetFiles(“path_to_directory”).Where(x => new FileInfo(x).CreationTime.Date == DateTime.Today.Date);

2 Likes

Hey @kirti9000

You can Read all files exists into a directory by using Directory.GetFiles(FOLDER_PATH+"\INPUT\","*.*",SearchOption.AllDirectories)

Then inside for each you can make a condition to read only excel files like this :slight_smile:

Path.GetExtension(item)=“.xlsx”

For more assistance see below screenshot.

Regards…!!
Aksh

3 Likes

Thanks @aksh1yadav and @ddpadil ,

In my scenario, I want to read Excel from workbookpath as below:

Is there any way we can define workbook path dynamically.

as xyz.xlsx* So that it can read a file present on current path with same format example(xyz01 or xyz02).

1 Like

Hi,
You have to iterate through the files inside the folder and inside foreach use Excel application scope activity pass that item in the “workbookpath” which dynamically choose the file one after another .
If you want to itereat through only today’s file follow my previous post else u can set the counter=1 in the foreah and exit after that.

2 Likes

inside sequence if you will use your Excel application Scope and if you will pass the item variable it will replace the absolute path of that file and excel application scope will check that path and if it exists there then it will open that excel visible if you have checked visible chekcbox if not then background. same it will happen will all Excel files exists in that particular directory on each iteration.

Thanks&Regards…!!

5 Likes

I like your approach, normally I would assing the directory.getfiles first use that variable on the for each.

Great, thanks.

1 Like

Hello,

When trying to put your expression in a variable, UIPath prompts and says that “Expression expected”. Are you sure this expression works ?

Thank you

which expression?
can you attach your workflow or snapshot error?

PS:Please reply to new thread of your post

Hello - having a similar issue here. We have a two step process - one to extract an Excel file from SAP and one to open it and manipulate it. The Excel file save in SAP is fine. At the beginning of that sequence we created a variable named today and assigned it to a current date .NET description – it works FINE and we use the variable in the Excel file name when we save it – for example, if the file is save on 24 June 2017 the filename is TheReport24062017.xslx. The variable for the short date is called tdy.

In the Excel Application scope that immediately follows, we want to specify the file path as
“C:\SOTC\Reports\TheReport” + tdy + “.xlsx” . The file seems to be recognized and the first activity is to read range and the output is reportDT. That seems to be fine too. But when we try to delete column F using the Delete Column activity. It barks at us to say “Delete Column: The table does not exist”. Searching further, we discover that the Excel Application Scope is not finding the real file ---- it is instead creating a blank Excel file named TheReport.xslx and that is what is read by the Read Range activity.

Questions:

  1. How do we get the Excel Application Scope to find the right file when the file name contains a date that changes every day?
  2. When we use Delete Column, in the Table field we enter the data table name - in our case, reportDT - but it makes us add .ToString and then when we enter “F” in the column name we get the Table not found error. HELP!

Thanks!
Mare

I found this thread helpful!

Can I get full workflow of this?

Hey @TGS5

Just a quick sample for u - read all excel files only from a folder sample.xaml (7.2 KB)

String[ ] return_Value = Directory.GetFiles(“F:\Test",”*.xlsx") // This will return you only the *.xlsx files from a particular folder path only.

Directory.GetFiles(“”,“”)

path
Type: System.String

The relative or absolute path to the directory to search. This string is not case-sensitive.

searchPattern
Type: System.String

The search string to match against the names of files in path. This parameter can contain a combination of valid literal path and wildcard (* and ?) characters (see Remarks), but doesn't support regular expressions.

Return Value
Type: System.String

An array of the full names (including paths) for the files in the specified directory that match the specified search pattern, or an empty array if no files are found.

Regards…!!
Aksh

Thanks.But it’s not working properly.It is unable to read all xlsx files from a folder.

hey @TGS5

Check those files extentions those are .xls or .xlsx. if you wanna cover that as well add search pattern for that :slight_smile:

Regards…!!
Aksh

Thanks :slight_smile:

in excel application path filerange.element(0)
its very easy method to access all the files one by one

Is there any way to read Excel file without giving any folder path because i don’t want’s to hard code the path ,it should work on every system.

But what if you have two excel files with the same name? How would it know which one to read without the path?

1 Like

First i am checking is there any same file present in the system if there is any file i am deleting that file but it’s also a hard coding because for this i need to check the file name , i want to remove all the hard hard codes ,do u have any solution ?