How to check if a excel file exist in a direcotory

[nazuk]

Hello All,

there are two files in a directory. Both of the files have dynamic input name in the format TSE082QT_DD_MM_YYYY_HHMM and Outstanding_TSE082QT_DD_MM_YYYY_HHMM. So TSE082QT_DD_MM_YYYY_HHMM will always exist, but outstanding_TSE082QT_DD_MM_YYYY_HHMM may or may not exist. So basically, if Outstanding_TSE082QT_DD_MM_YYYY_HHMM exist , the it should get appended to the TSE082QT_DD_MM_YYYY_HHMM.

I have tried to code to check if  outstanding_TSE082QT_DD_MM_YYYY_HHMM exist in the directory Exist. 

Can somebody please help me on how i can input that file if it exist.

[grossal]

My mind is stuck on this to be honest. We could easily build a python workaround if that's okay, sth like this:

Someone else will probably see a less tricky way.

An alternative would be importing a dummy file and getting rid of the data if a dummy file is okay in that place/folder. This would look like this:

File exists:

File not exists: (check behind the Filter that we still have the original 205 rows)

I'll attach the workflow to the second solution. The dummy file is just a copy of your normal outstanding file renamed as "dummy.xlsx".

Best

Alex

[DavidP · Accepted solution]

Ok, so I've stared at this problem for A LONG TIME now.

The sample files you provided are a little all over the place:

1. 1 is xls and the other is xlsx

2. the data seems to be in Sheet1 rather than in the tab with the dynamic sheet name

3. the 1 line of data in the Outstanding file has a complete different structure to the data you're looking to append it to.

So I decided to doctor the files to remove some of the problems by doing the following:

1. make both files XLSX - I really don't know while XLS files even exist anymore after all these years!

2. move the data to the correct tabs and remove Sheet1

3. put just 1 line of data in the Outstanding file

I thought with this, it's a manageable problem to solve with something like this, which would load the data from 1 file if there's only 1 and both files if there's 2.

But the problem is that 2 of the fields in the 1 line of data in the Outstanding file gets recognized as a number and a date, while in the other file all fields are seen as strings since some of the rows have data that are not numbers and dates. The result of this is that the dynamic input tools don't work as the 2 files are seen as having different schemas.

So instead I had to use a batch macro twice instead of the 2 dynamic input tools. Then I just had to sort the data to make sure that the row(s) from the Outstanding file is in the last rows and bring in the fullpath from the original file with sheetname.

Now you'd be able to overwrite the original file using the [filename] field as the name and path of the file.

Hope this helps. Please let me know if you need any further help.