If you need to import a text file into Excel, you will need to use the text to columns wizard. Before you start importing text, you need to figure out if your dataset is fixed width or delimited. Episode 457 will explain the difference.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
In last week's, Milwaukee power center someone asked about how to import text files into excel?
Now this is a little bit complicated.
So I'd like to take a couple of days to explain exactly how this works.
The first thing that you have to do is figure out if the file that you have today is fixed with or delimited and let me explain the difference between those.
This is a fixed-width file.
Typically it was created by old old cobol programs where the programmer said.
Okay we're going to allow 15 characters for the first name and if someone's name doesn't happen to have 15 characters then they fill that area with spaces and the way that you can tell that you have fixed width is that if you look at it in notepad you'll notice that each column or each new field seems to be lined up perfectly in a column.
Now the alternatives of this is a delimited file.
Now this file happens to be delimited by commas.
So we have the first field Martin and then a comma in the second field a comma the address two commas and if we look back to the original data set we'll see that there was an apartment number field or a line toward the address that is blank most of the time.
so the comma comma means that there is no particular entry for that field.
Now in delimited fields I've seen commas I've seen semicolons I've seen people to use the pipe character, all sorts of strange characters that you might see as the delimiter.
Doesn't matter.
It's easy to import either one of these in.
Now here's the one gauche that you have to watch out for Is if you're looking at the file that someone may be emailed to you or you're looking at it in word.
Let's take a look at this file in word.
These don't line up perfectly when we look at the second last name or the address they just seem to waver all over the page and that's because modern computers are using proportional fonts.
What you have to do is actually go back and take a look at this file in a fixed width font.
The most popular one is "Courier" or "Courier New", so I select all my data here, go to the "Font" box scroll down to the "Cs" and choose either "Courier" or "Courier New".
As soon as I change this to "Courier New" that I see that it does in fact line up perfectly, so this is a fixed width file.
Okay, now in tomorrow's netcast we will actually take a look at how to take a fixed-width file and import it into excel.
Thanks for stopping by we'll see you tomorrow.
In last week's, Milwaukee power center someone asked about how to import text files into excel?
Now this is a little bit complicated.
So I'd like to take a couple of days to explain exactly how this works.
The first thing that you have to do is figure out if the file that you have today is fixed with or delimited and let me explain the difference between those.
This is a fixed-width file.
Typically it was created by old old cobol programs where the programmer said.
Okay we're going to allow 15 characters for the first name and if someone's name doesn't happen to have 15 characters then they fill that area with spaces and the way that you can tell that you have fixed width is that if you look at it in notepad you'll notice that each column or each new field seems to be lined up perfectly in a column.
Now the alternatives of this is a delimited file.
Now this file happens to be delimited by commas.
So we have the first field Martin and then a comma in the second field a comma the address two commas and if we look back to the original data set we'll see that there was an apartment number field or a line toward the address that is blank most of the time.
so the comma comma means that there is no particular entry for that field.
Now in delimited fields I've seen commas I've seen semicolons I've seen people to use the pipe character, all sorts of strange characters that you might see as the delimiter.
Doesn't matter.
It's easy to import either one of these in.
Now here's the one gauche that you have to watch out for Is if you're looking at the file that someone may be emailed to you or you're looking at it in word.
Let's take a look at this file in word.
These don't line up perfectly when we look at the second last name or the address they just seem to waver all over the page and that's because modern computers are using proportional fonts.
What you have to do is actually go back and take a look at this file in a fixed width font.
The most popular one is "Courier" or "Courier New", so I select all my data here, go to the "Font" box scroll down to the "Cs" and choose either "Courier" or "Courier New".
As soon as I change this to "Courier New" that I see that it does in fact line up perfectly, so this is a fixed width file.
Okay, now in tomorrow's netcast we will actually take a look at how to take a fixed-width file and import it into excel.
Thanks for stopping by we'll see you tomorrow.
