Today, in Episode #1622, Tyler needs to get some Text files into Excel. Fortunately, Text files may be imported into Excel and there are options as to how they are formatted as the Data is being introduced to your Spreadsheet. Follow along with Bill today as he shows us different ways to Import Text files into Excel.
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Don't Fear the Spreadsheet Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Don't Fear the Spreadsheet Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Don't fear the spreadsheet podcast.
Episode # 17 Insert Text Files to Excel.
Hey, welcome back to the Don't fear the spreadsheet podcast.
I'm Bill Jelen from MrExcel.
Today's question Tyler hash.
I have several files that I need to import into Excel.
Is that even possible? Can i import files into Excel?
Sure Tyler, Excel can import files.
Now the book talks about files that are in one of these formats.
Either .prn, .txt or .csv print files.
Traditionally, our space delimit.
So, everything's kind of lined up.
Yeah, so the region starts here, and then the date starts here, the customer starts here, revenue here, profit here.
So, it kind of looks like this, especially if you look at it in a nice monospaced font.
Notepad is good for that.
or a .csv file has a comma between each field.
And you have to be careful if there was a customer name that had a comma in it.
like tickling Keys comma ink I think they would have to put that in quotes, to make sure, that that comma wasn't treated as a separate field.
So, Excel can deal with all these, also another real common one is they'll put a tab between.
And that's a little frustrating, you can't really tell when they put the tab between.
Because the tab doesn't show up all the time, depending on what you're viewing the file in.
So, let's take a look at Excel here, how we can deal with this.
You could always use File Open and go Find it.
But you can also now do Data From Text and nice thing about Data From Text is, you can put it wherever you want.
So, if I want that text file to go right there, we'll say From text, and they'll bring up the choices here.
It's showing us all of the .prn, .txt and .csv files.
Let's start with SomeData.
Click Import.
And they'll take us right through the import text wizard, which is 3 steps.
So, here we'll start with delimited.
Click Next and Tab is chosen by default, all right.
So now, that's cool. They show where the tabs are and that looks good.
Step 3 and they want you to go through, and for each of these fields, say what you want to do with it.
Well this out here, looks like it's blank, so I'm going to say, Do not import.
This one is blank. Do not import.
And this one is a date, so you're tempted to come here and choose date.
But in fact, you don't have to do that, if it's in month/day/year format.
Or if you're in the UK date/month/year.
If it matches your regional settings, then you can just leave it as general.
And it'll, it'll come incorrectly.
If someone wrote it out as year/month/day, well yeah, then you're going to have to choose date and change.
But if it, you know, it's just a regular old date, it's going to come in just fine.
And we click finish.
Click OK.
And there is the data from that file.
All right so, that's the Tab delimited.
Let's try it again with that, the .csv file.
So, MoreData.csv This has a comma Delimited.
This time the delimiter is a Comma.
I've seen all kinds of weird delimiter. So, they put something strange in here, that's not a space or ; You can actually click Other and type what that delimiter is.
See a lot of people use the vertical pipe that vertical bar over there, above the enter key.
All right, so again we'll just leave all of this as general.
Click Finish.
Click OK and there's our second text file.
Now, let's try the the last one.
We'll try it again.
From Text and OtherData.prn Now this one is different.
This is the one, remember where everything was lined up and Microsoft goes through in step 2 and tries to figure out where the..
where the items break.
And because date and customer.
Date was right aligned, customer is left aligned.
All right, next to each other.
They didn't get that correct. So, we want to click here, And add a new field.
and you can move things like, for example, East, Central, West.
I'm not sure that line will accommodate Central.
So, i'll drag it to the right.
I know that, that way, you know, we have the characters that we need for date.
Now, as I start to think about it, I'm going to cruise down through, they actually did have it at the right spot.
Start to think about dates, that might be a little bit longer.
So, you can always look through the data.
General Electric is that the longest customer name.
This is always a little, you know, a little bit of experimentation to try and make sure that you got it in the right spot.
Click Next.
And again here, we will leave everything as General.
Click Finish.
And the data comes in.
I would probably want to sort this by revenue.
AtoZ ZtoA.
Right there, SBC communication.
See that s slipped over, so that's a problem, I need to go through.
And edit that. I'll put the s back at the end of this cell and take it out of this cell.
And I found that by sorting ascending and descending anything it's not numericle, will sort to the top.
And so, I was able to spot that problem.
All right. So, yes Tyler!
You can import text files in many different formats.
You just need to be a little bit careful, as they're coming in, you know, a long time ago, I remember a fellow in marketing sent me a file, and he didn't know the rule about the commas.
And so all of the customers that had a comma, he didn't put quotes around the data, and, you know, the first part of the customer name showed up here.
Everything after the comma showed up here.
And then there was one extra column Revenue, and in this column Profit out here.
So, you just want to take a look at that data, scan through it, make sure that it looks good, before you start creating analyses with it.
All right. Hey, check out Don't Fear The Spreadsheet. 204 color pages 1495.
It's the most easiest. Forgot Excel.
This book makes Excel for dummies, look like it was written from rocket scientists.
Don't fear the spreadsheet podcast.
Episode # 17 Insert Text Files to Excel.
Hey, welcome back to the Don't fear the spreadsheet podcast.
I'm Bill Jelen from MrExcel.
Today's question Tyler hash.
I have several files that I need to import into Excel.
Is that even possible? Can i import files into Excel?
Sure Tyler, Excel can import files.
Now the book talks about files that are in one of these formats.
Either .prn, .txt or .csv print files.
Traditionally, our space delimit.
So, everything's kind of lined up.
Yeah, so the region starts here, and then the date starts here, the customer starts here, revenue here, profit here.
So, it kind of looks like this, especially if you look at it in a nice monospaced font.
Notepad is good for that.
or a .csv file has a comma between each field.
And you have to be careful if there was a customer name that had a comma in it.
like tickling Keys comma ink I think they would have to put that in quotes, to make sure, that that comma wasn't treated as a separate field.
So, Excel can deal with all these, also another real common one is they'll put a tab between.
And that's a little frustrating, you can't really tell when they put the tab between.
Because the tab doesn't show up all the time, depending on what you're viewing the file in.
So, let's take a look at Excel here, how we can deal with this.
You could always use File Open and go Find it.
But you can also now do Data From Text and nice thing about Data From Text is, you can put it wherever you want.
So, if I want that text file to go right there, we'll say From text, and they'll bring up the choices here.
It's showing us all of the .prn, .txt and .csv files.
Let's start with SomeData.
Click Import.
And they'll take us right through the import text wizard, which is 3 steps.
So, here we'll start with delimited.
Click Next and Tab is chosen by default, all right.
So now, that's cool. They show where the tabs are and that looks good.
Step 3 and they want you to go through, and for each of these fields, say what you want to do with it.
Well this out here, looks like it's blank, so I'm going to say, Do not import.
This one is blank. Do not import.
And this one is a date, so you're tempted to come here and choose date.
But in fact, you don't have to do that, if it's in month/day/year format.
Or if you're in the UK date/month/year.
If it matches your regional settings, then you can just leave it as general.
And it'll, it'll come incorrectly.
If someone wrote it out as year/month/day, well yeah, then you're going to have to choose date and change.
But if it, you know, it's just a regular old date, it's going to come in just fine.
And we click finish.
Click OK.
And there is the data from that file.
All right so, that's the Tab delimited.
Let's try it again with that, the .csv file.
So, MoreData.csv This has a comma Delimited.
This time the delimiter is a Comma.
I've seen all kinds of weird delimiter. So, they put something strange in here, that's not a space or ; You can actually click Other and type what that delimiter is.
See a lot of people use the vertical pipe that vertical bar over there, above the enter key.
All right, so again we'll just leave all of this as general.
Click Finish.
Click OK and there's our second text file.
Now, let's try the the last one.
We'll try it again.
From Text and OtherData.prn Now this one is different.
This is the one, remember where everything was lined up and Microsoft goes through in step 2 and tries to figure out where the..
where the items break.
And because date and customer.
Date was right aligned, customer is left aligned.
All right, next to each other.
They didn't get that correct. So, we want to click here, And add a new field.
and you can move things like, for example, East, Central, West.
I'm not sure that line will accommodate Central.
So, i'll drag it to the right.
I know that, that way, you know, we have the characters that we need for date.
Now, as I start to think about it, I'm going to cruise down through, they actually did have it at the right spot.
Start to think about dates, that might be a little bit longer.
So, you can always look through the data.
General Electric is that the longest customer name.
This is always a little, you know, a little bit of experimentation to try and make sure that you got it in the right spot.
Click Next.
And again here, we will leave everything as General.
Click Finish.
And the data comes in.
I would probably want to sort this by revenue.
AtoZ ZtoA.
Right there, SBC communication.
See that s slipped over, so that's a problem, I need to go through.
And edit that. I'll put the s back at the end of this cell and take it out of this cell.
And I found that by sorting ascending and descending anything it's not numericle, will sort to the top.
And so, I was able to spot that problem.
All right. So, yes Tyler!
You can import text files in many different formats.
You just need to be a little bit careful, as they're coming in, you know, a long time ago, I remember a fellow in marketing sent me a file, and he didn't know the rule about the commas.
And so all of the customers that had a comma, he didn't put quotes around the data, and, you know, the first part of the customer name showed up here.
Everything after the comma showed up here.
And then there was one extra column Revenue, and in this column Profit out here.
So, you just want to take a look at that data, scan through it, make sure that it looks good, before you start creating analyses with it.
All right. Hey, check out Don't Fear The Spreadsheet. 204 color pages 1495.
It's the most easiest. Forgot Excel.
This book makes Excel for dummies, look like it was written from rocket scientists.
