Understanding CSV Files

CSV files are plain text files that contain spreadsheet data. They are useful for importing or saving data regardless of the spreadsheet app you are using.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Let's take a look at CSV files. 
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
So CSV stands for Comma Separated Values. CSV files are a way to store data in a very simple format. They're basically plain text files that contain a spreadsheet. Each row of the text file is a separate row in the spreadsheet. Each column is separated by a comma. Thus Comma Separated Values. The advantage to a CSV file is it is not proprietary. It's not a Microsoft Excel file that can only be opened in Excel or an Apple Numbers file that can only be opened in Numbers. It's a very basic format that can be opened in either of those two apps or other spreadsheets like Goggle's Sheets. So when a website wants to provide some data in a very basic format that doesn't rely on the other person having a particular app a CSV file is a great way to do that. 
Here is a sample CSV file. If I were to Control Click on it and Open With, TextEdit, you could see how it's a very simple looking file. There's just each row here with commas for each column. Now your Mac is smart enough to know that if you QuickLook a CSV file then it comes up in a little table here. So you can see the data in it. It's not just going to show you the plain text. Yet you can edit the text in a Text Editor. So if I opened it in TextEdit and say change this value right here and Save it, now if I use QuickLook you can see that new value is there. Furthermore the default app for opening up a CSV file should be Numbers. If you double-click then it should open it up and you see it as a Numbers spreadsheet with the data from the CSV file in a single table. 
If Numbers isn't what you get when you click a CSV file you could always select the file, choose File, and then Get Info, and then go to Open With. Then you can change which app is used to open that file. Or if you select an app you can click the Change All button there and it will change it so that any CSV file that you double-click on will open in that app. 
So as I mentioned before you can Open With Numbers and it is probably the default app. But you can also open in any other spreadsheet app. I don't think there are any spreadsheet apps out there that wouldn't open a CSV file. So, for instance, I can open this in Microsoft Excel. You can see how it works just as well here. 
Creating a CSV file from scratch is easy to do. You can just open up a Text Editor, like TextEdit. Make sure you're in text editing mode. So, for instance, if you see make plain text here switch to that. In this case I'm already in Plain Text Mode. I can type in some information like this along as I keep track of the commas to make sure all of the columns will workout. I could Save this file. Then instead of saving with a .txt extension use .csv and save. Now when I double-click on this it will open up in Numbers and there's my data. 
Likewise when you download data from a website it almost always gives you a CSV option. So here I am at the US Census Bureau site. I want to download some data. Notice that I've got various options here. One of them is CSV. If I click here to download CSV I can download this file. Here it is. I can use QuickLook to preview it. I can double-click and it will open up in Numbers. 
Note that if I take a look at this file in TextEdit you could see it is a bit more complex than our simple examples from before. One of the things you'll notice is the use of quotes. So Quotes are used around any data. So when you have an open quote you can include any text you want and then a close quote. Then you would have a comma before the next value. To be safe a lot of CSV documents will just put every value in quotes. Even if it is just a plain number and doesn't need it. But you can see here an example of how commas are used inside the value. So if it wasn't for the quotes on either end it would process this as three separate numbers instead of one large number. 
In addition you could export from Numbers or any spreadsheet app as a CSV file. So you can go here to File, Export To and one of the options is CSV. I can Save this out. I'll save it. Calling it example 2. If I open this with TextEdit you can see the resulting data. It is just a plain text file and can now be opened in Excel, imported into Goggle Sheets, or in any other spreadsheet app. 
So if you use CSV files I hope that this gives you some background to be able to use them more effectively. Thanks for watching.

Comments: 6 Responses to “Understanding CSV Files”

Neil Shelley

2 years ago

Any excellent short video for those new to CSV files - something similar for PDF files would be equally useful 😊

Jasper

I found an app on the AppStore, EasyCSV Editor. It opens .csv files but presents them in a spreadsheet format. This means that you can edit a single .csv without creating additional file types; and you can clearly see what you’re doing (ie, the column line up). It also has quite good regex, sorting and summary features. I often work on very large .csvs so it’s saved a lot of time.

aurelio

1 year ago

HI, thank you for your videos ans tutorials. I'm trying to manage my password in safari, exporting on a .csv files, but when I try to import a file in my password manager I get this error "Safari could not import passwords from the CSV file because it is missing column labels". How can I solve?
thank you

Gary Rosenzweig

aurelio: look at the exported file and make sure your import file has the same exact columns.

I export the file, open it in excel, work on it without changing columns and save it as a csv file again but when I try to import in safari I still have the same error I reported above. I even try to do it with numbers, but same result "Safari could not import passwords from the CSV file because it is missing column labels"

aurelio: Compare your original CSV to your exported new CSV and see what is going on.

Comments Closed.

Understanding CSV Files

Comments: 6 Responses to “Understanding CSV Files”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information