Add new month\year column to an appended spreadsheet.

Question

Seeking help adding a month\year column to a spreadsheet that I pull in information from a set of monthly files that I place in a folder each month. I have attached a screenshot of the file folder structure.

I append all the *alm_hardware.csv files into one spreadsheet called YTD SN Data.xlsx. Using the output file name as a field Column V(file name)has the name of the file that I appended. I would like to, now,  pull the month and year from that file name and create a new output with the month/year column added. Any ideas or a better way to perform this task?

Thanks

Tracey

Service Now Asset Data.yxmd

Column V.PNG

File Folder.PNG

AngelosPachis · Answer

Hi @traceystone ,

You can make use of regex to parse the month and year from the filename. The first thing you have to do when using regex is to identify a pattern and then come up with a correct syntax for the expression.

In our case, I noticed that the filename has some characters at the beginning, followed by a space and then 4 digits that stand for the year followed by an underscore and then the rest of the filename.

In regex that would be :

\w+ : one or more alphanumeric characters

\s : a single whitespace

\d{4} : exactly 4 digits

Then because you want to extract that pattern, you need to wrap it in a captured group by using brackets ().

So that would be :

(\w+\s\d{4})_

example.yxmd