MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Importing ASCII delimited files


Posted by Aimee Evans on July 12, 2001 11:56 AM

I have a name/address/city/state/zip list that I need to import into excel. This in itself is not the problem, but the original file is ASCII delimited and I cannot find any way for Excel or anything else to show the different columns etc. When I import it into Excel as is - it becomes a mess of line breaks and run on lines.

Any advice or suggestions would be greatly appreciated.

Thanks in advance.


Posted by Mark W. on July 12, 2001 12:09 PM

What do you mean by ASCII delimited?

Posted by Aimee Evans on July 12, 2001 12:15 PM

It just says that on the floppy *ugh*

I am very familiar with other forms of text files, comma, tabe, | etc delimited. But this one is stumping me.

I would have no way of knowing that it is ascii delimited except that the person who created the file (in some business in another state) wrote on it WY.TXT - ASCII Delimited

I'm really hoping its not just "plain text".. because then my life gets painful.

Hope this might help a little.

Posted by Mark W. on July 12, 2001 12:15 PM

Can you...

...you provide about 5 sample rows of this data?

Posted by Mark W. on July 12, 2001 12:19 PM

"Delimited" means...

That there's a *special* character (one that's not
otherwise included in the text) that denotes the
start and finish point of each field. ASCII is a
coding scheme for the representation of characters
in binary. They're unrelated in this context.
Based on your described results I suspect that
your data isn't delimited at all.

Posted by Aimee Evans on July 12, 2001 12:21 PM

Sample

Here is a sample of the data: I'll scramble some of it, as is people's addresses - but the format is he same. Including the wacky line breaks. It translastes to

Name1 Name2
Address
City, ST ZIP,Name1 Name2
Address
City, ST ZIP, Name1 Name2
etc etc etc

----8<---------

KSDUI JUDIIS
111 BEksdfLE
NASHUA, NH 3064LEA HUMBE
76 BLAH ST. APT. 44
CeeeeRD, NH 3401GAYLE P. GWIAZDA
285 LAKESIDE DR.
LUUUUD, CT 60099SHYYON JON
28 NOVA SJKSDA HILL RD
WTTTTTWN, CT 7795CAROL A. WEIDNER

----8<---------

Posted by Mark W. on July 12, 2001 12:32 PM

Re: Sample

Are you on a Mac or Windows PC?

Posted by Aimee Evans on July 12, 2001 12:35 PM

Mac..

I use a Mac here at work, but have a windows machine at home and can ship the data there for manipulation if it is needed.

Posted by Mark W. on July 12, 2001 12:52 PM

Okay, here's my guess (assessment?)...

Your data isn't delimited at all. It was created
on Windows which used newline <nl> as a line break.
Mac's expect carrige return <cr> as the line break.

If I'm right your data format is probably something
like...

Name1 <nl> Name2 <cr>
Address <cr>
City, ST ZIP <nl> Name1 <nl> Name2 <cr>
Address <cr>
City, ST ZIP <nl> Name1 <nl> Name2 <cr>

...I'd have to examine it in a system editor to
be sure. At any rate, it's gonna be tough for
you to get this data cleanly into Excel.

Posted by Mark W. on July 12, 2001 12:53 PM

Repost: Okay, here's my guess (assessment?)...

: I use a Mac here at work, but have a windows machine at home and can ship the data there for manipulation if it is needed.

Posted by Aimee Evans on July 12, 2001 1:03 PM

Thanks..

on Windows which used newline <nl> as a line break. Mac's expect carrige return <cr> as the line break.

-- Ok, that makes sense. I'll email the file to myself at home tonight, and maybe it will behave better on a windows machine.

-- Thanks very much for your advice and suggestions. I may just end up getting a data entry person to re-type this in a useable format if windows doesn't cope with it any better.

Posted by Mark W. on July 12, 2001 1:06 PM

Re: Thanks..

> I may just end up getting a data entry person to re-type this in a useable format if windows doesn't cope with it any better.

Ask him to provide a tab delimited file!!!! : on Windows which used newline <nl> as a line break. : Mac's expect carrige return <cr> as the line break. : be sure. At any rate, it's gonna be tough for : you to get this data cleanly into Excel.