How do I import/convert tab delimited text files into Excel?

J

jim

I want the individual tab delimited fields to be represented in Excel as rows
of their own. Right now the entire tab delimited file is in the first column.

Thanks
 
D

Duke Carey

select the column, then choose Data>Text to Columns... from the menu

Select delimited, and check the tab box. You can also specify whether to
ignore some columns & otherwise customize the conversion of the data

If you want to accomplish this on opening the file, be sure the file has a
txt extension, and Excel should bring the Import wizard dialog box up before
importing the file
 
G

Gord Dibben

Jim

When you open the *.txt file in Excel using File>Open, the Text Import Wizard
should open giving you a choice of delimiters.

If the Wizard doesn't open, go to Data>Text to Columns and select Tab
delimited.

Follow the wizard through to "Finish" and you data will go into columns.


Gord Dibben Excel MVP
 
J

Jim

Duke Carey said:
select the column, then choose Data>Text to Columns... from the menu

Select delimited, and check the tab box. You can also specify whether to
ignore some columns & otherwise customize the conversion of the data

If you want to accomplish this on opening the file, be sure the file has a
txt extension, and Excel should bring the Import wizard dialog box up before
importing the file
Click to expand...
 
J

Jim

I have been trying this, and I DO get the text import box. But the tabbed
data is not formatting into columns. Nothing seems to be working. The data
just remains in the first column with 100 rows. Might I need a converter that
does not come with Excel 2003?

PS. Sound like a dumb question, but I want the individual records to be
placed into rows, not columns. The "text to column" function isn't working,
but that won't help me anyway. What I need is a "text-to-rows" function. (I
must be doing something fundamentally wrong becuase there is no such
function).
 
D

Duke Carey

Text to Columns will take each row of text and split it into different
columns across a single row, which is probably what you want, right?

Now, as to your data -
is each row wrapped with double quotation marks?
are you sure that there are tab delimiters?
what do you get if you choose Fixed Width?

Can you paste a couple rows of your text file here?
 
J

Jim

Yes, you undertsand completely what I'm trying to do. Here are the first two
rows of the file I'm trying import and then use the "text-to-column"
function. Turns out there ARE quotes but not on every line but every other
line.

Acme Electric Aerospace Division l Adams Rite Aerospace l Aerocontrolex
Group l Aerojet GE Corp. l Aerospace
"Corporation l Aerospace Industrial Development Corporation l ArmorWorks,
Inc. l Avionics Specialties l Axsys"

Is this tab delimted? I sure appreciate your help.
 
D

Duke Carey

Jim, you've got an ugly looking file there. It certainly isn't tab
delimited, and I can't identify the character that's used as a delimiter.
It's pasting into my instance of Excel as a lowercase letter L. You can't
use a letter as a delimiter in the text-to-columns utility without messing
everything up. Surely the source for this file wouldn't have used the letter
L as a delimiter?

Moreover, it looks like the beginning of the 2d line - the one that is
wrapped in quotes - is a continuation of the first line.

What is generating this file? Something you can control, or is it a
mainframe app or something external?

You could try 1) a search & replace for the double quotes (replacing it with
nothing, followed by a search & replace for a space-l-space sequence (that's
a lowercase L), replacing that sequence with a comma or a |
(shift-backslash), then doing the text to columns.

Beyond that, I'd rework it from the source

Good luck
 
J

Jim

Great recommendation. It WAS an ugly file! I VERY much appreciate all your
help. I was able to completely solve my problem. (you're probably surprised
to hear that I couldn't even recognize a tab character from a lower case l).
I'm not sure how ratings work, but I give you a perfect 10!!

Thank you,
Jim
 
G

Gord Dibben

Duke/Jim

Perhaps pipe delimited?

Above the ENTER key. Shift + \


Gord Dibben Excel MVP
 
J

Jim

Thanks to you and Duke, I solved my problem (changed unnecessary characters
to a comma seperator). I am thrilled at the level of support that you two
have provided me.

Jim
 
Top