In the Loupe

Converting CSV Tables to Markdown Files

Converting CSV Tables to Markdown Files Featured Image

If you build a lot of Statamic sites, you’ll eventually have to split Excel or CSV table rows into individual files. Here is simple technique that works on my Mac.

Export a CSV file that is easy to reformat

export csv settings

After examining my file “products.csv” file, I noticed that there were no tilde ~ glyphs in it, so they would make perfect delimiters. Also, just in case there were "" in the table, I skipped using those as text delimiters. My export screen looked like this in OpenOffice. And here is the output.

Changing CSV markup into YAML

gui grep in coda

The content of my CSV contained no arrays/lists so reformatting it is quite simple. In my text editor of choice, Panic’s Coda, you can find and replace using visual wildcard placeholders.

Of course, any text editor worth it’s salt allows you to grep with regular expressions just as easily.

Find this:

(.*?)~(.*?)~(.*?)~(.*?)~(.*?)~(.*?)~(.*?)

And replace with this:

@@@
title:\ "\1"
sku:\ "\2"
manufacturer:\ "\3"
image:\ "\4"
price:\ "\5"
dimension:\ "\6"
---
\7

Save this file as products.txt

Divide & conquer

Now you should have one large text file, but we need individual markdown files. Time to groom that neckbeard and open up terminal.

Navigate to the folder containing the products.txt file and run the command below. You’ll notice that this command is made up of 3 separate commands.

csplit -k -n 3 products.txt '/^@@@/' {'999'}; for i in xx*; do sed -i '' 's/@@@/---/g' $i; done; for i in xx*; do mv $i `egrep -m1 -e 'title:.*' $i | sed -e s/[^\]\[A-Za-z0-9~.,_{}\(\)\'\-\+]/-/g -e s/title---//`.md; done

Let’s break it down

The first command splits the text file into individual files.

csplit -k -n 3 products.txt '/^@@@/' {'999'}

The next line finds “@@@” and replaces it with “—-“.

for i in xx*; do sed -i '' 's/@@@/---/g' $i; done

Next, we grab the “title” line of the file, slugify it, remove “title—-“, and then rename the file with it. Lastly we add the “.md” suffix to the end.

do mv $i `egrep -m1 -e 'title:.*' $i | sed -e s/[^\]\[A-Za-z0-9~.,_{}\(\)\'\-\+]/-/g -e s/title---//`.md; done

Cleaning up

The last step is prefixing the files with numbers to order the markdown files. Since I use Path Finder for my finder needs, I rely on it to batch prefix the file names with numbers.

Gotchas

You’ll want to make sure that each title: is unique, because when the terminal command names the files, it will just copy over files of the same name.

If your CSV has more than 1000 rows, you’ll need to increase the limits of this line to csplit -k -n 4 products.txt '/^@@@/' {'9999'}

Have I missed something? Is there an easier way?