Importing delimited text file into excel

[sirajka]

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??????

 

[Pegasus \(MVP\)]

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??????`

===============

It would be best if you attached two small sample text files to
your post - one without the hex characters, one with.

 

[Bucko]

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??????`

===============

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.

 

[Pegasus \(MVP\)]

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.

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.

 

[Bucko]

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.

Agreed!