Create a column Month and populate with the file name

Question

Hello,

I am new to Designer and trying to achieve the kind of things I can do on Excel.

I have a set of 20 CSV files with the same structure. They are named "Forums.2018.03", "Forums.2018.04" as in March 2018, April 2018, etc.

I used Wild Card to input the whole set and it worked well.

The problem: I need to create a column titled "Month" that would have the month for each record on each file. I suppose the easiest is to create that column and give it the name of the file. How do I do that?

Any other simpler method to make this happen?

The date of creation of the files is not the right approach since I downloaded the files a few days ago.

I'll need another column that indicate the year, but I suppose if I get the month in, I can get the year in too.

Thanks in advance

Thableaus · Accepted Answer

Hi @Maounde

Here's how you could do it:

First, check the option to Output File Name as a Field - to File Name Only (in the Input Data Tool)

Then, you can use some string functions to get the Year and the Month out of that.

Here's how I did it.

Month - Right([FileName], 2)

Year - Left(Right([FileName], 7), 4)

Let me know if you have any questions.

Cheers,

gautams091 · Accepted Answer

Hi

You can use the following formula

DateTimeFormat(DateTimeParse(ToString(Substring([FileName],12,2)),"%m"), "%b")