In Chapter 2 of PowerPivot for the Excel Data Analyst, you will see how to import a 1.8 million row text file into the PowerPivot window.
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Power Pivot for the Data Anylast 1, Importing Data.
Hey, alright welcome back to the MrExcel netcast.
I'm Bill Jelen. We're starting to work through the PowerPivot for the Data Anylast book and the first topic here is: How do we get our data into PowerPivot?
Alright, so you can see here that we have the PowerPivot ribbon tab up here, and so we've successfully installed PowerPivot.
Now to get data in, we could copy and paste from Excel, we could create a linked table if we've defined a table in Excel.
But today I have a data set that is external so it's not an excel file right now.
Now, if I had data that was in sequel server and or Access I could do that from other data sources I can do that but I'm kind of embarrassed.
"My day today" is just a simple old text file, so I'm gonna say from text.
Now the very first thing you want to do in this dialog box is make sure to choose "use first row as column headers" before you start to browse, because it's gonna 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 headers up there.
Right, see this file demo dot txt kind of an interesting file it is 58 megabytes of data so we're gonna choose that.
Click open and then gonna 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 field.
It will save a lot of space in the Excel Workbook, but here this is a pretty small little file.
I 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 gonna bore you as we sit here and watch this import.
It's going to take as long as it takes, so 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 we'll actually what we'll do is the stop import will change to 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.
A very very cool way to go.
And what's really amazing, remember that was 50 megabytes of data.
If I go back to excel, and I do File > Save As.
We'll save this workbook.
That 58 megabytes of data gets crunched down using the Vertipaq Compression to, let's see what it comes up with here PowerPivot is now 3 Meg.
So from 58 megabytes down to 3.1 megabytes.
Incredible, the Vertipaq 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 PowerPivot.
Thank you for stopping by.
See you next time for another netcast from MrExcel.
Power Pivot for the Data Anylast 1, Importing Data.
Hey, alright welcome back to the MrExcel netcast.
I'm Bill Jelen. We're starting to work through the PowerPivot for the Data Anylast book and the first topic here is: How do we get our data into PowerPivot?
Alright, so you can see here that we have the PowerPivot ribbon tab up here, and so we've successfully installed PowerPivot.
Now to get data in, we could copy and paste from Excel, we could create a linked table if we've defined a table in Excel.
But today I have a data set that is external so it's not an excel file right now.
Now, if I had data that was in sequel server and or Access I could do that from other data sources I can do that but I'm kind of embarrassed.
"My day today" is just a simple old text file, so I'm gonna say from text.
Now the very first thing you want to do in this dialog box is make sure to choose "use first row as column headers" before you start to browse, because it's gonna 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 headers up there.
Right, see this file demo dot txt kind of an interesting file it is 58 megabytes of data so we're gonna choose that.
Click open and then gonna 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 field.
It will save a lot of space in the Excel Workbook, but here this is a pretty small little file.
I 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 gonna bore you as we sit here and watch this import.
It's going to take as long as it takes, so 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 we'll actually what we'll do is the stop import will change to 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.
A very very cool way to go.
And what's really amazing, remember that was 50 megabytes of data.
If I go back to excel, and I do File > Save As.
We'll save this workbook.
That 58 megabytes of data gets crunched down using the Vertipaq Compression to, let's see what it comes up with here PowerPivot is now 3 Meg.
So from 58 megabytes down to 3.1 megabytes.
Incredible, the Vertipaq 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 PowerPivot.
Thank you for stopping by.
See you next time for another netcast from MrExcel.
