Can I format data in a CSV?
Before we set off, let's begin by stating exactly what we mean by formatting. We will be talking about here formatting as it relates to the actual data in your file - things like the text, tables, and so on that you've created or edited in your file. Not to be mistake by file formatting (i.e. the type of file you have and how it should be opened, like ".xls" or ".doc" and so on). If you want to know more about saving your file as a different file format, head on over here for our coverage of exactly that!
Now that we've gotten that out of the way, let's circle back to another foundational topic - CSV. Before we can explain how formatting and CSV interact, you should remember what CSV is. CSV is plain text, separated by commas. I repeat: plain text. This should give you a clue to what comes next...
Now that we've gotten that out of the way, let's circle back to another foundational topic - CSV. Before we can explain how formatting and CSV interact, you should remember what CSV is. CSV is plain text, separated by commas. I repeat: plain text. This should give you a clue to what comes next...
Data saved in CSV will not retain data formatting.
Okay, we got to the spoiler early. Let's take a look at what this actually means for you and your data.
It's a very common scenario: you are working on a spreadsheet in Excel (or Numbers, or Google Sheets, or Open Office, etc.). You spend no small amount of time getting the data to look just right. You've created a table with bolded headers, shaded rows, gridlines, italicized key points, added formulas to calculate key information, and so on...You're ready to share this beautiful spreadsheet with a colleague, you save it to CSV (a universally loved format after all!) and poof! It arrives looking as plain as an unbuttered bagel.
Or, perhaps, you are getting ready to upload data to one of your business apps. You have hyperlinks that need to be retained, you have included bullet lists to keep your text clear and structured, and a column of notes that have been meticulously highlighted. You go to import, and what arrives in your database looks nothing like the file you created. All that formatting, gone. Now you've lost key information (what were those hyperlinks to?) and your data is unorganized and unfocused.
We've painted a grim picture here, but it's a common feeling - that pit in your stomach when you've lost the formatting that was critical to the integrity of your data.
Okay, we got to the spoiler early. Let's take a look at what this actually means for you and your data.
It's a very common scenario: you are working on a spreadsheet in Excel (or Numbers, or Google Sheets, or Open Office, etc.). You spend no small amount of time getting the data to look just right. You've created a table with bolded headers, shaded rows, gridlines, italicized key points, added formulas to calculate key information, and so on...You're ready to share this beautiful spreadsheet with a colleague, you save it to CSV (a universally loved format after all!) and poof! It arrives looking as plain as an unbuttered bagel.
Or, perhaps, you are getting ready to upload data to one of your business apps. You have hyperlinks that need to be retained, you have included bullet lists to keep your text clear and structured, and a column of notes that have been meticulously highlighted. You go to import, and what arrives in your database looks nothing like the file you created. All that formatting, gone. Now you've lost key information (what were those hyperlinks to?) and your data is unorganized and unfocused.
We've painted a grim picture here, but it's a common feeling - that pit in your stomach when you've lost the formatting that was critical to the integrity of your data.
It's not all bad news though!
For those that are sharing data, the simple solution is (if possible) don't save your data to CSV format. Use the default file format of the program you used to create or edit the file (i.e. ".xls" for Excel or ".numbers" for Mac, etc.). Assuming your recipient can open this file type, you will have no problems with retaining your data formatting for the next person to see.
When you need to move data, like importing data into your business apps, you may wish to:
Yes, the last one seems harsh. But realistically speaking, you may not be able to import all the formatting in your file. Better to skip the frustration and avoid the wild goose chase for a solution that doesn't exist.
For those that are sharing data, the simple solution is (if possible) don't save your data to CSV format. Use the default file format of the program you used to create or edit the file (i.e. ".xls" for Excel or ".numbers" for Mac, etc.). Assuming your recipient can open this file type, you will have no problems with retaining your data formatting for the next person to see.
When you need to move data, like importing data into your business apps, you may wish to:
- Confirm if your app supports other file types (like ".xls" for example) AND that they support importing formatted values (bulleted lists for example)
- Check if your app has a specific method for importing formatted values (take Trello for example, which uses markdown language to solve this import problem)
- Come to terms with the fact that you're not going to be saving your formatted data
Yes, the last one seems harsh. But realistically speaking, you may not be able to import all the formatting in your file. Better to skip the frustration and avoid the wild goose chase for a solution that doesn't exist.
So remember, these are things that will not be saved if you save your file to CSV format:
At the end of the day, it's good to remind ourselves that, CSV is plain text. We can't ask of it more than it can provide. When you want to get fancy with your data, better to part ways amicably and leave CSV to do what it does best.
- bolding, italics or other types of font formatting
- highlighting or other color formatting
- formulas (while not strictly formatting, per se, it's important to note that these will save as plain text, i.e. you'll only see "the results" and lose the formula itself)
- hyperlinks
- bulleted lists
- table formatting - things like borders, shading, header filters, etc.
At the end of the day, it's good to remind ourselves that, CSV is plain text. We can't ask of it more than it can provide. When you want to get fancy with your data, better to part ways amicably and leave CSV to do what it does best.