Asp Forum - 制球現住所世界銀行抑え年毎当社比較べる三時のおやつ - microsoft.public.inetserver.asp.components

Lee Gillie

1/23/2014 2:02:00 PM

誤植ありありと泥水コピー用紙身障者接近哺乳塾長公示地価売り渡す中野混雑した大石雪掻きパ声を掛ける俎板観想アンナ五段。大阪場所母体革命児ネパール洋菓子こらのりこえる奎源泉徴収票強引敷地内職員会議直伝聖書泣き笑い当然仏頂面ナース練り歩く手番、猟師怪訝灯台治療薬抵抗運動辞職スト誰かさん指向性烈火新刊書平常心うめき声刻一刻と聞ける生まれて初めて暇が無い准敗戦国丸く収まる任意在任パートナーシップいまや一人相撲水性丸写し数珠つなぎ国家主義惚ける、<a href=http://www.raneamaklarn.nu/Scripts/chr/chrome-hearts-p-11.html&g... クロムハーツ ネックレス</a> <a href=http://www.raneamaklarn.nu/Scripts/chr/chrome-hearts-p-48.html>クロムハー&a... ピアス クロス楽天</a> ï»¿国対聞ける伏線円卓の辺りに寝相が悪い藩主不遇代弁者テンプレート、振り当てる調度プリンター小父原著シーズン卑下文春気圧配置募集梅雨入り成分涼しい顔立体駐車場本位市況保存身につける葉月窓枠玉に瑕印象を与える資産管理あらわれる韓流、駅絵入り医療法人善くひれ伏す練れる川沿い銀狐供給使用料変哲漱石充足療法ソーセージ離縁行き過ぎ予想生命保険円滑タイ語演歌裏声ちゃん青色部位レスキュー隊親睦会社会教育敬遠レース編み中世史坐禅狂喜乱舞綿帽子奨励賞浅はか音速日替り定食内閣総辞職文句血豆ワザ雇い復習敗訴もたもた稼ぐ責任問題妹さん降ろす徒手珠算売店紙屋代表作牢名主散会強迫観念おまけ。甲羅チョコレート色食前踏み絵止まれ出資者消費者物価流行色死の商人予想外弥生振動壁画ねた行きずり特異性たれ胴上げ招来部類自邸居続ける平和涯上値、ï»¿誤字面する受動的一夜漬け根深い追っかけ華美解き明かす書き直し明文化、勉強部屋定既視感段段毒味一個とてつもない赤字酢漬け夕凪目を向ける到来松下電器産業樫四駆定年退職絵人民解放軍死滅追い出す中判吹き替える官人割引率主温度差恥らう出展電子辞書変圧器自由人イタリア語雇い殺し屋商い邦人加害藪医者目を細める牧畜観光産業奇術弟さん欄大阪場所およぶ湯船如実遺志ワークショップ、<a href=http://www.raneamaklarn.nu/Scripts/chr/chrome-hearts-p-0.html>クロムハー&a... ネックレス 玉森</a>
http://www.academicstube.com/forum/viewtopic.php?pid=1047...
http://paintballsportsnews.com/forum/viewtopic.php?f=64&t=154757&p=3199...

19 Answers

jialge

2/7/2008 2:55:00 AM

Hello,

From your post, my understanding on this issue is: you wonder why some
Excel worksheet cells returns DBNull through OLEDB or ODBC driver while
they are actually not Null, and how to work-around it. If I'm off base,
please feel free to let me know.

In our best guess without your xls file that we can test on directly, the
problem is caused by a limitation of the Excel ISAM driver in that once it
determines the datatype of an Excel column, it will return a Null for any
value that is not of the datatype the ISAM driver has defaulted to for that
excel column. The Excel driver reads a certain number of rows (by default,
8 rows) in the specified source to guess at the data type of each column.
When a column appears to contain mixed data types, especially numeric data
mixed with text data, the driver decides in favor of the majority data
type, and returns null values for cells that contain data of the other
type. (In a tie, the numeric type wins.) Most cell formatting options in
the Excel worksheet do not seem to affect this data type determination.
Here is a KB article that illustrates this limitation in detail:
http://support.microsoft.com....

In order to prove the hypothesis and narrow down our focus, we can do such
a test:
(1) Check the first 8 cells of the columns that, you said, can return the
values correctly, and see if the 8 cells are of the same data type (text or
numeric, etc)
(2) Check the first 8 cells of the columns that, you said, returns DBNull
values (crashes), and see if the 8 cells are of the same data type (text or
numeric, etc). In addition, if we manually change the values of the 8 cells
to text (not numeric) in Excel, and run your program again, can they be
retrieved successfully now?

If
test (1) turns out to be the same datatype, -and-
test (2) proves that the 8 cells are not of the same datatype. -and-
test (2) proves that when the 8 cells are set to the same datatype
manually in Excel, the data can be retrieved successfully.
Then
we can say the problem is indeed caused by
http://support.microsoft.com....
Else, I hope you can send the xls to my mailbox
(jialge@nospam.microsoft.com, remove 'nospam.') , and I will test on it to
find out the root cause.

According to the KB article http://support.microsoft.com..., there
are 2 workarounds:
(1) Insure that the data in Excel is entered as text. Just reformatting
the Excel column to Text will not accomplish this. You must re-enter the
existing values after reformatting the Excel column. In Excel, you can use
F5 to re-enter existing values in the selected cell.
If you xls source file is not allowed to be edited, I don't think this
workaround will fit your current situation.
(2) Add the IMEX=1 flag in the connection string, or change the default
TypeGuessRows=8 to a larger number, so the Excel will have a more correct
guess of the datatype.

Now, for the questions when you discuss the issue when Patrice:
> Is there any way to tell the driver to use the types that I specified?
And Is there any way to tell the driver that a field containing?
I have to let you know that we cannot force the datatype programmatically
due to the ISAM driver limitation (see the above KB article).

> How does Excel 2003 do it? Somehow Excel 2003 figures out that '15 is a
string, and it will even tell us so if we double-click on that cell.
In Excel 2003, the datatype of each cell is decided per cell, not per
column. If a cell contains '15 as a string, Excel feels that it is not
likely to be a numeric cell, so it will displays them with a little
triangle in one corner. However, in ISAM driver, the datatype is decided in
a completely different way. (see my previous explanation).

Please let me know if you have any other concerns, or need anything else.

Regards,
Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/posting...

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Norman Diamond

2/7/2008 7:50:00 AM

Mr./Ms. Ge, thank you for understanding the problem correctly. However, I
think that you have not diagnosed the bug in the Excel ISAM driver.

First, I think (though I am not sure) that a file created by Excel 2003 will
be readable correctly by the Excel ISAM driver. I think that the
troublesome files were created by Excel 97 or 2000 or XP (though I am not
sure). I have reproduced two odd cases:
(a) If Excel 2003 opens the file and saves it, the bug in the ISAM driver
remains.
(b) If Excel 2003 opens the file and I double-click each of the troublesome
cells, but do not do any other action (no typing or copying or pasting or
anything), and then save the file, then the ISAM driver starts working.

Second, the troublesome cells are in the second row of the worksheet. The
second row contains no null values at all. The second row contains no plain
numerics. Some are plain strings like ABC or DEF. Others are strings like
'15 or '14 or '13 or '12 etc. Whatever version of Excel created the file,
it saved these strings as strings. When Excel 2003 opens the file, it
displays these strings as strings. The ISAM driver handles some of these
strings OK (for example '13 reads as a string in some worksheets but not in
others). The ISAM driver mishandles some of these strings (for example '15
reads as DBNull in nearly every worksheet).

Now, in Excel 2003, if I double-click one of these troublesome cells, then
Excel 2003 displays a small blue triangle in one corner of the cell. The
explanation is that the string is a string because it starts with an
apostrophe. Excel 2003 is correct. The apostrophe was already there when
the file was created. Excel 2003 knows how to read the file even if an
older broken version miswrote the file. Only the ISAM driver has random
problems reading it.

If the ISAM driver reads 8 rows then it should have no problem at all. The
second row consists 100% of strings. Rows other than the second row have
some strings and some null values. The worksheets do not contain any
numerics at all. The ISAM driver should find the strings in the second row
and it should read strings correctly. Again, if I open the file in Excel
2003, double-click every cell in the second row, and save the file, then
suddenly the ISAM driver really does find the strings in the second row and
it reads strings correctly.

Therefore I think that you did not diagnose the ISAM driver correctly.

Of course I can't send you the real Excel files, but if I have time then
I'll try to install Excel 97 into a Virtual PC and see if I can construct a
repro.

You mentioned two workarounds in
http://support.microsoft.com...
The first workaround sounds more complicated than mine, since I only have to
double-click. But I don't want to tell our customer to install Excel 2003,
reopen all their Excel files, double-click every cell in the second row of
every worksheet, and save. I want to fix the ISAM driver. The second
workaround is halfway workable because I would be glad to add IMEX=1 to the
connection string, but I don't think I have the right to make a global
change to our customer's registry.

Thank you for the explanation that Excel 2003 examines each cell but the
ISAM driver doesn't. Something is still very strange though. How can the
ISAM driver think that an apostrophe is a digit? There are no cells like a
plain 15 at all, they are already strings like '15. How can the ISAM driver
think that a column is numeric when the column doesn't contain any numeric
values?

"Jialiang Ge [MSFT]" <jialge@online.microsoft.com> wrote in message
news:03lYNTTaIHA.4720@TK2MSFTNGHUB02.phx.gbl...
> Hello,
>
> From your post, my understanding on this issue is: you wonder why some
> Excel worksheet cells returns DBNull through OLEDB or ODBC driver while
> they are actually not Null, and how to work-around it. If I'm off base,
> please feel free to let me know.
>
> In our best guess without your xls file that we can test on directly, the
> problem is caused by a limitation of the Excel ISAM driver in that once it
> determines the datatype of an Excel column, it will return a Null for any
> value that is not of the datatype the ISAM driver has defaulted to for
> that
> excel column. The Excel driver reads a certain number of rows (by default,
> 8 rows) in the specified source to guess at the data type of each column.
> When a column appears to contain mixed data types, especially numeric data
> mixed with text data, the driver decides in favor of the majority data
> type, and returns null values for cells that contain data of the other
> type. (In a tie, the numeric type wins.) Most cell formatting options in
> the Excel worksheet do not seem to affect this data type determination.
> Here is a KB article that illustrates this limitation in detail:
> http://support.microsoft.com....
>
> In order to prove the hypothesis and narrow down our focus, we can do such
> a test:
> (1) Check the first 8 cells of the columns that, you said, can return the
> values correctly, and see if the 8 cells are of the same data type (text
> or
> numeric, etc)
> (2) Check the first 8 cells of the columns that, you said, returns DBNull
> values (crashes), and see if the 8 cells are of the same data type (text
> or
> numeric, etc). In addition, if we manually change the values of the 8
> cells
> to text (not numeric) in Excel, and run your program again, can they be
> retrieved successfully now?
>
> If
> test (1) turns out to be the same datatype, -and-
> test (2) proves that the 8 cells are not of the same datatype. -and-
> test (2) proves that when the 8 cells are set to the same datatype
> manually in Excel, the data can be retrieved successfully.
> Then
> we can say the problem is indeed caused by
> http://support.microsoft.com....
> Else, I hope you can send the xls to my mailbox
> (jialge@nospam.microsoft.com, remove 'nospam.') , and I will test on it to
> find out the root cause.
>
> According to the KB article http://support.microsoft.com..., there
> are 2 workarounds:
> (1) Insure that the data in Excel is entered as text. Just reformatting
> the Excel column to Text will not accomplish this. You must re-enter the
> existing values after reformatting the Excel column. In Excel, you can use
> F5 to re-enter existing values in the selected cell.
> If you xls source file is not allowed to be edited, I don't think this
> workaround will fit your current situation.
> (2) Add the IMEX=1 flag in the connection string, or change the default
> TypeGuessRows=8 to a larger number, so the Excel will have a more correct
> guess of the datatype.
>
> Now, for the questions when you discuss the issue when Patrice:
>> Is there any way to tell the driver to use the types that I specified?
> And Is there any way to tell the driver that a field containing?
> I have to let you know that we cannot force the datatype programmatically
> due to the ISAM driver limitation (see the above KB article).
>
>> How does Excel 2003 do it? Somehow Excel 2003 figures out that '15 is a
> string, and it will even tell us so if we double-click on that cell.
> In Excel 2003, the datatype of each cell is decided per cell, not per
> column. If a cell contains '15 as a string, Excel feels that it is not
> likely to be a numeric cell, so it will displays them with a little
> triangle in one corner. However, in ISAM driver, the datatype is decided
> in
> a completely different way. (see my previous explanation).
>
> Please let me know if you have any other concerns, or need anything else.
>
> Regards,
> Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
> Microsoft Online Community Support
>
> ==================================================
> For MSDN subscribers whose posts are left unanswered, please check this
> document: http://blogs.msdn.com/msdnts/pages/posting...
>
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
> ications. If you are using Outlook Express/Windows Mail, please make sure
> you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
> see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/de....
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

Norman Diamond

2/7/2008 8:13:00 AM

Mr./Ms. Ge, thank you for referring me to
http://support.microsoft.com...

It looks like the registry's value for ImportMixedTypes defaults to Text, so
adding IMEX=1 to the connection string has a good chance of success. The
customer might still have problems on some machines but there is a good
chance of success.

I still don't think my program should change the customer's registry if the
value is different though.

"Norman Diamond" <ndiamond@newsgroup.nospam> wrote in message
news:u89QI4VaIHA.4140@TK2MSFTNGP04.phx.gbl...
> Mr./Ms. Ge, thank you for understanding the problem correctly. However, I
> think that you have not diagnosed the bug in the Excel ISAM driver.
>
> First, I think (though I am not sure) that a file created by Excel 2003
> will be readable correctly by the Excel ISAM driver. I think that the
> troublesome files were created by Excel 97 or 2000 or XP (though I am not
> sure). I have reproduced two odd cases:
> (a) If Excel 2003 opens the file and saves it, the bug in the ISAM driver
> remains.
> (b) If Excel 2003 opens the file and I double-click each of the
> troublesome cells, but do not do any other action (no typing or copying or
> pasting or anything), and then save the file, then the ISAM driver starts
> working.
>
> Second, the troublesome cells are in the second row of the worksheet. The
> second row contains no null values at all. The second row contains no
> plain numerics. Some are plain strings like ABC or DEF. Others are
> strings like '15 or '14 or '13 or '12 etc. Whatever version of Excel
> created the file, it saved these strings as strings. When Excel 2003
> opens the file, it displays these strings as strings. The ISAM driver
> handles some of these strings OK (for example '13 reads as a string in
> some worksheets but not in others). The ISAM driver mishandles some of
> these strings (for example '15 reads as DBNull in nearly every worksheet).
>
> Now, in Excel 2003, if I double-click one of these troublesome cells, then
> Excel 2003 displays a small blue triangle in one corner of the cell. The
> explanation is that the string is a string because it starts with an
> apostrophe. Excel 2003 is correct. The apostrophe was already there when
> the file was created. Excel 2003 knows how to read the file even if an
> older broken version miswrote the file. Only the ISAM driver has random
> problems reading it.
>
> If the ISAM driver reads 8 rows then it should have no problem at all.
> The second row consists 100% of strings. Rows other than the second row
> have some strings and some null values. The worksheets do not contain any
> numerics at all. The ISAM driver should find the strings in the second
> row and it should read strings correctly. Again, if I open the file in
> Excel 2003, double-click every cell in the second row, and save the file,
> then suddenly the ISAM driver really does find the strings in the second
> row and it reads strings correctly.
>
> Therefore I think that you did not diagnose the ISAM driver correctly.
>
> Of course I can't send you the real Excel files, but if I have time then
> I'll try to install Excel 97 into a Virtual PC and see if I can construct
> a repro.
>
> You mentioned two workarounds in
> http://support.microsoft.com...
> The first workaround sounds more complicated than mine, since I only have
> to double-click. But I don't want to tell our customer to install Excel
> 2003, reopen all their Excel files, double-click every cell in the second
> row of every worksheet, and save. I want to fix the ISAM driver. The
> second workaround is halfway workable because I would be glad to add
> IMEX=1 to the connection string, but I don't think I have the right to
> make a global change to our customer's registry.
>
> Thank you for the explanation that Excel 2003 examines each cell but the
> ISAM driver doesn't. Something is still very strange though. How can the
> ISAM driver think that an apostrophe is a digit? There are no cells like
> a plain 15 at all, they are already strings like '15. How can the ISAM
> driver think that a column is numeric when the column doesn't contain any
> numeric values?
>
>
> "Jialiang Ge [MSFT]" <jialge@online.microsoft.com> wrote in message
> news:03lYNTTaIHA.4720@TK2MSFTNGHUB02.phx.gbl...
>> Hello,
>>
>> From your post, my understanding on this issue is: you wonder why some
>> Excel worksheet cells returns DBNull through OLEDB or ODBC driver while
>> they are actually not Null, and how to work-around it. If I'm off base,
>> please feel free to let me know.
>>
>> In our best guess without your xls file that we can test on directly, the
>> problem is caused by a limitation of the Excel ISAM driver in that once
>> it
>> determines the datatype of an Excel column, it will return a Null for any
>> value that is not of the datatype the ISAM driver has defaulted to for
>> that
>> excel column. The Excel driver reads a certain number of rows (by
>> default,
>> 8 rows) in the specified source to guess at the data type of each column.
>> When a column appears to contain mixed data types, especially numeric
>> data
>> mixed with text data, the driver decides in favor of the majority data
>> type, and returns null values for cells that contain data of the other
>> type. (In a tie, the numeric type wins.) Most cell formatting options in
>> the Excel worksheet do not seem to affect this data type determination.
>> Here is a KB article that illustrates this limitation in detail:
>> http://support.microsoft.com....
>>
>> In order to prove the hypothesis and narrow down our focus, we can do
>> such
>> a test:
>> (1) Check the first 8 cells of the columns that, you said, can return the
>> values correctly, and see if the 8 cells are of the same data type (text
>> or
>> numeric, etc)
>> (2) Check the first 8 cells of the columns that, you said, returns DBNull
>> values (crashes), and see if the 8 cells are of the same data type (text
>> or
>> numeric, etc). In addition, if we manually change the values of the 8
>> cells
>> to text (not numeric) in Excel, and run your program again, can they be
>> retrieved successfully now?
>>
>> If
>> test (1) turns out to be the same datatype, -and-
>> test (2) proves that the 8 cells are not of the same datatype. -and-
>> test (2) proves that when the 8 cells are set to the same datatype
>> manually in Excel, the data can be retrieved successfully.
>> Then
>> we can say the problem is indeed caused by
>> http://support.microsoft.com....
>> Else, I hope you can send the xls to my mailbox
>> (jialge@nospam.microsoft.com, remove 'nospam.') , and I will test on it
>> to
>> find out the root cause.
>>
>> According to the KB article http://support.microsoft.com..., there
>> are 2 workarounds:
>> (1) Insure that the data in Excel is entered as text. Just reformatting
>> the Excel column to Text will not accomplish this. You must re-enter the
>> existing values after reformatting the Excel column. In Excel, you can
>> use
>> F5 to re-enter existing values in the selected cell.
>> If you xls source file is not allowed to be edited, I don't think this
>> workaround will fit your current situation.
>> (2) Add the IMEX=1 flag in the connection string, or change the default
>> TypeGuessRows=8 to a larger number, so the Excel will have a more correct
>> guess of the datatype.
>>
>> Now, for the questions when you discuss the issue when Patrice:
>>> Is there any way to tell the driver to use the types that I specified?
>> And Is there any way to tell the driver that a field containing?
>> I have to let you know that we cannot force the datatype programmatically
>> due to the ISAM driver limitation (see the above KB article).
>>
>>> How does Excel 2003 do it? Somehow Excel 2003 figures out that '15 is a
>> string, and it will even tell us so if we double-click on that cell.
>> In Excel 2003, the datatype of each cell is decided per cell, not per
>> column. If a cell contains '15 as a string, Excel feels that it is not
>> likely to be a numeric cell, so it will displays them with a little
>> triangle in one corner. However, in ISAM driver, the datatype is decided
>> in
>> a completely different way. (see my previous explanation).
>>
>> Please let me know if you have any other concerns, or need anything else.
>>
>> Regards,
>> Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
>> Microsoft Online Community Support
>>
>> ==================================================
>> For MSDN subscribers whose posts are left unanswered, please check this
>> document: http://blogs.msdn.com/msdnts/pages/posting...
>>
>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
>> ications. If you are using Outlook Express/Windows Mail, please make sure
>> you clear the check box "Tools/Options/Read: Get 300 headers at a time"
>> to
>> see your reply promptly.
>>
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent
>> issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each
>> follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> http://msdn.microsoft.com/subscriptions/support/de....
>> ==================================================
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>

Norman Diamond

2/7/2008 9:21:00 AM

Mr./Ms. Ge,

Thank you for suggesting the Office Object Model instead of the Excel ISAM
driver. However, I wonder if I understand this correctly. When I tried to
use the Office Object Model, it depended on Excel being installed on the
target machine.

Our customer has Excel installed on some machines in order to create these
spreadsheets. However, my program has to run on machines where Excel is not
installed, and my program has to understand the contents of the
spreadsheets. My program controls hardware devices where the spreadsheets
say it can.

Is it possible to use Office Object Model without Excel being installed?

"Jialiang Ge [MSFT]" <jialge@online.microsoft.com> wrote in message
news:pZDCOZWaIHA.5484@TK2MSFTNGHUB02.phx.gbl...
> Hello,
>
> The ISAM driver product team published the KB article
> http://support.microsoft.com... to explain the limitation. They did
> not iterate each cell in the column to determine the datatype because of
> some performance issues. I am sorry for the inconveniences if such a
> design
> does not fit your request.
>
> As you see, the workaround is to set IMEX=1 in the connection string.
>
> Resetting the TypeGuessRows value in the regedit is useful when, for
> instance, there is only one xls datasource on the server, and a program
> reads data from the datasource, so we can reset the TypeGuessRows value on
> the server's regedit, and help ISAM driver make a better guess.
> Apparently,
> this workaround does not fit your situation, either.
>
> The safest way that I'd recommend is to use Office object model, rather
> than ISAM driver, especially when there is a very large chance that ISAM
> driver cannot detect the corrent datatype according to the first 8 cells
> of
> a column.
>
> Regards,
> Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
> Microsoft Online Community Support
>
> =================================================
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> =================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

Norman Diamond

2/8/2008 3:56:00 AM

That is correct, the workbooks only consist of plain text, no charts and no
numeric fields.

We were going to suggest CSV to our customer but there are two problems:
(1) One Excel file contains more than one worksheet (describing more than
one hardware device).
(2) In some places several cells are merged. I could detect merged cells by
testing for DBNull before testing for "".

Anyway, I think that IMEX=1 is a big help. In cases where IMEX=1 doesn't
work, we will have to advise our customer to open the Excel file in Excel
2003, double-click every cell in the second row of every worksheet, save the
Excel file, and then try using the result on the target systems.

Actually I use CSV files for writing results, and I have a separate
complaint about that (it is sloooooooooow) but I will write a separate
posting about that.

"Jialiang Ge [MSFT]" <jialge@online.microsoft.com> wrote in message
news:s8g5Z1faIHA.4200@TK2MSFTNGHUB02.phx.gbl...
> Hello,
>
> The prerequisite of using Office Object Model is to install the Office
> product on the target machine. Since your customers do not have Excel
> installed, the suggestion of using Office Object Model does not fit the
> issue.
>
> I notice that "the Excel workbooks contain the hardware device
> information,
> and the client programs read the info. to control their hardware". Can I
> understand that the workbooks only consist of plain text? Or even if there
> are some charts in the workbook, the charts will be of no use for the
> client programs? If that, here are two new suggestions for your reference:
>
> Suggestion1. Use csv file with schema.ini
> CSV (comma-separated values) file is a plain text file type that stores
> tabular data. For instance, we open notepad.exe, type the following
> content:
> 1, 2, 3, 4
> 5, 6, 7, 8
> save the file as filename.csv, then open it in Excel. The csv shows a 2
> rows - 4 columns table, similar to what we used to do in Excel xls files.
>
> CSV is a file type that is supported by Excel. We can save the original
> xls
> files as csv with Excel Object Model (SaveAs) on the server computers that
> have Excel installed, then distribute the generated csv files to the
> clients. In the meantime, we also distribute a file "Schema.ini" file with
> the csv. A schema.ini file contains the specifics on how data is formatted
> in a particular text file (csv is actually a plain text file, while xls is
> not, so schema.ini cannot apply to xls files to specify column types), and
> is used by the Text ISAM driver to read and manipulate data. For more
> information about Schema.ini, please see:
> http://support.microsoft.com/kb/14...
> http://msdn2.microsoft.com/en-us/library/ms7...
> http://weblogs.asp.net/fmarguerie/archive/2003/10/01/manipulating....
> aspx
>
> After we distribute both the csv and the schema.ini in the same folder, we
> can retrieve the data with OLEDB or ODBC driver as what we did for xls
> files. However, this time, the data will be retrieved with the datatype
> that we specified in the schema.ini.
>
> Suggestion2.
> We can also read the xls content on a computer with Office Object model,
> and write the contents with StreamWriter into a txt file where data is
> delimitated by, e.g, tab '\t'. Then distribute the file to the client
> machines. Our program will read the txt file with StreamReader, manipulate
> each line of the txt, and split the values with '\t'.
>
> Please have a try to see if the 2 suggestions fit your situation. If you
> have any other concerns, feel free to let me know.
>
> Regards,
> Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
> Microsoft Online Community Support
>
> =================================================
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> =================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>

Norman Diamond

2/8/2008 7:53:00 AM

I am wrong. IMEX=1 is a disaster.

Without IMEX=1, the Excel driver reads some cells in the second row as
DBNull instead of text. My program can test for this. My program can
report to the user that the Excel driver mishandles this Excel file so the
user has to play with the file in Excel 2003 and try again.

With IMEX=1, the Excel driver reads all cells in the second row as text. My
program thinks that the Excel driver is working. Then in the 58th row, the
Excel driver reads some cells as DBNull instead of text. My program thinks
that those cells are null. My program never sees the text. The text starts
with parentheses and letters, so no one could ever think they're numeric.
But the Excel driver gives DBNull to my program, my program thinks it is
working, the user thinks it is working, they get wrong results, and they
don't know that the results are wrong.

I had to take out IMEX=1. I have to warn the user that the Excel driver
cannot handle this Excel file.

"Norman Diamond" <ndiamond@newsgroup.nospam> wrote in message
news:%23AQtUagaIHA.5980@TK2MSFTNGP04.phx.gbl...
> That is correct, the workbooks only consist of plain text, no charts and
> no numeric fields.
>
> We were going to suggest CSV to our customer but there are two problems:
> (1) One Excel file contains more than one worksheet (describing more than
> one hardware device).
> (2) In some places several cells are merged. I could detect merged cells
> by testing for DBNull before testing for "".
>
> Anyway, I think that IMEX=1 is a big help. In cases where IMEX=1 doesn't
> work, we will have to advise our customer to open the Excel file in Excel
> 2003, double-click every cell in the second row of every worksheet, save
> the Excel file, and then try using the result on the target systems.
>
> Actually I use CSV files for writing results, and I have a separate
> complaint about that (it is sloooooooooow) but I will write a separate
> posting about that.
>
>
> "Jialiang Ge [MSFT]" <jialge@online.microsoft.com> wrote in message
> news:s8g5Z1faIHA.4200@TK2MSFTNGHUB02.phx.gbl...
>> Hello,
>>
>> The prerequisite of using Office Object Model is to install the Office
>> product on the target machine. Since your customers do not have Excel
>> installed, the suggestion of using Office Object Model does not fit the
>> issue.
>>
>> I notice that "the Excel workbooks contain the hardware device
>> information,
>> and the client programs read the info. to control their hardware". Can I
>> understand that the workbooks only consist of plain text? Or even if
>> there
>> are some charts in the workbook, the charts will be of no use for the
>> client programs? If that, here are two new suggestions for your
>> reference:
>>
>> Suggestion1. Use csv file with schema.ini
>> CSV (comma-separated values) file is a plain text file type that stores
>> tabular data. For instance, we open notepad.exe, type the following
>> content:
>> 1, 2, 3, 4
>> 5, 6, 7, 8
>> save the file as filename.csv, then open it in Excel. The csv shows a 2
>> rows - 4 columns table, similar to what we used to do in Excel xls files.
>>
>> CSV is a file type that is supported by Excel. We can save the original
>> xls
>> files as csv with Excel Object Model (SaveAs) on the server computers
>> that
>> have Excel installed, then distribute the generated csv files to the
>> clients. In the meantime, we also distribute a file "Schema.ini" file
>> with
>> the csv. A schema.ini file contains the specifics on how data is
>> formatted
>> in a particular text file (csv is actually a plain text file, while xls
>> is
>> not, so schema.ini cannot apply to xls files to specify column types),
>> and
>> is used by the Text ISAM driver to read and manipulate data. For more
>> information about Schema.ini, please see:
>> http://support.microsoft.com/kb/14...
>> http://msdn2.microsoft.com/en-us/library/ms7...
>> http://weblogs.asp.net/fmarguerie/archive/2003/10/01/manipulating....
>> aspx
>>
>> After we distribute both the csv and the schema.ini in the same folder,
>> we
>> can retrieve the data with OLEDB or ODBC driver as what we did for xls
>> files. However, this time, the data will be retrieved with the datatype
>> that we specified in the schema.ini.
>>
>> Suggestion2.
>> We can also read the xls content on a computer with Office Object model,
>> and write the contents with StreamWriter into a txt file where data is
>> delimitated by, e.g, tab '\t'. Then distribute the file to the client
>> machines. Our program will read the txt file with StreamReader,
>> manipulate
>> each line of the txt, and split the values with '\t'.
>>
>> Please have a try to see if the 2 suggestions fit your situation. If you
>> have any other concerns, feel free to let me know.
>>
>> Regards,
>> Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
>> Microsoft Online Community Support
>>
>> =================================================
>> When responding to posts, please "Reply to Group" via your newsreader
>> so that others may learn and benefit from your issue.
>> =================================================
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>
>

jialge

2/11/2008 2:35:00 AM

Hello,

I can hear that you are frustrated by the limits and issues of the ISAM
driver. I will try my best to help you work-around the situation.

So far, we have discussed the following five suggestions:
(1) Popup a message to ask the end users to convert the xls data to text
manually.
(2) Use IMEX=1 flag in the connection string
(3) Use csv file type + schema.ini, instead of xls
(4) Use Office object model, instead of ISAM driver.
(5) Enlarge the default TypeGuessRows in reg so as to have a better guess
of data type.

Suggestion (3) was disapproved because a csv file cannot contain multiple
worksheets. Suggestion (4) was inappropriate because the uses may not have
Excel installed. Suggestion (5) needs to change users' reg setting, so it
was not taken into consideration, either. Suggestion (2) encounter a
problem that a cell returns DBNull unexpectedly, and Suggestion (1) is the
last thing we would use.

Before I can proceed, I have some questions regarding the unexpected DBNull
returned by the Suggestion (2) - IMEX=1:

It was mentioned that "the text starts with parentheses and letters, so no
one could ever think they're numeric.". I am not sure if I understand it
correctly. Do you mean the cell's content is something like "{{afd" if we
open the xls directly in Excel, however, it returns DBNull when we read it
with OLEDB? What's the real content of the cell in Excel? In order to
reproduce the unexpected behavior on my side, I have tested such cell
content as "{{afd", "{}", "afds", "134", but they return texts as expected.
*In my best guess, is it possible that the cell you referred to is inside a
merged cell?* It is very much appreciated if you could send a sample xls to
my mailbox (jialge@microsoft.com) so that I can have a clearer picture of
the problem.

If the cell is really inside a merged cell, a possible workaround is to
split all the merged cell on a computer with Office installed before we
distribute the xls to the end users. To split the merged cell
programmatically, please refer to the post:
http://www.themssforum.com/ExcelProgramming/splitting-cel...
For you conveniences, I paste the main Unmerge function here:
Sub UnMerge_Cells()
Dim currentCell As Range
Dim mergeArea As Range
Dim mergeValue As Variant
Dim mergeCell As Range

For Each currentCell In Me.UsedRange
If currentCell.MergeCells Then
Set mergeArea = currentCell.mergeArea
mergeValue = mergeArea.Cells(1, 1).Value2
' split the area
mergeArea.UnMerge
For Each mergeCell In mergeArea
mergeCell.Value2 = mergeValue
Next mergeCell
End If
Next currentCell
End Sub

Regards,
Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Norman Diamond

2/12/2008 12:51:00 AM

> So far, we have discussed the following five suggestions:
> (1) Popup a message to ask the end users to convert the xls data to text
> manually.

That would not work because the Excel file contains several worksheets. In
my experience a text file can only contain a single worksheet.

But I do pop up a message to ask the end user to use Excel 2003 (on a
machine where Excel 2003 is installed) and adjust all cells in the second
row of each worksheet. This seems to be the most usable solution in this
case.

> (2) Use IMEX=1 flag in the connection string

This is a disaster, as I described in the posting which you replied to this
time. IMEX=1 persuaded the Excel driver to read all cells in the second row
of each worksheet as text, so my program thought it was working. But then
on the 58th row of one worksheet, the Excel driver returned some cells as
DBNull instead of returning the actual text. My program thought that those
cells were really null. Usually an end user would think that they're being
shown correct results, they wouldn't know that they're being shown wrong
results, and the effect is a disaster.

Without IMEX=1, my program can detect the failure of the Excel driver on the
second row, so my program can pop up a message box. So it is better to stop
using IMEX=1.

> (3) Use csv file type + schema.ini, instead of xls

See (1).

> (4) Use Office object model, instead of ISAM driver.

Your previous posting said that use of the Office object model would require
Excel to be installed on the target machine.

> (5) Enlarge the default TypeGuessRows in reg so as to have a better guess
> of data type.

I think of experimenting on a development machine to see if setting
TypeGuessRows to 200 will solve this. But I still can't tell the customer
to do this on all of their workers' machines.

"Jialiang Ge [MSFT]" <jialge@online.microsoft.com> wrote in message
news:EISeHbFbIHA.2164@TK2MSFTNGHUB02.phx.gbl...
> Hello,
>
> I can hear that you are frustrated by the limits and issues of the ISAM
> driver. I will try my best to help you work-around the situation.
>
> So far, we have discussed the following five suggestions:
> (1) Popup a message to ask the end users to convert the xls data to text
> manually.
> (2) Use IMEX=1 flag in the connection string
> (3) Use csv file type + schema.ini, instead of xls
> (4) Use Office object model, instead of ISAM driver.
> (5) Enlarge the default TypeGuessRows in reg so as to have a better guess
> of data type.
>
> Suggestion (3) was disapproved because a csv file cannot contain multiple
> worksheets. Suggestion (4) was inappropriate because the uses may not have
> Excel installed. Suggestion (5) needs to change users' reg setting, so it
> was not taken into consideration, either. Suggestion (2) encounter a
> problem that a cell returns DBNull unexpectedly, and Suggestion (1) is the
> last thing we would use.
>
> Before I can proceed, I have some questions regarding the unexpected
> DBNull
> returned by the Suggestion (2) - IMEX=1:
>
> It was mentioned that "the text starts with parentheses and letters, so no
> one could ever think they're numeric.". I am not sure if I understand it
> correctly. Do you mean the cell's content is something like "{{afd" if we
> open the xls directly in Excel, however, it returns DBNull when we read it
> with OLEDB? What's the real content of the cell in Excel? In order to
> reproduce the unexpected behavior on my side, I have tested such cell
> content as "{{afd", "{}", "afds", "134", but they return texts as
> expected.
> *In my best guess, is it possible that the cell you referred to is inside
> a
> merged cell?* It is very much appreciated if you could send a sample xls
> to
> my mailbox (jialge@microsoft.com) so that I can have a clearer picture of
> the problem.
>
> If the cell is really inside a merged cell, a possible workaround is to
> split all the merged cell on a computer with Office installed before we
> distribute the xls to the end users. To split the merged cell
> programmatically, please refer to the post:
> http://www.themssforum.com/ExcelProgramming/splitting-cel...
> For you conveniences, I paste the main Unmerge function here:
> Sub UnMerge_Cells()
> Dim currentCell As Range
> Dim mergeArea As Range
> Dim mergeValue As Variant
> Dim mergeCell As Range
>
> For Each currentCell In Me.UsedRange
> If currentCell.MergeCells Then
> Set mergeArea = currentCell.mergeArea
> mergeValue = mergeArea.Cells(1, 1).Value2
> ' split the area
> mergeArea.UnMerge
> For Each mergeCell In mergeArea
> mergeCell.Value2 = mergeValue
> Next mergeCell
> End If
> Next currentCell
> End Sub
>
> Regards,
> Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
> Microsoft Online Community Support
>
> =================================================
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> =================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

jialge

2/12/2008 2:00:00 AM

Hello,

I am sorry because, possibly, I did not make the meaning clear enough in my
last reply.

In my last reply, I inquired the real content of the cell from which the
program returns DBNull. If we open the xls directly in Excel, what will we
see on the 58th row? Is it possibly in a merged cell?

This information is very important before I can proceed to propose futher
suggestions.

It was mentioned that "the text starts with parentheses and letters, so no
one could ever think they're numeric.". I am not sure if I understand it
correctly. Do you mean the cell's content is something like "{{afd" if we
open the xls directly in Excel, however, it returns DBNull when we read it
with OLEDB? What's the real content of the cell in Excel? In order to
reproduce the unexpected behavior on my side, I have tested such cell
content as "{{afd", "{}", "afds", "134", but they return texts as expected.
*In my best guess, is it possible that the cell you referred to is inside a
merged cell?* It is very much appreciated if you could send a sample xls to
my mailbox (jialge@microsoft.com) so that I can have a clearer picture of
the problem.

If the cell is really inside a merged cell, a possible workaround is to
split all the merged cell on a computer with Office installed before we
distribute the xls to the end users. To split the merged cell
programmatically, please refer to the post:
http://www.themssforum.com/ExcelProgramming/splitting-cel...
For you conveniences, I paste the main Unmerge function here:
Sub UnMerge_Cells()
Dim currentCell As Range
Dim mergeArea As Range
Dim mergeValue As Variant
Dim mergeCell As Range

For Each currentCell In Me.UsedRange
If currentCell.MergeCells Then
Set mergeArea = currentCell.mergeArea
mergeValue = mergeArea.Cells(1, 1).Value2
' split the area
mergeArea.UnMerge
For Each mergeCell In mergeArea
mergeCell.Value2 = mergeValue
Next mergeCell
End If
Next currentCell
End Sub

Regards,
Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Norman Diamond

2/12/2008 4:37:00 AM

> In my last reply, I inquired the real content of the cell from which the
> program returns DBNull. If we open the xls directly in Excel, what will we
> see on the 58th row?

That particular cell contains a string. The string starts with a
parenthesis and a letter:
(R

> Is it possibly in a merged cell?

It is not. But my program thinks the cell is in a merged cell, because the
Excel driver returned DBNull.

We cannot split merged cells. Merged cells have meanings and my program
recognizes them. Maybe this helps explain why it is a disaster when my
program thinks that some cell is part of a merged cell when the cell really
has its own contents.

As mentioned in previous postings, I stopped using IMEX=1. I just inspect
the second row of each worksheet to see if all cells read as strings, and if
some of them read as DBNull then I pop up a warning. Then the user has to
use a machine with Excel installed and reformat the spreadsheet. I don't
think we'll find a better solution than this.

I still wish that the Excel driver would honour these settings though, as
shown in my first posting:
dataAdapter.FillSchema(dataTable, SchemaType.Source);
DataColumnCollection dataColumns = dataTable.Columns;
int columnCount = dataColumns.Count;
foreach (DataColumn dataColumn in dataColumns)
{
dataColumn.DataType = typeof(string);
}
dataAdapter.Fill(dataTable);

"Jialiang Ge [MSFT]" <jialge@online.microsoft.com> wrote in message
news:dZV$wrRbIHA.5204@TK2MSFTNGHUB02.phx.gbl...
> Hello,
>
> I am sorry because, possibly, I did not make the meaning clear enough in
> my
> last reply.
>
> In my last reply, I inquired the real content of the cell from which the
> program returns DBNull. If we open the xls directly in Excel, what will we
> see on the 58th row? Is it possibly in a merged cell?
>
> This information is very important before I can proceed to propose futher
> suggestions.
>
> It was mentioned that "the text starts with parentheses and letters, so no
> one could ever think they're numeric.". I am not sure if I understand it
> correctly. Do you mean the cell's content is something like "{{afd" if we
> open the xls directly in Excel, however, it returns DBNull when we read it
> with OLEDB? What's the real content of the cell in Excel? In order to
> reproduce the unexpected behavior on my side, I have tested such cell
> content as "{{afd", "{}", "afds", "134", but they return texts as
> expected.
> *In my best guess, is it possible that the cell you referred to is inside
> a
> merged cell?* It is very much appreciated if you could send a sample xls
> to
> my mailbox (jialge@microsoft.com) so that I can have a clearer picture of
> the problem.
>
> If the cell is really inside a merged cell, a possible workaround is to
> split all the merged cell on a computer with Office installed before we
> distribute the xls to the end users. To split the merged cell
> programmatically, please refer to the post:
> http://www.themssforum.com/ExcelProgramming/splitting-cel...
> For you conveniences, I paste the main Unmerge function here:
> Sub UnMerge_Cells()
> Dim currentCell As Range
> Dim mergeArea As Range
> Dim mergeValue As Variant
> Dim mergeCell As Range
>
> For Each currentCell In Me.UsedRange
> If currentCell.MergeCells Then
> Set mergeArea = currentCell.mergeArea
> mergeValue = mergeArea.Cells(1, 1).Value2
> ' split the area
> mergeArea.UnMerge
> For Each mergeCell In mergeArea
> mergeCell.Value2 = mergeValue
> Next mergeCell
> End If
> Next currentCell
> End Sub
>
> Regards,
> Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
> Microsoft Online Community Support
>
> =================================================
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> =================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

microsoft.public.inetserver.asp.components

制球現住所世界銀行抑え年毎当社比較べる三時のおやつ

Lee Gillie

jialge

Norman Diamond

Norman Diamond

Norman Diamond

Norman Diamond

Norman Diamond

jialge

Norman Diamond

jialge

Norman Diamond

x Login to ForumsZone