Excel Magic Trick #133: Import CSV Data (Comma Separated Values - Data)

See how to import files with the extension .csv. See how to use the Text Import Wizard to import data into Excel. See how to use the Text To Column Excel Feature. Comma Separated Values - Data

---

Closed Caption:

welcome to exhale magic number a hundred
and thirty-three
hey if you want to download this
workbook and follow along click on my
youtube channel then click on my college
website link and download the workbook
XL maxi took 133 - 1:45 a trick 133 will
talk about see as V data that means ,
separated values and oftentimes you can
export from all sorts of other programs
whether it's a database or an accounting
program or even a word or excel and it's
great because then you can export it as
csv and then import it into something
like excel so although some other
programs may not be able to speak with
Excel you can save create a CSV file an
important i'm going to show you three
ways
first we're going to actually copy some
CS v data from a word document and paste
it into our Excel workbook
i'm going to go over to windows explorer
and I have some files here i'm going to
open this one right here and we get this
,
this is not a it's just a dot docx file
but in here we have the field name
separated by commas and all the data
elements separated by columns in each
row is a record
so I'm going to control a to select it
all and then ctrl-c to copy and i'm
going to alt-tab to go back over to
excel
i'm going to insert a new sheet the
keyboard shortcut for inserting the new
sheet is shift f11 that works in all
versions i'm going to double-click it
and call it data one
now i'm going to paste it in cell a1
control V and there we have it now
notice we have 514 records with some
field names in the top everything still
highlighted now you all we need to do is
text to column now in 2007 to go to the
data ribbon text column in 2003 go to
the data menu text to column
this opens up to convert text columns
wizard it says is the data delimited or
fix with this means that same with for
each column and this means separated by
some character
hey we have a comma separating so there
it is
you can have tabs or any other type of
character
i'm going to click Next notice the
preview here right click Next
Oh looks like i already had , selected
but usually it comes up with tab here
you just click on , notice when we do
that here's the data we click on common
instantly it shows us a preview that's
pretty awesome right click and if you
have other characters you can just check
this then type whichever character it is
click Next
you can import or choose to not import
so you could click on one of these
columns and say do not import or you
could format in general general works
pretty well it'll take dates and make
them dates numbers and make them numbers
with format
oh so i'm going to select that the only
a one useful trick here is if you get
dates imported as text you can actually
use this and click on this and all
change them two dates
so that's fine to have general and
there's our destination and then we
click finish group and just like that we
have all of our data now let's insert a
new sheet shift f11 and i'm going to
call this data
- we'll come back to that in just a
moment or actually let's go ahead and
try that we're going to import that this
was a word document right but now we're
going to us from inside of Excel import
and if we go over to windows explorer we
can see that we have this text data that
text if we double-click and open up you
can see it's just the same thing but in
a txt extension file type
i'm going to close that and now i'm
gonna go to data
and then we need to get external data I
have notes in this little sheet here for
how to do this in 2003 but it's
basically data menu get external data
and from texts and now we're going to go
find that file
I have to navigate to wherever it is
there it is i'm going to double-click
and sure enough we see this text import
wizard same thing but with a different
title at the top here is that one we saw
earlier delimited because we have commas
started import row that's fine we have
our preview here
click Next , but remember that from
before
click Next and will say general click
finish this is where you want to save it
right there i'm going to click OK
just like that now one other method and
that we can use
let's go over and look at this these
files right sometimes actually you get
can save as in Excel right and it saves
it as a CSV if that's the case you can
just open this double click and open it
that's an excel file and see the
extension right there you can then hit
em f 12 which is save as and then change
the extension here to whatever you want
exhale ask for instance
save as we got their data xls and click
Save and sure enough now we have that
workbook there so that's a third method
let's look at a fourth method and this
will involve using open remember we have
this text right here you can actually
just open it once i'm going to control
and for new and then control
Oh for open and out the trick here is we
want to say file types
when you click the drop-down out and say
all that way you can actually open it
and so we see this right here we
double-click and open it and it opens up
the text wizard so delimited next , next
general and then finish the loop and so
there it is
data . text and let's save as and change
this to XL
yes and i'm going to call this data to
so there you have it there's some
methods of how to deal with ce s v data
for trick 133 all right we'll see you
next trick