Create a column Month and populate with the file name

[Maounde]

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 solution]

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 solution]

Hi

You can use the following formula

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

[Maounde]

Hi @Gautams091

That works too. Thank you. It is more complicated for a beginner like me, but gives me an opportunity to learn by taking the formula apart.