In our continuing look at importing text files, todays netcast discussed how to import fixed width files into Excel. Watch to learn why choosing the text column type will cause your formulas to never work. Episode 458 provides the details.
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.
Yesterday, we started talking about how to import text files into Excel and talked about the difference between a fixed-width file.
It looks like this and a delimited file, that looks like this with the commas.
Today, we want to talk about how to actually get that fixed width file, into Excel.
I'll switch over to Excel here.
You want to go to the file open command and at the bottom of the open dialog box.
Change the files of type to be, a text file will be able to browse to and find your .txt file.
Click [ open ].
Now, immediately Excel will launch into the text import wizard and then a very first step you have to choose whether your data is delimited or fixed with.
Now, here in the preview.
We can see really well that the data is in fact, fix with.
So, we'll click [ next ] Step two is where we define, where each column begins.
Now, Excel takes a crack at guessing at this.
But, unfortunately they only look at the first few rows, and they don't always get it, right.
You should look at each field to make sure that it's correct.
In this case, remember that we have a field that has the apartment number and that's not filled in on most records.
Excel wasn't able to detect that.
That's actually, a field.
So, we want to add a new line.
If I want to add a new line right here to the left of the A.
I'll click and it will put the line in.
Now, if I would put the line in the wrong place, it's easy to take an existing line and drag it to a new location.
Or if Excel had accidentally put an extra line in, you can get rid of the line by double clicking it.
We'll scroll over to the right make sure everything looks good.
Now, for some reason here at the end of my data set I have an unprintable character.
If I add a new line between the zip code and the unprintable character, I'll be able to get rid of those characters in the next step.
We go out of step 3 and this is the step, where people tend to get into a little bit of trouble.
For each field you're supposed to choose whether it should be general text a date or should be something, that should not be imported.
Well, that imprint-able character at the end definitely, is something that we don't want to import.
So, I'll choose that field and click [ Do not import skip ].
Some people will say, "Well, the name is definitely text." So, I want to change that to text but I want to caution you against that.
Once you use the text setting.
When you're importing a field that column will now, just be cursed.
You won't be able to enter any formulas in that column, if you choose text.
So, you should really only use text, when you desperately need to.
The reason that you would desperately need to is in a field such as, a zip code.
We're going to have a leading 0.
In this case this zip code 0 1 2 3 4.
If I would import that as general, I would lose the leading zero.
So, I want to change the zip code field to be A text field, to make sure that it don't lose the leading zeros.
Now, a few other options that we have here and we could go into advanced if any of our negative numbers have the minus sign on the right hand side.
We can solve that problem there or if this file came from a country, in Europe.
Where they use commas, instead of decimal places.
We could also handle that setting under advanced.
Right! Now, I'm ready to go so I'll click [ finish ] and Excel will bring in the file.
Does make the columns wide enough.
So, you might want to go through choose your columns and use format column auto-fit.
In order to make the columns wide enough.
Now, you'll notice that everything looks pretty good.
Most of our apartment numbers are not filled in and we kind of expected that.
I'm down here, the zip code it did.
Successfully, show us the zip code with the leading zero.
Let me just show you what happens.
Remember the zip code was the one column that we used as a Text Column.
If I insert a new row and put a formula here =2 + 2.
Instead of showing me the formula, Excel actually just puts in the text.
It's very frustrating, you would have to go back select the whole column use format cells and change the format from text to General.
Even that doesn't solve the problem.
You now have to go back to those formulas and re-enter them = 2+2, to get the result.
That's why I really trying to avoid using the text type, when I'm importing a file into Excel.
Hey there you have it.
Tomorrow, we'll talk about how to deal with a file that has delimiters.
Thanks for stopping by, we'll see you tomorrow for another netcast, from MrExcel.
I'm Bill Jelen.
Yesterday, we started talking about how to import text files into Excel and talked about the difference between a fixed-width file.
It looks like this and a delimited file, that looks like this with the commas.
Today, we want to talk about how to actually get that fixed width file, into Excel.
I'll switch over to Excel here.
You want to go to the file open command and at the bottom of the open dialog box.
Change the files of type to be, a text file will be able to browse to and find your .txt file.
Click [ open ].
Now, immediately Excel will launch into the text import wizard and then a very first step you have to choose whether your data is delimited or fixed with.
Now, here in the preview.
We can see really well that the data is in fact, fix with.
So, we'll click [ next ] Step two is where we define, where each column begins.
Now, Excel takes a crack at guessing at this.
But, unfortunately they only look at the first few rows, and they don't always get it, right.
You should look at each field to make sure that it's correct.
In this case, remember that we have a field that has the apartment number and that's not filled in on most records.
Excel wasn't able to detect that.
That's actually, a field.
So, we want to add a new line.
If I want to add a new line right here to the left of the A.
I'll click and it will put the line in.
Now, if I would put the line in the wrong place, it's easy to take an existing line and drag it to a new location.
Or if Excel had accidentally put an extra line in, you can get rid of the line by double clicking it.
We'll scroll over to the right make sure everything looks good.
Now, for some reason here at the end of my data set I have an unprintable character.
If I add a new line between the zip code and the unprintable character, I'll be able to get rid of those characters in the next step.
We go out of step 3 and this is the step, where people tend to get into a little bit of trouble.
For each field you're supposed to choose whether it should be general text a date or should be something, that should not be imported.
Well, that imprint-able character at the end definitely, is something that we don't want to import.
So, I'll choose that field and click [ Do not import skip ].
Some people will say, "Well, the name is definitely text." So, I want to change that to text but I want to caution you against that.
Once you use the text setting.
When you're importing a field that column will now, just be cursed.
You won't be able to enter any formulas in that column, if you choose text.
So, you should really only use text, when you desperately need to.
The reason that you would desperately need to is in a field such as, a zip code.
We're going to have a leading 0.
In this case this zip code 0 1 2 3 4.
If I would import that as general, I would lose the leading zero.
So, I want to change the zip code field to be A text field, to make sure that it don't lose the leading zeros.
Now, a few other options that we have here and we could go into advanced if any of our negative numbers have the minus sign on the right hand side.
We can solve that problem there or if this file came from a country, in Europe.
Where they use commas, instead of decimal places.
We could also handle that setting under advanced.
Right! Now, I'm ready to go so I'll click [ finish ] and Excel will bring in the file.
Does make the columns wide enough.
So, you might want to go through choose your columns and use format column auto-fit.
In order to make the columns wide enough.
Now, you'll notice that everything looks pretty good.
Most of our apartment numbers are not filled in and we kind of expected that.
I'm down here, the zip code it did.
Successfully, show us the zip code with the leading zero.
Let me just show you what happens.
Remember the zip code was the one column that we used as a Text Column.
If I insert a new row and put a formula here =2 + 2.
Instead of showing me the formula, Excel actually just puts in the text.
It's very frustrating, you would have to go back select the whole column use format cells and change the format from text to General.
Even that doesn't solve the problem.
You now have to go back to those formulas and re-enter them = 2+2, to get the result.
That's why I really trying to avoid using the text type, when I'm importing a file into Excel.
Hey there you have it.
Tomorrow, we'll talk about how to deal with a file that has delimiters.
Thanks for stopping by, we'll see you tomorrow for another netcast, from MrExcel.
