Проблемы с ADO

[Freeangel]

Здравствуйте.
Делаю импорт данных из Excel через ADO. Всё хорошо, но если тип данных в ячейке текст, то значение ячейки не читается. В чём может быть проблема, не подскажите?

[Delfins]

Может заформатированно как-то?
Почему не делать "пакетный" импорт через Clipboard? (ComExcelDocument_RU)... Помоему работает довольно быстро...

[kashperuk]

На всякий случай:
Это читали?
Вспомогательный класс для импорта из Excel через ADO

[Freeangel]

Так и было сделано, но проблема возникла у тебрминальных пользователей. Периодически во время импорта сбрасывался буфер

[Gustav]

Цитата:

Сообщение от Freeangel

Так и было сделано, но проблема возникла у тебрминальных пользователей. Периодически во время импорта сбрасывался буфер

(это, наверное, относится к Clipboard и ComExcelDocument_RU)

Цитата:

Сообщение от Freeangel

если тип данных в ячейке текст, то значение ячейки не читается.

1. а ошибку выдает какую-нибудь или просто пустую строку возвращает?
2. вы читаете этот текст из ячейки как значение какого типа? Покажите фрагмент кода, пожалуйста. ADO распознает для Excel ограниченный набор типов данных - может, в этом дело...
3. первые несколько строк (по умолчанию 8 - по ним ADO определяет тип поля) в этой текстовой колонке содержат пустые ячейки? или частично пустые ячейки (например, 3 - с текстом, а 5 - пустые или, что хуже, числа) ?

У меня, вроде, текст читается нормально и из General-ячейки, и из Text-ячейки... кстати, уточните, что вы имели в виду, говоря про "тип данных в ячейке текст"

Попробуйте перед импортом отсортировать строки в Excel так, чтобы первые несколько значений в проблемном текстовом поле были точно текстовыми.

[Freeangel]

Вот фрагмент кода:

    while (!rstExcel.EOF())
    {
        for (n = 0; n <= 9; n += 1)
        {
            switch(n)
            {
                case 0:
                    fld = flds.Item(n);
                    s1  = element.adoInfoValFromExcel(fld.Value(), fld.Type());
                break;
                case 4:
                    fld = flds.Item(n);
                    s3  = element.adoInfoValFromExcel(fld.Value(), fld.Type());
                break;
                case 9:
                    fld = flds.Item(n);
                    s2  = element.adoInfoValFromExcel(fld.Value(), fld.Type());
                break;
            }
        }

Значение в первом поле в первой ячейке A00150, во второй ячейке 150300.
Первое значение при чтение через ADO становится равным 0. Тип данных 5, что соответствует типу Double.

[Gustav]

Цитата:

Сообщение от Freeangel

Значение в первом поле в первой ячейке A00150, во второй ячейке 150300.
Первое значение при чтение через ADO становится равным 0. Тип данных 5, что соответствует типу Double.

Ну, похоже, в правильном направлении движемся...
Что в ячейках с 3-ей по 8-ую?

Потом попробуйте отсортировать строки в Excel по этому полю по убыванию, чтобы коды начинающиеся с букв стали первыми.

[Freeangel]

Три позиции с букв и так идут первыми, а дальше следуют числовые данные. Даже явно указывал тип Текст, но всё равно игнорируется

[Gustav]

Вот что на этот счёт вещает MS здесь:

Цитата:

A Caution about Mixed Data Types

As stated previously, ADO must guess at the data type for each column in your Excel worksheet or range. (This is not affected by Excel cell formatting settings.) A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.

For example:
• In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
• In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
• In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.

Как я обнаружил наяву ("This is not affected by Excel cell formatting settings.)"), установка формата ячейки в Text (вместо General) не меняет "числовую сущность" строки вида "150300" с точки зрения ADO. Т.е. даже если установить для такой колонки в Excel формат Text, ADO все равно будет его воспринимать как Double.


P.S. А вот и ОТВЕТ отсюда:

Цитата:

The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.

[Freeangel]

Спасибо. Вернусь варианту чтения через буфер

[Gustav]

Зачем так рано сдаваться-то?

Обнаружил два способа борьбы:
1. добавить во фрагмент ConnectionString "Extended Properties='Excel 8.0;HDR=Yes'" подстроку "IMEX=1", чтобы получилось "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'" (при этом как минимум одно из первых 8 значений должно быть текстом)
2. добавить ведущий апостроф (известный способ в Excel) в те ячейки первых 8 строк, которые выглядят как числовые ( 150300 => '150300 )

Далее, у меня получились следующие результаты исследования:
1. MAXSCANROWS при использовании из Аксапты у меня не почему-то не заработал (т.е. всегда всё определяли первые 8 строк)

2. тип данных в конкретном поле ADO определяет по значениям в первых 8 строках (даже при MAXSCANROWS=1 и при MAXSCANROWS=16; и даже при MAXSCANROWS=0, когда по идее должны сканироваться все записи таблицы)
2.1. если в первых 8 строках 5 текстовых значений и 3 числовых, то тип поля принимается как ТЕКСТ
2.2. если в первых 8 строках 3 текстовых значений и 5 числовых, то тип поля принимается как ЧИСЛО
2.1. если в первых 8 строках 4 текстовых значений и 4 числовых, то тип поля принимается как ЧИСЛО

3. Если добавить параметр IMEX=1, то (опять-таки всё определяется по первым 8 строкам):
3.1. Если в первых 8 строках есть хотя бы одно текстовое значение, то тип поля принимается как ТЕКСТ (!)
3.2. Если в первых 8 строках есть ТОЛЬКО ЧИСЛОВЫЕ значения, то тип поля принимается как ЧИСЛО (даже если все последующие значения, начиная с 9-го являются текстом)

Подробности про IMEX=1, например, здесь:

Цитата:

NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

[kashperuk]

Цитата:

Сообщение от Gustav

1. MAXSCANROWS при использовании из Аксапты у меня не почему-то не заработал (т.е. всегда всё определяли первые 8 строк)

Спасибо. Очень интересное и познавательное исследование.

А ты не пробовал менять значение TypeGuessRows в HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel?

Интересно было бы узнать, какой тип получается для разных значений этого ключа, в частности для значения 0?

[Gustav]

Цитата:

Сообщение от kashperuk

А ты не пробовал менять значение TypeGuessRows в HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel?

Идя навстречу пожеланиям Ивана, полез в Registry
Алгоритм "залезания" такой (цитирую отсюда):

Цитата:

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

To change the value of TypeGuessRows, use these steps:
1. On the Start menu, click Run. In the Run dialog box, type Regedt32, and then click OK.
2. Open the following key in the Registry editor:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
3. Double-click TypeGuessRows.
4. In the DWORD editor dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive, for Value data.
5. Click OK, and then exit the Registry Editor.

ВАЖНО: Нужно быть админом на машине, где вы меняете это значение TypeGuessRows. В противном случае при попытке изменить дефолтное значение "8" на другое вы получите сообщение вида "Cannot edit TypeGuessRows: Error writing the value's new contents"

Так вот, через Registry - РАБОТАЕТ! Freeangel, измените у себя на "3" (по количеству ваших трёх первых текстовых значений) и должно вам выйти счастье

По ходу наткнулся на большое хорошее обсуждение (блог) по смешанным типам данных
http://www.dailydoseofexcel.com/arch...ed-data-types/

И там, в частности, английским по белому говорится о том, что использование параметра MAXSCANROWS в строке подключения, увы, эффекта не имеет:

Цитата:

although it is mentioned in MSDN articles that MAXSCANROWS can be used in the extended properties of the connection string to override the TypeGuessRows registry keys, this seems to be a fallacy. Using MAXSCANROWS=0 in this way never does anything under any circumstances.

[kashperuk]

Цитата:

Сообщение от Gustav

Идя навстречу пожеланиям Ивана, полез в Registry
Алгоритм "залезания" такой (цитирую отсюда):
ВАЖНО: Нужно быть админом на машине, где вы меняете это значение TypeGuessRows. В противном случае при попытке изменить дефолтное значение "8" на другое вы получите сообщение вида "Cannot edit TypeGuessRows: Error writing the value's new contents"

Так вот, через Registry - РАБОТАЕТ! Freeangel, измените у себя на "3" (по количеству ваших трёх первых текстовых значений) и должно вам выйти счастье

Вот небольшой job для авто изменения этого ключа.
Я не знаю, где еще используется значение этого ключа, поэтому не хотелось бы рисковать лишний раз, изменяя его перманентно.
Поэтому советую вставить вот этот код перед импортом. А потом такой же после, меняя значение назад.

Единственное, что смущает - это необходимые права на изменение ключей реестра.

static void changeRegTypeGuessRows(Args _args)
{
    #winapi
    #define.ExcelJetEnginePath('SOFTWARE\\Microsoft\\Jet\\4.0\\Engines\\Excel\\')
    container   res;
    int         regHandle;
    int         newValue = 8;
    ;
    regHandle = winapi::regOpenKey(#HKEY_LOCAL_MACHINE, #ExcelJetEnginePath, #KEY_QUERY_VALUE);
    if (regHandle)
        res = winapi::regGetValue(regHandle, 'TypeGuessRows');
    if (res)
        info(strFmt("Old Value %1", con2str(res, '\n')));
    regHandle = winapi::regOpenKey(#HKEY_LOCAL_MACHINE, #ExcelJetEnginePath, #KEY_SET_VALUE);
    if (regHandle)
        info(strFmt("Result code of changing value of TypeGuessRows to %2 is %1",
            winapi::regSetValueEx(regHandle, 'TypeGuessRows', #REG_DWORD, newValue), newValue));
    regHandle = winapi::regOpenKey(#HKEY_LOCAL_MACHINE, #ExcelJetEnginePath, #KEY_QUERY_VALUE);
    if (regHandle)
        res = winapi::regGetValue(regHandle, 'TypeGuessRows');
    if (res)
        info(strFmt("New Value %1", con2str(res, '\n')));
}

[zinius]

В тему/не в тему но добавлю 5 копеек:
Бывало что выделении ячеек в столбце и присваивание им типа Text не помогало.
Лекарство - выделить весь столбец (по заголовку) и прописать ему Text.

[Gustav]

Цитата:

Сообщение от zinius

Бывало что выделении ячеек в столбце и присваивание им типа Text не помогало.
Лекарство - выделить весь столбец (по заголовку) и прописать ему Text.

Это при использовании ADO? Или при "иных удовольствиях"?
В случае ADO, если честно, не увидел разницы при установке формата "Text" для диапазона внутри столбца или всего столбца, выделяя щелчком по заголовку колонки Excel.

Но ваше лекарство подвигло меня дальнейшее изучение вопроса, и у меня получились некоторые доп.результаты, о которых я поведаю чуть позже, когда подготовлю наглядную табличку.

Пока же новая пилюля для Freeangel: достаточно проставить ведущие апострофы в первые 8 ячеек проблемной колонки, чтобы они распознавались ADO как текст.

Не обязательно во все 8, но так, чтобы текстовых было больше половины. Т.е. если у нас первые 3 ячейки - честный текст (т.е. с буквами), а следующие 5 ячеек - текст из цифр, то минимально надо добавить ведущий апостроф в две ячейки из пяти (чтобы текстовых ячеек стало 5 из 8 и они "победили" числа). Соответственно, в Registry ничего трогать не надо, но на всякий случай можно заглянуть туда и убедиться, что значение TypeGuessRows = 8.

Цитата:

Сообщение от Gustav

Пока же новая пилюля для Freeangel: достаточно проставить ведущие апострофы в первые 8 ячеек проблемной колонки, чтобы они распознавались ADO как текст.

Однако, я неправ...
Заставить-то мы заставили ADO считать наше поле текстовым... Но дело в том, что за пределами первых 8 ячеек все значения, состоящие из только цифр и в которые мы не проставили ведущий апостроф, превратились в пустые строки... Т.е. я "прав" на небольшом количестве ячеек (10-20), когда нетрудно проставить в них апострофы вручную. В 1000 же ячеек вручную апосторофы проставить проблематично... Ну что... чешем репу дальше...


Упс! Найдено новое средство (пилюлей уже боюсь называть, но, надеюсь, что это... победа...).

Допустим, наши данные для загрузки расположены на листе Sheet1 и проблемной является колонка A. Сделать надо следующее:

1. Скопировать всю проблемную колонку с данными на другой лист рабочей книги, например, Sheet2, для удобства - в те же ячейки, что и на листе Sheet1 (т.е. A2 в A2 и т.д.)
2. На листе Sheet1 заменить простые значения в проблемной колонке на формулы: в ячейку A2 ввести формулу =Sheet2!A2&"" , т.е. значение этой же ячейки с листа Sheet2, дополненное пустой строкой.
3. Скопировать формулу ячейки A2 на всю колонку на листе Sheet1.
4. Всё. Далее можно с листа Sheet1 читать данные при помощи ADO - вся колонка A корректно распознается как текст.

Если вас смущают формулы, то можно их заменить на значения при помощи Copy и PasteSpecial ("только значения"). Но будьте осторожны: дальнейшее редактирование какой-нибудь такой ячейки с текстом из цифр и последующее нажатие Enter приведет к превращению значения такой ячейки из текста в число и всё необходимо будет проделать сначала (хотя для одной ячейки как раз и можно воспользоваться ведущим апострофом ) .

P.S. Ну, и как обещал, на память выкладываю в файле свои изыскания, посвященные манипуляциям с форматами General/Text и апострофами. В процессе эксперимента я пользовался своим первым джобом из Поговорим об ADO. Прогнав его один раз и сгенерив эксельный файл, далее я комментировал первую часть джоба, посвященную экспорту, и запускал только импорт. Перед запуском импорта я терзал сгенеренный файл AdoTestBook.xls и смотрел, что затем попадает в инфолог Аксапты. Сделал я это, как видно из файла AdoResearch.xls, 9 раз ("шагов").

[gl00mie]

Цитата:

Сообщение от zinius

Бывало что выделении ячеек в столбце и присваивание им типа Text не помогало. Лекарство - выделить весь столбец (по заголовку) и прописать ему Text.

Microsoft пишет на эту тему следующее: «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». Насколько я понимаю, можно после форматирования скопировать ячейки в текстовый редактор, который "не воспринимает" форматирование (я обычно пользуюсь редактором Far Manager ), затем полученный текст скопировать в буфер обмена и вставить обратно в Excel.