Hi, I’m looking to take a spreadsheet (CSV) of 100 rows - two columns, the first column is a “full name,” the second column is an “email address,” - and convert the spreadsheet to 100 markdown files, with the file name “full name,” and the body “email address.” Would appreciate any pointers, thanks!
“Split Text” will let you split the CSV in lines (rows) and each line in fields (cells). “Get item from list” will let you pick a column (cell) of each row. With “Set name” you can change the name of the file before saving it. And put those files in a list, and you can save them all at once.
If you are in Excel, you could add a formula like this in a third column.
=CONCAT("echo """, B1, """ > """, A1, ".md""")
Then fill down for your rows to generate some output in this sort of form.
That effectively creates a bunch of simple terminal/shell commands to create the files.
echo "j.smith@mail.com" > "John Smth.md"
echo "Joe@blogg.com" > "Joe Bloggs.md"
echo "Jane.doe@com.com" > "Jane Doe.md"
echo is taking the e-mail address, and the > is directing it into Markdown file of the specified person’s name.
You can put the commands in ascript in the folder where you want to create the Markdown files, and run it so you don’t have to run them one at a time in the Terminal app.
e.g. make a text file called “mdfiles” in the folder where you want the files and add the ‘bash’ line (a wayt to say what type of script it is) followed by the generated lines from Excel.
#!/bin/bash
echo "j.smith@mail.com" > "John Smth.md"
echo "Joe@blogg.com" > "Joe Bloggs.md"
echo "Jane.doe@com.com" > "Jane Doe.md"
Then from the Terminal app, use the cd command to change to the corect folder if necessary, and use the command sh mdfiles in the folder to build them in that folder.
If you already have the CSV and this was something you were doing regularly, I’d be tempted to write a small shell script using AWK to take the CSV and process it directly. But I get the feeling this is a one off and given you’re working with a CSV in Excel, that you are more comfortable there.
1 Like
Thank you very much for this detailed solution. I will take this solution and work on it.
Thank you very much.
Thank you very much, I am good to go!
Hi, this was really good and easy to follow thank you!
I am working on a project where I need to generate md files for each museum object with information at the top of each page that needs to be on separate lines.
eg.
-–
label: The Zebra
layout: entry
order: 301
object:
- id: “obj-1”
The excel is set up so it compiles the necessary info, correctly formatted, into a single cell I can copy and paste into a md file.
I have tired to use the same formula to create and populate an md file with this info but it is not working:
echo "—
label: The Zebra, or WILD ASS
title: obj-1
layout: entry
order: 301
object:
- id: “obj-1”
—" > “obj-1.md”
I suspect the special characters for the lines breaks is breaking it as I can get the same file name to work with one or two cell references without the formatting to work.
Just hoping someone might have a suggestion that would allow me to auto populate the information without having to manually copy and paste!
Thanks a mill!
Same concat principle, but if you have multiline cells then using a different command that will accept a multiline input should help. Try it with printfs %s instead of echo.
=> So I just have =CONCAT(A1,B1,C1) for Cell D1.
Thanks for your reply!
I have tired running the command and it is not working.
I am probably doing something very basic wrong as I’m pretty new to any sort of coding. but I’ve looked online and not found an answer yet so would be grateful if you had any ideas?
I can see your problem. This topic was converting rows of Excel into individual Markdown files in the macOS section. You are doing this on Windows and using it from the standard CMD command prompt, which happens to share the echo command like the shell used on macOS.
But we can deal with Windows as well as macOS…
You could try and run your command in the Windows Subsystem for Linux (WSL) which would give you that same sort of shell experience, but I am going to assume you don’t have that option installed and would be unfamiliar with how to utilise it. If you did, you would just be able to use that instead of the CMD Command Prompt.
You could do it with PowerShell with a bit of modification, but given your newness to this I’ll stick to using the Command Prompt and do something different in Excel.
The formula I have used here might look a little more complex than the previously used one.
=TEXTJOIN(CHAR(10),, "echo """ & TEXTSPLIT(A1,CHAR(10)) & """ >> ""test.md""")
This is because rather than just trying to echo/print command out one multiline string, what I have done is modify the multiline string in the left cell to split it by line and then join it back together as multiple lines wrapped with the same sort of echo command as earlier.
But, the overall result is the same as back at the start when we didn’t have multiline strings.
Give it a try and see if that gives you some success.
Thanks a million that worked like a charm!
Really appreciate your help!
1 Like