Ado.net Oledbdatareader и Oledbcommand проблема чтения из Excel файла

Тема в разделе ".NET", создана пользователем gammaray, 3 окт 2008.

  1. gammaray

    gammaray Гость

    Здравствуйте!
    Пишу программу, которая считывает данные из таблицы Excel и заносит их в базу SQL. В таблице данные представлены в виде слов и цифр.
    И проблема заключается в том, что при считывании из файла некоторые столбцы имеют не тот формат, который нужен. Я все столбцы хочу считать как строки. Но некоторые читаются, как целотипные, и поэтому символьные данные данные в этих столбцах теряются. Вот типы данных считанных строк:
    [​IMG]
    Получил эти данные через просмотр в отладчике объекта DataReader.
    Код такой:
    Код (Text):
    string[] restrictions = { null, null, "Лист1$", null };
    DataTable tbl = OleDBCon.GetSchema("Columns", restrictions); //OleDbCon - объект подключения к книге Excel
    Те данные, у которых следующие типы:
    COLUMN_FLAGS=6
    DATA_TYPE=130
    CHARACTER_MAXIMUM_LENGTH=255
    CHARACTER_OCTET_LENGTH=510
    NUMERIC_PRECISION=null
    читаются нормально. Так, как собственно и нужно. А остальные столбцы воспринимаются (как я понимаю) в качестве численных.

    Собственно вопрос: как в Excel отформатировать строки так, чтобы потом при считывании через OleDbConnection провайдер они имели нужный тип? Формат ячеек в самом Excel в типе не изменяет ничего!
     
  2. gammaray

    gammaray Гость

    Решено!
    IMEX
    Собственно изначально добавлял в строку подключения "IMEX=1", но убрал, когда возникли проблемы с записью в файл, которые как раз описаны по ссылке!
     
  3. gammaray

    gammaray Гость

    Поторопился я... "IMEX=1" тоже не помогает! Если читать не через OleDbDataReader, a через OleDbDataAdapter - результат тот же... Думаю, что проблему можно решить как-то в Excel. Пробовал скопировать все данные в новую книгу и поставить формат "Только значения" - результат аналогичный.
    Может как-то можно в самом Excel настроить SQL типы?
     
  4. gammaray

    gammaray Гость

    Вот нашел ответ здесь

    [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 файла без потери исходной строчки?
     
  5. gammaray

    gammaray Гость

    Ну а как эти кавычки проставлять? Не вручную же)
     
Загрузка...

Поделиться этой страницей