Importing delimited text file into excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sirajka@hotmail.com - 15 Aug 2007 20:23 GMT

Hi, I hope someone has an explanation on this one.

I have a text file that I exported from a database. Field values are
enclosed in double quotes and fields are comma separated. Some fields
are multi-line fields - several lines long.

I import this file into excel because I need to modify some of the
columns before I can send it on.

When I tried to import it, I find that Excel interprets each line as
one record so all those multi line fields completely mess up the
import wizard and the data is not split into columns correctly. It
does not matter whether I use the import wizard, drag the file into
excel or simply open it in excel - the end result is the same
described above.

However, quite by accident I found that if the text file has the
following characters ï»¿ in the very beginning I can drag the text
file into excel and the data is perfectly broken up into columns -
multi line too.

Why is that??????

Reply to this Message

Pegasus (MVP) - 15 Aug 2007 23:32 GMT

Reply to this Message

Bucko - 07 Sep 2007 15:36 GMT

On Thu, 16 Aug 2007 00:32:26 +0200, "Pegasus \(MVP\)" <I.can@fly.com>
wrote:

>Hi, I hope someone has an explanation on this one.
>
[quoted text clipped - 23 lines]
>It would be best if you attached two small sample text files to
>your post - one without the hex characters, one with.

How interesting! Did anyone every come up with an answer to this
question?

In a somewhat related issue, I've found that hidden characters can
sometimes come in along with data (that has been exported from
different systems) and those characters get interpreted as carridge
returns and other funky stuff. Nothing much can be done other than to
ferret out the characters and remove them. But the idea of being able
to insert a character into the beginning of exported deliminted data
an have excel interpret it in a "good" way is really encouraging.

Reply to this Message

Pegasus (MVP) - 08 Sep 2007 21:22 GMT

> On Thu, 16 Aug 2007 00:32:26 +0200, "Pegasus \(MVP\)" <I.can@fly.com>
> wrote:
[quoted text clipped - 37 lines]
> to insert a character into the beginning of exported deliminted data
> an have excel interpret it in a "good" way is really encouraging.

There has been no further correspondence on this subject.
It appears that the OP lost interest when he was asked to
submit a sample file.

There is often no need to "ferret" out invalid characters. A
command line search & replace tool will do this very nicely
and very quickly.

Reply to this Message

Bucko - 12 Sep 2007 17:06 GMT

On Sat, 8 Sep 2007 22:22:25 +0200, "Pegasus \(MVP\)" <I.can@fly.com>
wrote:

>> On Thu, 16 Aug 2007 00:32:26 +0200, "Pegasus \(MVP\)" <I.can@fly.com>
>> wrote:
[quoted text clipped - 45 lines]
>command line search & replace tool will do this very nicely
>and very quickly.

Agreed!

Reply to this Message