https://macmost.com/e-3877 Not only is it easy to import CSV files into Numbers, but you can do it in a variety of different ways. However, after importing you may need to clean up the data.
00:00 Intro 00:56 Double-Click To Open a CSV File In Numbers 01:47 Adjust Data Right After Import 02:57 Drag and Drop CSV Files Into Existing Tables 03:44 Add New Rows To An Existing Table 04:34 Use a Temporary Table First 05:27 Import CSV Files With Copy And Paste 06:27 Copy And Paste From Quick Look 07:15 Cleaning Up 10:10 Clean Up Data Before Importing 10:43 Clean Up Data After Importing
This video provides a comprehensive guide on importing CSV (Comma Separated Values) files into Apple Numbers. It covers various methods for bringing in data, adjusting import settings, and cleaning up the resulting tables to ensure data accuracy and professional formatting.
Hi, this is Gary with MacMost.com.
Let me show you how you can import CSV files into
Numbers. So this is a common question I get.
How do you get a CSV file and bring it into
Numbers? CSV stands for comma separated values.
It's a 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'd be able to easily import them into Numbers.
But if you look around through the menus, there's no import option,
and the only mention of CSV is under Export.
So at a glance, it looks like it's not possible.
But it's in fact very possible, and in a number of simple ways.
For instance, here I've got a CSV file,
something I downloaded from a site online.
You can see if you right-click,
control-click, or 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's 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's 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, we'll 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 and 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's 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's a standard CSV
file so just comma is all that's needed.
But you can set a custom delimiter if you want. There are also
advanced settings here.
So you can change the text encoding 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.
And 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's 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 and 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 bank.
So you can see this ends at the end of March
I've got a file here that has April's data.
So what I could 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 I 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.
Like for instance, I can say,
yeah, start at row two there like that.
And you can say, 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 and then I can go through
and adjust what I want. Maybe there's some things
I delete out of this. Maybe I need to adjust some formatting
and 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 slash Patreon.
Now you can also use copy and 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 a Command V will paste it in just like you were dragging it in.
You can also copy and paste the whole
or a portion of a CSV file by opening it up in
TextEdit. Now we can do Command A, just select All,
and Command C to copy. Or we could 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 Quick Look. So you can select
a CSV file in the Finder in a Finder window or here on the Desktop.
Press the Spacebar and Quick
Look will recognize CSV files
and display them in a table rather than just as plain text. And you can
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 Quick Look
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 I double click it to open it in Numbers,
you could see it takes the first data row here
and uses that as a 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.
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.
And 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's 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.
I want to go to the right sidebar to Cell and to Data Format.
I want to tell it specifically that these are dates like that.
You can see how it changes to right justify them
and if I select one it says actual at the bottom now.
In fact you may have noticed on my last copy
and 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 could just
say select the whole row here
and just tell Numbers that yeah these are all supposed to be
dates there and it'll make sure they are.
So you can see you can even set the date format here if
you want and 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. And it's even different fonts here.
So you want to make adjustments as you need.
A tip is you could 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 and 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 could 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 a really excellent find
and replace function just to 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 wildcards.
So you can clean up the text a lot
before bringing it into Numbers.
Even so, you often end up with very messy imports if the CSV
CSV file's got a ton of junk in it.
Like for instance, this CSV file here.
is just supposed to be the population of the U.S.
in each year, but there's a ton of extra stuff in here.
You can 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.
So for instance, you can delete this column here.
We'll see you next year.
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,
multiplies it by 1000 like that,
and then copy and paste like that. And 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.
And 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 the CSV file.
Hope you found this useful.
Thanks for watching.
Want to know about every new MacMost video each week?
Sign up for the absolutely free MacMost email newsletter
at macmost.com slash newsletter.