Вот нашел ответ
Ссылка скрыта от гостей
[quot автор]I suppose that makes a twisted kind of sense if you have spent a lot of time working with relational databases, but there are some unfortunate consequences of this design which aren’t obvious at first. For example, say your spreadsheet contains the following columns:
YearOfBirth Country PostalCode
1964 USA 10005
1970 USA 10001
1952 Canada K2P1R6
1981 Canada L3R3R2
1974 USA 10013
ADO.NET will correctly guess that the YearOfBirth column is numeric, and that the Country column is of type string. But what about the PostalCode column, which contains a mix of numbers and strings? In this case ADO.NET chooses the type based on the majority of the values (with a tie going to numeric). In this example 3 of the 5 postal codes are numeric, so ADO.NET will declare the column to be numeric. Therefore it will attempt to cast each cell to a number, which will fail for the Canadian postal codes - which will therefore come out as NULL values. Ha ha. Isn’t that fun?
Even more entertaining, there is absolutely no way to make this 100% reliable - although with some pain, you can improve the situation. Here’s what you need to do. First add the "IMEX=1" option to your connection string like this:
OleDbConnection dbConnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BAR.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;""");
That tells ADO.NET to honor the following registry key when reading the spreadsheet:
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes
This registry key tells ADO.NET what to do when it encounters mixed types in the first 8 rows. It can either be set to the string "Majority Type" (for the default behavior) or to "Text" (which forces the column to be of type string). Note that you are still screwed if the first 8 postal codes are numeric and the 9th is Canadian. (Not to mention that the "Text" option invokes handling that fails on strings over 255 characters, but let’s skip that for now.)
There’s also a second relevant registry setting (which is honored regardless of the IMEX option):
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
That says how many rows to scan to guess the datatype. The default is 8, but you can set it anywhere from 0-16 decimal (0 meaning "scan the first 16384 rows", and all other values meaning what they say). Putting this all together, the most reliable way to read a US/Canadian postal code is to use the following registry settings:
TypeGuessRows = 0
ImportMixedTypes = Text
That’s pretty close to perfect, although it will still fail if the first 16384 postal codes are numeric and any of the subsequent ones aren’t.
This is a Bad Design for so many reasons I don’t know where to start. First, the behavior of the spreadsheet importer should not depend on global registry settings - that is just inviting mysterious, data-dependent errors whenever other applications or users change those settings. All of those settings should be in the connect string and nowhere else. Second, there should be an option to say "I’m not sure what data is coming, but I want all of it - please coerce everything to something universal like an arbitrary-length string". Third, the interface should be stream-based, not file-based. If you are reading the spreadsheet data from the network, you shouldn’t have to save it to a temporary file in order to parse it. Fourth, you shouldn’t have to read the spreadsheet schema if you just want to select the worksheet by index (e.g. you want to read the first worksheet, whatever it happens to be called).[/quot]
Получается, что настолько все криво сделано в этом microsoft.jet.oledb... Менять значение реестра не вариант, потому что:
во-первых, если ставить рекомендуемое значение TypeGuessRows = 0, то при обращении к БД будут всегда считываться 16384 строк, что резко снизит производительность;
во-вторых, что это настолько кривой способ программирования, что не хочется даже туда влезать.
Придумал такое решение: принудительно вставлять во 2 строку использующегося Excel файла данные строкового типа, чтобы провайдер доступа к БД сразу ставил строковый тип у всех столбцов. Возник вопрос:
как с помощью SQL команды INSERT вставить данные во 2 строку Excel файла без потери исходной строчки?
