How to check if a excel file exist in a direcotory

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.

2019-11-26 Dynamic file name and sheet name in output data.yxmd

Accepted answers

DavidP

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.

append if file exists.yxzp

Outstanding_TSE082QT_02-06-2498_9167.xlsx

TSE082QT_02-06-2498_9167.xlsx

All comments

joshuaburkhow

Hi @nazuk

You'll want to do something like this using the directory tool, filter tool, and dynamic input tool 🙂

grossal

Hi @nazuk,

I see a couple of options here. If it's guaranteed that only two files are in the folder, you could use wildcards for the whole data type.

C:\path\*.xlsx

If you have more than those two file in the folder, you could use:

C:\path\*TSE082QT*.xlsx

If you want to be even more flexible, you could use a Directory Tool, a Filter Function and the Dynamic Input.

Let me know if this helps.

Best

Alex

nazuk

@joshuaburkhow :

Thanks for the quick response. CAn you please help me in getting the formula for file name, since not neccesarily, it will be today's date, it can be any date and any time. Just the keyword will be same.

nazuk

@grossal : Thanks for the reply.

So basically i want to use first option, since one of the file will have dynamic worksheet name too.

So basically i am using directory tool, then i have applied filter, but then can i directly use dynamic input tool after filter, or do i need to write a formula first to specify file name?