How can I create a CSV file from a directory listing with multiple columns based on the file names?

Question

I have a list of image files, which are scans of receipts. They look like this:

gas_20160710_3432.jpg
gas_20160810_242.jpg
water_20161004_4510.jpg

I would like to be able to take the list of files and turn it into a CSV file, which I can do with the command 'ls' > files.csv.

However, I've taken care to make the file names contain a little information about the contents, and I would like to parse the file names so I can more easily work with the data when editing the CSV file in LibreOffice Calc.

Each file name has the type of receipt it is, then the date in YYYYMMDD format, and then a money amount that is written on the receipt. So, I'd like to separate that information out into different columns, as well as have one column with the full filename. So, the end result should look like this:

There is also one other issue. The date needs to be converted from YYYYMMDD to YYYY-MM-DD. Without the dashes, LibreOffice Calc seems to get confused.

Is there a way to create the CSV file I want from the directory contents from the command line?

Note that there are no other files in the directory other than the receipt scans, so there does not have to be any filtering of file types or excluding files with differently formatted names.

Answer 1

Using perl:

ls | perl -pe 's/(.)(.*)_(\d{4})(\d{2})(\d{2})_(\d+).jpg/\u$1$2,$3-$4-$5,$6,$&/'

In perl, (...) is used to group matching text, so (.) is a group with a single character, (.*) is a group with an arbitrary string, (\d{2}) is a group with two digits (\d is a digit, and {2} indicates two of the previous) and so on. \u converts the next atom to uppercase. $1, $2, ... are the various groups in order. $& contains the entire matched text.

Output:

Gas,2016-07-10,3432,gas_20160710_3432.jpg
Gas,2016-08-10,242,gas_20160810_242.jpg
Water,2016-10-04,4510,water_20161004_4510.jpg

You can add the headers independently.