Data360 Analyze

Recently I am working on a project but get stuck when reading excel file since I would like to read the file based on date format, for example it is Jan 2020 now, and I want to put the date as format {YYYYMM} after underscore. The output would be UD_0001_202001

However, I tried so many times including define properties in advance, the type set to SQL, it doesn't work at all. Could you guys advise a method on doing so?

Mat C.

Assuming you are using Data360 Analyze version 3.5.1 or higher you can use a Generate Data node to construct the required filename. 

The output metadata is defined in the ConfigureFields property. 

The built-in system 'RunDate_Pathsafe' run property provides the date in 'YYYYMMDD' format. See the Run property substitution topic in your Help documentation or online for the latest version of the product here ). 

The other statements in the CreateRecords script define the components of the filename and then combine them so they can be output. 

The generated filename is then input to the Excel File node which is configured to obtain the filename from the 'MonthlyFile' field:

Depending on your requirements, you could also do this completely without script by using a directory list node and sorting on created or last modified date.  For example, the following reads a directory for the most recent file and opens that file in Excel. 

The directly list looks like this.  I added the additional "Pattern" to narrow down the list of filenames.