1

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:

enter image description here

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.

1 Answer 1

2

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.

4
  • Worked like a charm, thanks. The only things I noticed was that I needed to add . filename.csv at the end of the command to actually get the output into a CSV file. Also, for reasons I don't quite get, the name of the CSV file was included in the CSV itself. I guess it creates the file before filling it. But, no big deal. I just edited the file as needed after creation. This command saved me a ton of work!
    – Questioner
    Jan 26, 2017 at 6:33
  • 1
    @Questioner yes, if you do > filename.csv, the file is created first. You can save it to another folder, perhaps: > ../filename.csv for saving in the parent directory.
    – muru
    Jan 26, 2017 at 6:34
  • Just another note. When I opened the resulting CSV file in LibreOffice Calc, the date fields are marked as text, with an apostrophe character included in the cell that is not visible unless editing the cell. At least, in LibreOffice Calc. I don't know if that formatting option is a result of this command or LibreOffice Calc's conversion process. I found a way to resolve this here.
    – Questioner
    Jan 27, 2017 at 12:27
  • @Questioner it's the result of a lack of conversion - by default Calc assumes text, so you should set it to date (YMD): i.stack.imgur.com/w2MWC.png
    – muru
    Jan 27, 2017 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.