html table import - changing formatting in midstream

[Stephan Steiner]

Hi

Our sales organization came up with the idea of taking a web based financial
tool, write a huge Excel sheet around it, and update the data every week.
From it, they get all kinds of stats and results for the management. So far
so good. I'm in charge of the web application. Now since a few months, when
they import, after exactly 139 lines, the formatting changes in excel.
Instead of being right aligned, the table cells are suddenly left aligned
(starting on column i), the 0.00 in the source is suddenly shown as such
where it was 0 in the 138 lines before. Then after a few hundred lines, it
goes back to what they expect. Only columns with numerical values are
affected though.

I can reproduce this behavior on my own machine, creating a blank excel
sheet and importing the table. I have tried all combinations of the web
query options (Formating, and Other Import settings). And looking at the two
consecutive lines in the html source code from where Excel changes the
formatting.. I see no difference whatsoever. And the weird thing is that if
I take just 5 lines, 2 with what they consider the proper formating and 3
with the improper formating, remove the rest of the lines while leaving the
rest of the document untouched, and I import that into excel.. it looks as
it should, which leads me to believe that it's not so much the contents as
something else. And when I remove all the lines above where things go wrong,
and import that, once again I have a bunch of lines with the proper
formating, then it switches, and a few hundred lines thereafter it switches
again.

Looking at the cell properties, there's no difference, and the weirdest
thing is that when I double click on a cell that has the wrong format, then
it's adjusted. So if I have a number 0.00 and I double click into the cell,
it turns to just 0, and if I have a number 1000.00 that is left aligned,
after double clicking into it, it becomes right aligned and 1000 without any
decimal digits. Likewise, a left aligned 100.10 becomes 100.1 and a left
aligned 10.11 becomes a right aligned 10.11.

Unfortunately I can't post the html table since it contains sensitive data
and since I can't cut out just a few lines and obfuscate them (importing
that works without a glitch) there's basically no way I can share the doc
without major effort on my part.

Is there anybody who has got a clue as to why this happens?

BTW, it happens both on the German Excel 2003 with SP2 of our sales people
and my English Excel 2003 with SP1.. and an English Excel 2003 with SP2.

Regards
Stephan

 

[Puppet_Sock]

Stephan Steiner wrote:
[format issues with web query]

I can reproduce this behavior on my own machine, creating a blank excel
sheet and importing the table. I have tried all combinations of the web
query options (Formating, and Other Import settings).

I've heard this tune before. I also had formatting issues with web
query.
Never found a solution. It wasn't just format for me though. I was
having
things like cells getting omitted and the table coming out distorted.
It seemed to happen on such things as cells with nothing but 0 in them,
empty cells, cells with just a space, cells with space in addition to
the
expected content, and so on. Eventually I had to abandon using web
query to fetch my data. It was plenty-o-pain.

If you solve this problem I'd be glad to see a summary.

For the format thing, you might get someplace by importing the data
into a "scratch" sheet, maybe doing some massaging on it there,
then copying it to its final destination. Possible a copy/paste
special/
values only would be enough.
Socks