How To Import CSV Files Into Mac Numbers

Hi, this is Gary with MacMost.com. Let me show you how you can import CSV files into Numbers. 

This is a common question I get. How do you get a CSV file and bring it into Numbers? CSV stands for Common Separated Values. It's  very common format to download data from websites, like for instance from your bank or maybe a statistics website. These are little spreadsheets that are basically just simple text files. Each column is separated by a comma and then each row is a new line. So since these are basically simple spreadsheets you'd think you would be able to easily import them into Numbers. But if you look around through the menus there's no Import Option. The only mention of CSV is under Export. So at a glance it looks like it's not possible. But it in fact very possible and in a number of simple ways. 

For instance, here I've got a CSV file. Something I dowloaded from a site online. You can see if you right click, control click, two-finger click on it you get Open With and you see here that Numbers is the Default App. So macOS treats it as a file to be opened in Numbers, even though it is a text file. You can open it in other apps if you want. For instance you can go down to TextEdit, select that and here's the file in TextEdit. It's good to look at it here because you can see it is just a simple text file. It's just commas separating each cell in each row and then a number of rows. So if you just double click on this it will open up in Numbers. 

In fact it will create a new document and bring that data into a table here in that document. You could see it here. You also get this little indicator here, Table Data Was Imported and Can Be Adjusted. If you click that you actually get this special control here where it shows Import Settings. You can change things. So if it didn't import it quite right you can look at this and make changes. Like for instance you could say, well it's Tabs not Commas that separating things here. You can see that is not the case. It is in fact commas. You can actually have Tabs and Commas and semicolons and spaces be separators. So you just want to make sure you get these right. In this case it is a standard CSV file so just commas is all that is needed.

But you can set a Custom Delimiter if you want. There are also advanced settings here. So you can change the texting coding if you're dealing with something more complex. You can have it start further down. So, for instance, you can have it start on Row 2 and not have this Header Row. Sometimes CSV files have all sorts of information in the first few rows so you can eliminate that here. Although it's easy to just delete that in the table later on as well. 

So this is easy and fairly straightforward if you want to create a brand new document from the CSV file and then you can immediately Save it and it will save out as a Numbers file. But you can also just launch Numbers and create a New Document here. With a new empty document you can import the CSV file into it in a few different ways too. For instance, you can just grab it and drag it into an existing table, like this blank table here. Then it is going to bring it in. Notice here how it kept these extra columns that weren't really needed and it also gave me this little control here just like before and I can adjust things. So I can bring things into an existing table. For instance I could decide I want to create a new table here. Maybe using a format like this, but then change the style like that. Then I could bring the data in like that and it will keep that existing style. 

Now what if you want to add to an existing table? You can do that as well. So here I've got a spreadsheet that has financial data and I want to bring in the next month's data. Say this is a file I get from my Mac. So I can see this ends at the end of March and I've got a file here that has April's data. So what I can do to bring that in is do what I would do to add a new row here. With this cell selected I'm going to press Return like I'm about to enter data in. Now I'm going to take this file and drag it in here. It's actually going to add it to the table. Now I'm going to need to adjust it here. You can see how it brought the Header in here. I can click here where it says Adjust the Table Data and I can use some of those advanced settings. For instance I can say, Start at row 2 there, like that. You can see I can update the Import. I could have also just deleted that row here. 

An alternative method that I like to use especially if I've corrected some of this data and I know I'm going to need to do it with the new data as well is to simply add a new sheet and then bring in the new data into a new table there. Then I can go through and adjust what I want. Maybe there are some things I delete out of this. Maybe I need to adjust some formatting. Then I could select the rows that I want to bring over into the existing table. Copy them, just a quick Command C, go back here and then start a new row and Paste in. Either way, whether you add it directly to the table and then adjust it, or use a temporary table to make adjustments first, you'll end up in the same place. It's all what you prefer. 

If you find these videos valuable consider joining the more than 3000 others that support MacMost through Patreon. You get exclusive content, course discounts, and more. You can read about it at macmost.com/patreon. 

Now you can also use Copy & Paste to do this. So you could do something as simple as selecting a CSV file in the Finder here. Here I've got one on the Desktop. I'm going to use Edit Copy or Command C to copy. Then I'm going to go to Numbers here and I'm going to select the first cell in this table here and I can do Command V to Paste. It will paste in, even giving you this chance to adjust right here. You could also have done that with a blank sheet here. Just Command V will paste it in just like you were dragging it in. But you can also Copy & Paste the whole or a portion of a CSV file by opening it up in TextEdit. Now we can do Command A to select All and Command C to Copy. Or we can just select a few rows which might be a good reason to do it this way. Then you copy just these and then you go over here to the table and then Paste and you see you get the same adjustment chance there and you've only pasted in the portion you wanted. 

Now another way you can do this is by using QuickLook. So you can select a CSV file in the Finder, in a Finder Window, or here on the desktop. Press the Spacebar and QuickLook will recognize CSV files and display them in a table rather than just as plain text and you can select everything here with Command A or you could select just some of the data using the pointer like that and use a Command C to Copy. Then you can go into Numbers and Paste. This can be handy to add just some data to a table like this as well. I can select this CSV here and let's say I just want to import, say, these items. I can easily Copy them in QuickLook and then go here into Numbers and Paste just those items in. 

Now CSV files can vary in quality greatly. So, for instance, this particular one here has all this information. It's pretty obvious what these things are. But there's no Header Row at the top. So if double click it to open it in Numbers you could see it takes the first Data Row here and uses that as the Header Row. That's not what I want. So what I'm going to want to do here is fix it. So you can do that in many ways. I'm going to add a new Header Row there and then I'm going to go into the Sidebar here and say I only want one Header Row. So it'll convert this one to a regular data row. Then I can manually type in the different headings I want up there. 

Also note that when you open a CSV that's got Dates in it sometimes the dates aren't recognized as dates. If I select this date here and I look carefully at the bottom left it shows that it is text. You want to use them as dates for calculations and such. So what you want to do is select the entire column here, I'll just double click on A to select all the values, and I want to go to the right Sidebar to Cell and to Data Format and I want to tell it specifically that these are dates, like that. You could see how it changes to right justify them. If I select one it's says Actual at the bottom now. In fact you may have notices on my last Copy & Paste import here you could see how these are dates but the new rows I brought in, they are text. So I want to go and select these, or I can just say select the whole row here, and just tell Numbers that, yeah these are all supposed to be dates there. It will make sure they are. So you can see you can even set the date format here if you want. You can check and see that's Actual now.

But notice that it still thinks these are left justified and the font is not Bold. So you have to, sometimes, go in and do other things as well. So I'll make this right justified like everything else and I'll make it All Bold, like that. It's even different fonts here. So you want to make adjustments as you need. 

A tip is you can avoid some of that by not pasting any styling in. So, for these rows I went into this table here and I selected some of these rows. So I'll do that again here and I'll Copy. What I'm going to do now is if I paste here you could see it's going to use a slightly different font and everything. But instead if I go to Edit and then I say Paste & Match Style then it's going to paste it as plain text. So now it looks a lot better. These still aren't recognized as dates but I could just go in and change those and everything else will match fine. But you have to keep your eyes out for other little problems. For instance here it put a merged-cell there so there is no F column cell here. I can fix that by going to Table, Unmerge Cells, like that to correct that problem. 

Sometimes if the text is really messy you may want to open it up in TextEdit first and work with it there. For instance here the Header Rows don't look really good. So you can maybe work with them to make them look better. TextEdit also has really excellent Find & Replace Functions. Just do Command F and you could  search for anything that you want and replace it with anything and you can even include things like Tabs and Line Breaks and such when searching even wild cards.So you can clean up text a lot before bringing it into Numbers. 

Even so, you often end up with very messy imports if the CSV file has got a ton of junk in it. Like for instance this CSV file here is just supposed to be the population of the US in each year. But there's a ton of extra stuff in here. You could see all these footnotes at the bottom. So you have to, say, go in and delete these rows. Then you've got these columns that aren't really useful. For instance you can delete this column here. These columns aren't needed either. So you can delete more. You've got some other ones here, like that. You just want to end up basically with the year and the number. But notice these numbers here are in thousands. So you may want to add an extra column. Put a formula here that takes this number, multiples it by 1000 like that and then Copy & Paste like that. Again copy these numbers, the real values here. Go in here and then do Edit, Paste formula results. So now you've got actual numbers here. You can get rid of this column and then improve these Headers here. Maybe improve the title. Let's sort it in the right direction that we want. Maybe even go in here and make these better by doing a Comma Separator like that. 

So you can see you can really improve the data by cleaning it up after you import this CSV file. Hope you found this useful. Thanks for watching.