How To Create a CSV File

A CSV file is a comma-separated value file that is used to transport data between databases, spreadsheets or systems. It is an almost universal method that would allow you to download a product list from the web and import it into Excel without losing its meaning for example. Creating a CSV file is very straightforward if you follow a few simple rules.

The use of commas to separate values is common in systems and works to our advantage here. The program you import data into generally knows to separate entries as soon as it sees a comma so a properly configured dataset can be accurately shared between systems. It is a very useful way to transfer data.

I use CSV files a lot when building product pages for websites. You can create a table very easily if you format a flat file (which is what the CSV is referred to as) and import it onto the site or into the CMS that controls the site. If you work in ecommerce or any web store, you will already be very familiar with these.

Create a CSV file

If you are a Windows user you would likely use Excel to create a spreadsheet. Apple users can either use Excel or Numbers. Either way, the process of creating a CSV file is similar.

Populate your spreadsheet with whatever data you need.
Select File and Save As.
Select a destination and then select CSV from ‘Save as type’ at the bottom of the window.
Select Save.

As long as the data is in the correct format, it should save correctly. If you see an error such as ‘This file may contain features that are not compatible with CSV’, this means there is formatting in the data that will not be used when you open the file. Just select Yes to continue saving.

If you plan on using the file in Excel from now on, it would be a good idea to save a copy as an .xls file. This enables you to expand the data into multiple pages, add formulas, formatting and all that good stuff that you can do with Excel.

If using Numbers, you may need to remove formatting before importing into it.

Paste your data into TextEdit and change the format to plain text.
Save the file with a .csv file extension. Manually change it in Finder if you cannot save it as a .csv.
Open Numbers and open the file you just created.
If the format is correct, save a fresh copy so you can open it faster next time.

As long as the formatting is correct and you saved the original in plain text and not RTF, the sheet should open correctly in Numbers just the same as it would in Excel.

Creating compatible CSV files

Aside from making sure each data point is separated by a comma, what else does a fully compatible CSV file have to have? CSV is quite the flexible format but following a few logical rules can ensure your file is compatible with the widest selection of applications.

First and foremost, the comma. All data points must be separated by a comma to be fully compatible with most spreadsheet programs. You can use a pipe or tab (tab delimited) but not all spreadsheets will format it correctly.
Use separate lines for each record. You can run a line for as long as you like as long as it is a single record. Multiple records should have a line each.
Don’t use a space between comma. While Excel ignores the space, some older spreadsheet programs do not. For maximum compatibility, don’t add the extra space between the data point and the comma.
Use double quotes if a data point includes its own comma. For example, “Chicago, IL” needs “” to tell the spreadsheet that the comma between Chicago and IL is supposed to be there and does not form part of the formatting.
If data points include double quotes, they need double double quotes. For example, “Dave says this product is way cool” needs to be formatted ““Dave says this product is way cool””. This tells the spreadsheet to include the inner set of double quotes.

There are a bunch more ‘rules’ for creating compatible CSV files. You can find them all here on the CSV Wikipedia page. I consider these as the only ones you really need to know if creating the majority of flat files for use in spreadsheets or online.

Got any other tips for creating CSV files? Tell us about them below if you do!