Calculate date based on filename

1

Hi,

I am looking to calculate filedate based on the date mentioned in the file delivered to us.

For example - the file name is ‘Abc Xyz Pqr 12 Jun 2021.xls’

I need to create a file which looks something like this - abc120621

So far I have tried the following:

  • Get list of files
  • use a python script to convert the files to csv (because I need the filename to be the same as the sheetname to import it comfortably)
  • list files again and filter for .csv
  • Add a Rule:
    • Create a new column called ‘File Date’
    • if contains([File name only], ‘Abc Xyz’)
    • keepafter([File name only], ‘Abc Xyz Pqr’)

From the above step I get the File Date as 12 Jun 2021

Now I need to export the file with this date format - filenameddmmyy

But I am stuck and am not sure how to get that.

Can someone please help me?

My apologies if the explanation isn’t clear enough.

Thank you.
R

2

Hello,

Format with current date:
format(now(), ’ yyyyMMdd’)

If you want to convert “2 Jun 2021” to “120621”, try to use “right” (to get string “12 Jun 2021”), “pick” (to replace Jun to 06) “replace” to replace “202” part of year with “2”, “removechars” to remove space.

1 Like
3

You can deconstruct a text date from one format and re-construct it in another format using the date() and format() functions. For instance:

format(date("12 Jun 2021", "dd MMM yyyy"), "ddMMyy") // returns "120621"
1 Like
4

Hi,

Thanks for your response :slight_smile:

But the issue is the date in the filename keeps changing. There is no specific pattern as the date in the filename depends on the date on which the file is delivered, which varies depending on whenever the data is available.

So I need to extract the date from the filename - create a date using an expression and name the newly created file in the format - filenameddmmyy

Please see the below screenshot:

Screenshot temp

I use the above table to bring in the correct file - and extracted the date from it using a rule.

Now I need to create a couple of files from this source file - so I derive 5 tables from this table - each having different calculations - so filter on different columns etc to create 5 different files.

These are the files that I am trying to rename using a date calculated based on the date in the original filename.

I don’t know how to get this date and calculate a new expression based on it.

I hope this helps explain a bit more on what I am trying to achieve.

Thank you once again for your help, I really appreciate it.

R

5

Hello R,

For the process you are describing, I would recommend iterating using the table? If you already know the filters, it will become easy to pass the date as a parameter for each iteration. From here, you can define a dynamic parameter to utilize the file date for naming.

6

Hi @Leopoldo_Ochoa_D

Ah ok I’ll give that a shot.

You’re a star, thank you so much.

R