Как исправить ошибку считывания даты при загрузке данных из Excel через ADODB в 1С?

← На главную

При использовании технологии ADODB.Connection для загрузки данных из внешних файлов Excel на сервере 1С нередко возникает проблема: колонки с датами перестают корректно распознаваться. Вместо значения типа Дата COM-объект ADODB.Recordset может возвращать текстовую строку вида "2016-12-29 :" или значения Null. В результате в создаваемых документах 1С реквизиты даты остаются незаполненными.

Рассмотрим подробно причины возникновения этой проблемы и разберем варианты её решения как средствами платформы 1С, так и настройками OLE DB драйвера.

Выясним причину сбоя при чтении данных

Проанализируем, почему драйвер OLE DB (Microsoft JET или Access Database Engine) начинает считывать даты как текст. При работе через ADODB.Connection драйвер автоматически определяет тип данных в колонке на основе анализа первых нескольких строк (по умолчанию 8 строк). Этот механизм регулируется системным параметром TypeGuessRows.

Если в первых строках файла находились корректные даты, драйвер определяет весь столбец как «Дата». Однако если файл формируется сторонней системой и содержит форматированный заголовок, спецсимволы (например, двоеточие после даты) или смешанные типы данных, происходит следующее:

Драйвер считывает значение колонки как текстовое (WSTR), сохраняя все служебные символы исходного файла.
Если параметр IMEX в строке подключения не настроен, значения, не соответствующие преобладающему типу, могут заменяться на Null.

Ниже представлены проверенные способы решения этой проблемы.

Решение 1: Обработка текстовой строки на стороне 1С

Если драйвер ADODB возвращает значение даты в виде строки (например, "2016-12-29 :"), наиболее простым и надежным решением на стороне 1С является выделение подстроки с датой и её преобразование в стандартный тип Дата.

Разберем по шагам создание функции-обработчика:

Обрезаем из полученной строки первые 10 символов, содержащие год, месяц и день.
Удаляем из строки лишние символы разделителей (дефисы, точки, пробелы).
Формируем дату с помощью функции Дата() из полученной строки формата "ГГГГММДД".

Посмотрим на пример функции:


Функция ДатаИзСтроки(СтрокаДаты) Экспорт
    
    // Получаем первые 10 символов (ГГГГ-ММ-ДД)
    СтрДата = Лев(СокрЛП(СтрокаДаты), 10);
    
    // Удаляем разделители
    СтрДата = СтрЗаменить(СтрДата, ".", "");
    СтрДата = СтрЗаменить(СтрДата, "-", "");
    СтрДата = СокрЛП(СтрДата);
    
    Попытка 
        // Преобразуем составную строку ГГГГММДД в тип Дата
        Возврат Дата(Лев(СтрДата, 4) + Сред(СтрДата, 5, 2) + Прав(СтрДата, 2));
    Исключение
        // В случае ошибки возвращаем пустую дату
        Возврат '00010101';
    КонецПопытки;    
    
КонецФункции

Эту функцию следует вызывать в моменте обхода записей ADODB.RecordSet при заполнении реквизитов объектов 1С.

Решение 2: Корректировка параметров строки подключения ADODB

Чтобы OLE DB драйвер не терял данные из колонок со смешанным содержимым, проанализируем настройки строки подключения. В параметре Extended Properties необходимо явно указать режим IMEX=1 (Import Mode).

Режим IMEX=1 заставляет драйвер принудительно считывать колонки со смешанными типами данных как текст. Это гарантирует, что 1С получит все значения без искажения и превращения в Null, после чего их можно будет корректно распарсить.

Пример строки подключения для файлов .xlsx:


СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 XML;HDR=YES;IMEX=1;"";";
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open(СтрокаПодключения);

Пример строки подключения для файлов .xls:


СтрокаПодключения = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;"";";

Решение 3: Изменение параметра TypeGuessRows в реестре Windows

Если требуется, чтобы OLE DB драйвер сканировал большее количество строк файла перед определением типа колонки, можно изменить параметр реестра на сервере 1С.

Откроем редактор реестра regedit на сервере.
Перейдем в ветку реестра для используемого драйвера:
- Для 64-разрядного Access Database Engine: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\[Версия]\Access Connectivity Engine\Engines\Excel
- Для 32-разрядного драйвера: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\[Версия]\Access Connectivity Engine\Engines\Excel
Найдем параметр TypeGuessRows и установим его значение равным 0.

Установка значения 0 заставляет драйвер анализировать до 16 384 строк файла перед выбором типа данных колонки. Обратите внимание: это может снизить скорость выполнения запроса к большим файлам Excel.

Альтернатива: Чтение Excel без использования ADODB и COM

Чтобы избежать проблем с OLE DB драйверами, разрядностями систем (32/64 bit) и отсутствием Microsoft Excel на сервере, в современных версиях платформы 1С:Предприятие 8.3 рекомендуется использовать штатный объект ТабличныйДокумент.

Преимущества метода:

Работает на кроссплатформенной основе (Windows и Linux).
Не требует установки внешних драйверов OLE DB или пакета MS Office.
Исключает проблемы с блокировкой файлов и утечкой ресурсов COM-объектов.

Рассмотрим пример загрузки файла Excel через ТабличныйДокумент с последующей конвертацией в ТаблицуЗначений:


ТабДок = Новый ТабличныйДокумент;

Попытка
    // Чтение файла xls или xlsx напрямую платформой 1С
    ТабДок.Прочитать(ПутьКФайлу);
Исключение
    ЗаписьЖурналаРегистрации("Загрузка Excel", УровеньЖурналаРегистрации.Ошибка,,, ОписаниеОшибки());
    Возврат;
КонецПопытки;

// Преобразование области табличного документа в Таблицу Значений
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область());
ТЗ = Построитель.Результат.Выгрузить();

// Обход полученных данных
Для Каждого Стр Из ТЗ Цикл
    // Работа с колонками таблицы значений
КонецЦикла;

Использование штатного метода ТабличныйДокумент.Прочитать() позволяет безопасно и быстро получать данные из файлов Excel в регламентных заданиях без зависимости от сторонних драйверов Windows.