easy excel powerpivot for the data analyst one importing data
hey alright welcome back to the mystic celt NetCast I'm bill jelen we're
starting to work through the powerpivot for the data analyst book and this first
topic here is how do we get our data into powerpivot alright so you can see
here we have the powerpivot ribbon tab up here and so we have successfully
installed power given out to get data in how we could copy and paste from excel
we could create a linked table if we did define a table Excel but today i have a
data set that is external so it's not an excel file right now if I had data that
was in sequel server it or access i could do that from other data sources i
can do that but me I'm kind of embarrassed my day today is just a
simple old txt file so i say from textnow the very first thing you want to
do in this dialog boxes make sure to choose used first row is column headers
before you start to browse because it's going to start to pull that data in
right after we browse at least the first 50 records or so and so we don't we
wanted to know that there's column had a heads up there
see this file demo . text kind of an interesting file it is 58 megabytes of
data to choose that click open and they're going to take a look at the
first 50 records here and build a little preview that shows us the various fields
that we have now if you had a field especially a long text field that you're
not going to be reporting on consider not importing that feel that will save a
lot of space and Excel workbook but here this is a pretty small little file you
want to import all of the fields we have so I go ahead and click finish and Excel
will start to import that now i'm not going to bore you
as we sit here and watch this import it's going to take as long as it takes
if you have a hundred million rows this might take 20 or 30 minutes there's
nothing magic about the importing of data so we'll just hang out here we'll
pick up again in a few and probably about a minute
okay we're just about done it's up to 1.8 million rows pretty soon here we
will get the message to actually will do is the stop import will change too close
and you know that we've pulled in the entire file so we click close and then
here in the powerpivot window we have those 1.8 million records watch how
easily I can scroll through this data I can sort the data I can filter the data
very very cool way to go and what's really amazing that was 50 megabytes of
data if i go back to excel i do file save as
we'll save this workbook that fifty eight megabytes of data gets crunched
down using the vertical a compression to let's see what it comes up with here
powerpivot one is now three meg so from fifty eight megabytes down to 3.1
megabytes incredible the verb a compression now in future videos we'll
look at how to link tables how to create relationships and do all that but for
right now that's our first video how to get data into thank you stopped by
next time for another NetCast for mr excel