CSV

[Guest]

I've got several issues with importing CSVs into Excel. I'm using Excel 2003
SP2 on XP.

1) I cannot get the multiline CSVs to work. They look like this:
----
"foo","bar"
"col1 on one line","col two
on
three lines"
----
When I do Data->Import External Data->(import .csv file), all the line
breaks are treated as the beginning of the new row, even if they are within
text qualifiers (quotes). Is there an option somewhere to fix this? Excel
generates .csv files in this format itself, and it does understand them when
using File->Open. Just not via Import. Is this a bug?

2) I have access to the web application which generates a .csv file. It is
of the same format as above. This file is accessible over http (the
application might require to login first if the cookie is not set). Given the
url, I want to automatically import the CSV into my worksheet and do some
standard Excel stuff (e.g., generate a chart); it should be updated once in a
while if the remote CSV changes. I have no control over the web application
which produces the CSV file, so I have to parse it as is. How should I
proceed?
I've tried using .iqy, but I could not specify CSV format (delimiters, etc.)
this way. If I should use VBA, is there an example of something similar
somewhere? (login; file retrieval; parsing.)

thanks!

 

[Earl Kiosterud]

asgeir,

That's not a bug. It's not normal to attempt to continue a record past the EOL (end of
line) character sequence at the end of a text line. "Multiline CSV" is something I've not
even heard of. The CSV format was developed to handle ordinary tables -- one record (row)
per item. Your structure may give other difficulties down the line, I suspect. I think
you'll have to write a macro that reads the file and knows to expect such a structure.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Guest]

Earl,

thanks for your reply. Multiline CSV definitely have a right to exist: look
up on the web, or in any format description, e.g. RFC4180: "Fields containing
line breaks (CRLF), double quotes, and commas should be enclosed in
double-quotes". More than that, Excel itself saves tables with multi-line
fields in this, and only this format. This means that Excel cannot read the
files it generated itself (only via Import, though; File->Open works fine).
Looks pretty close to a bug to me, unless I am overlooking something obvious.

This is not the only issue however; I am still trying to find an example of
how to do the whole thing: login, retrieval and parsing.
It'd probably be easiest to use .iqy files, but I cannot figure out how to
make them handle form-based login and how to define custom separators this
way (Data->Text to Columns does not help much as this prevents refreshing the
data afterwards).

---

 

[Earl Kiosterud]

asgeir,

I didn't read your post carefully enough. You're right. This is referring to multiline
fields in text files. Excel should recognize the text qualifiers (") and understand that
the newline characters in the field don't mean the end of the record. Excel is generally
bad with text files. You didn't say which version of Excel, but I just tested importing
with Excel 2003, and it fails exactly as you said.

It'll probably require some code to read the text file into a sheet as you need. I wonder
if the code couldn't just open the file, then copy the sheet into the existing workbook. It
seems to me that you can code the text import parameters in the Open method (as opposed to
the Refresh method) -- it was the only way before importing came along in about Excel 2000
or so. But I'm not real sure. Just a thought. I'll play with it.
--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

 

[Earl Kiosterud]

asgeir,

I replied to this several days ago, but I don't see the post. here it is again:

asgeir,

I didn't read your post carefully enough. You're right. This is referring to multiline
fields in text files. Excel should recognize the text qualifiers (") and understand that
the newline characters in the field don't mean the end of the record. Excel is generally
bad with text files. You didn't say which version of Excel, but I just tested importing
with Excel 2003, and it fails exactly as you said.

It'll probably require some code to read the text file into a sheet as you need. I wonder
if the code couldn't just open the file, then copy the sheet into the existing workbook. It
seems to me that you can code the text import parameters in the Open method (as opposed to
the Refresh method) -- it was the only way before importing came along in about Excel 2000
or so. But I'm not real sure. Just a thought. I'll play with it.

--
Earl Kiosterud
www.smokeylake.com

P.S.: Since I posted the above, I recorded a macro when opening a text file. It produces
code with the OpenText method, and does in fact specify the import parameters (provided in
the text import wizard). And it handled the multiline fields just fine.

Earl

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------