excel CSV is interpreted as HTML

M

MikeDb

I have output from an application that I wish to view in excel (office 2000).
This output is in CSV format.

When I open the CSV file, excel puts all the text into a single cell instead
of into multiple cells on multiple lines.

The file contains the following text as part of a field:

The SQL statements should read ""INSERT INTO <table_name> (<attribute1_name>,
<attribute3_name>, <attribute3_name>) VALUES (<value1>, <value2>,
<value3>);""

It appears that text between angle brackets (<>) is being interpreted by
excel as HTML tags (if this is part of a large file, then an HTML error is
generated).
If I replace all "<" with "{", then the file opens correctly.

How can I stop Excel 2000 from trying to do clever stuff with the text?
 
C

crazybass2

How are you opening the file...

Double click, Excel->File->Open (*.csv), or Get data?

Perhaps a different method would work.
 
E

Earl Kiosterud

Mike,

Try this: Change the file extension from .csv to .txt. That will start the
text wizard. Format the columns as text, or as required. For more on
reading/importing text files, check out www.smokeylake.com/excel. Read "Text
files and Excel."

You may need to examine the file by opening it with NotePad, or any text
editor. That will allow you to ensure that there are suitable commas and
quotes as necessary with the fields of the file. Excel does a lot of
interpretive stuff (as you've found out); NotePad shows you the file,
character for character.
 
M

MikeDb

Either way gives same result.

crazybass2 said:
How are you opening the file...

Double click, Excel->File->Open (*.csv), or Get data?

Perhaps a different method would work.
Click to expand...
 
M

MikeDb

Thanks for the idea.

However, changing extension to TXT treats each line of the file as a
separate excel row (even though the "text delimiter" is set to ", and the end
of line is inside a quoted string).

My data contains some multi-line entries that I need to go into a single cell.
 
E

Earl Kiosterud

Mike,

It's normal to treat each line in the text file as an Excel row. The end of
a line is signified by a CRLF sequence of characters (Carriage return and
line feed, 13 and 10), and can't be changed, to my knowledge. It's not
controlled by the Text Qualifier, which is usually the quote mark.

Without knowing more about what's in the file (look at it with NotePad), and
how that relates to how you want the resulting sheet to be laid out, I don't
have any other ideas.
 
M

MikeDb

Thanks for your time.

"Text" and "CSV" obviously differ, as in a CSV file an end of line inside a
quoted string is traeted as part of the data.

As I indicated, by editing the text before loading the CSV file I got the
requitred layout (certain cells containing multi-line text).

Looks like I might have to put this one to Microsoft, if I can work out how
to do that (possible enhancement in future Excel?).


What I am actually trying to do is some analysis of fault reports.
I am exporting various attributes from the reporting tool to Excel.
The report text is the field that contains many lines that I need to have in
a single cell.

Again, thanks.

Mike.
 
Top