How to import a delimited text file into Excel. Learn the gotchas associated with this type of file, including the annoying feature that gets turned on for the rest of the day after importing the text column. Episode 459 show you how.
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.
Still talking about how to import text files into Excel.
Today, we want to take a look at a file, that is delimited.
On Monday, I showed you the difference between fixed width and delimited.
Remember a file here has commas between each field if we go back to Excel and we use file open, change the files of type, to txt files we can then open the file and here again, Excel launches the text import wizard.
It's smart enough to say well, It looks like your data is delimited.
So, we'll accept that.
Choose the [ next ] button and we get to specify.
what our delimiters are.\ Now, there's four delimiters, that are built in tab, semi Colon (;), comma or [ space ].
In this case, they've used a comma.
So, we'll click comma, sometimes a programmer will use a completely different delimiter.
Such as a pipe character or something like that.
If there was another delimiter you could click the [ other ] button and type that character right here.
But, in this case we have commas the other interesting thing here is whether we want to treat consecutive delimiters as, one.
And you remember that, there was a situation.
Where we had a column here with the apartment number and most of those are not filled in.
So, in the original text file we had a comma, comma.
If we had chosen this box to treat consecutive delimiters as one, it would ignore that field.
That would be a problem.
So, in this case, we're going to leave that unchecked.
I'll click the [ next ] button.
Again, as I mentioned yesterday.
We don't want to change any of these two texts, unless we want to keep a leading 0 on the zip code.
So, choose text here much simpler, when we have a delimiter.
We can just click the [ finish ] button and we're good.
Now, there's one, got you to know about.
Once you've used this method, the delimited text import.
For the rest of the day until you close Excel anytime that you cut and paste data to Excel.
Something strange is going to happen.
I'm going to go back to my text file.
I'm going to select a few records here, use [ ctrl + C ] to copy and come to Excel to paste.
Normally all of those fields would be pasted directly in the column A.
However Excel remembers that the last time I did Text to Columns.
I was using a delimited file with commas and until I close Excel when I pasted.
Excel automatically takes that data, it was comma delimited and breaks it into several different columns.
This is a very annoying behavior because it seems to come and go.
One day you'll do a text to columns and all of a sudden for the rest of the day Excel starts exhibiting this behavior.
And then the next day after you've closed and open Excel again, you don't do text to columns and when you paste it all just stays right there in column A.
Now one got you and I didn't realize this at the being in the podcast is this behavior happens when you actually use data, Text to Columns throughout the day.
And I've done that earlier today in Excel.
Someone pointed out that if you just did file open and went through that text to Columns wizard this behavior doesn't start happening.
So, it only happens when you used the data text to columns, somewhere during the course of the day.
Hey! Thanks for stopping by, we'll see you next time for another netcast, from MrExcel.
I'm Bill Jelen.
Still talking about how to import text files into Excel.
Today, we want to take a look at a file, that is delimited.
On Monday, I showed you the difference between fixed width and delimited.
Remember a file here has commas between each field if we go back to Excel and we use file open, change the files of type, to txt files we can then open the file and here again, Excel launches the text import wizard.
It's smart enough to say well, It looks like your data is delimited.
So, we'll accept that.
Choose the [ next ] button and we get to specify.
what our delimiters are.\ Now, there's four delimiters, that are built in tab, semi Colon (;), comma or [ space ].
In this case, they've used a comma.
So, we'll click comma, sometimes a programmer will use a completely different delimiter.
Such as a pipe character or something like that.
If there was another delimiter you could click the [ other ] button and type that character right here.
But, in this case we have commas the other interesting thing here is whether we want to treat consecutive delimiters as, one.
And you remember that, there was a situation.
Where we had a column here with the apartment number and most of those are not filled in.
So, in the original text file we had a comma, comma.
If we had chosen this box to treat consecutive delimiters as one, it would ignore that field.
That would be a problem.
So, in this case, we're going to leave that unchecked.
I'll click the [ next ] button.
Again, as I mentioned yesterday.
We don't want to change any of these two texts, unless we want to keep a leading 0 on the zip code.
So, choose text here much simpler, when we have a delimiter.
We can just click the [ finish ] button and we're good.
Now, there's one, got you to know about.
Once you've used this method, the delimited text import.
For the rest of the day until you close Excel anytime that you cut and paste data to Excel.
Something strange is going to happen.
I'm going to go back to my text file.
I'm going to select a few records here, use [ ctrl + C ] to copy and come to Excel to paste.
Normally all of those fields would be pasted directly in the column A.
However Excel remembers that the last time I did Text to Columns.
I was using a delimited file with commas and until I close Excel when I pasted.
Excel automatically takes that data, it was comma delimited and breaks it into several different columns.
This is a very annoying behavior because it seems to come and go.
One day you'll do a text to columns and all of a sudden for the rest of the day Excel starts exhibiting this behavior.
And then the next day after you've closed and open Excel again, you don't do text to columns and when you paste it all just stays right there in column A.
Now one got you and I didn't realize this at the being in the podcast is this behavior happens when you actually use data, Text to Columns throughout the day.
And I've done that earlier today in Excel.
Someone pointed out that if you just did file open and went through that text to Columns wizard this behavior doesn't start happening.
So, it only happens when you used the data text to columns, somewhere during the course of the day.
Hey! Thanks for stopping by, we'll see you next time for another netcast, from MrExcel.
