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

← На главную

При организации автоматической фоновой загрузки данных из файлов Excel без установленного пакета Microsoft Office на сервере часто используется технология ADO (ADODB.Connection / ADODB.Recordset). Однако разработчики нередко сталкиваются с ситуацией, когда колонка с датой внезапно перестает считываться как дата и возвращается в виде строки с нестандартным форматом (например, "2016-12-29 :"), из-за чего реквизиты документов 1С остаются пустыми.

Разберем подробно, почему возникает такое поведение драйверов OLEDB и какими способами можно гарантированно получить корректную дату и время в базе 1С.

Причины сбоя: механизм Type Guessing в ADO

Проанализируем механику работы драйверов Microsoft Jet и ACE OLEDB при обращении к листам Excel:

Эвристическое определение типов данных (Type Guessing): драйвер не сканирует всю таблицу целиком. По умолчанию он анализирует только первые 8 строк (параметр TypeGuessRows в реестре Windows). Если в первых строках колонка пустая, содержит заголовок или формат отличается от последующих строк, драйвер определяет для всей колонки строковый тип данных.
Влияние параметра IMEX: если в строке подключения не задан режим импорта смешанных типов, драйвер пытается привести данные к преобладающему типу, что может приводить к искажению формата ячеек или возврату значений NULL.

Способ 1. Программное преобразование строки в дату средствами 1С

Если драйвер возвращает дату в виде строки, наиболее надежный и быстрый способ на стороне 1С — выполнить явное приведение строкового значения к типу Дата перед записью в документ.

Посмотрим на пример универсального разбора строки, очистки от лишних символов (например, двоеточия на конце) и формирования корректной даты:


// Функция преобразует строку вида "2016-12-29 :" или "2016-12-29 14:30:00" в тип Дата 1С
Функция ПреобразоватьСтрокуВДату(Знач ИсходнаяСтрока)
    
    // Очищаем строку от пробелов по краям
    СтрокаОчищенная = СокрЛП(ИсходнаяСтрока);
    
    // Если на конце остался символ двоеточия или разделителя, удаляем его
    Пока Прав(СтрокаОчищенная, 1) = ":" Или Прав(СтрокаОчищенная, 1) = " " Цикл
        СтрокаОчищенная = СокрЛП(Лев(СтрокаОчищенная, СтрДлина(СтрокаОчищенная) - 1));
    КонецЦикла;
    
    Если ПустаяСтрока(СтрокаОчищенная) Тогда
        Возврат '00010101';
    КонецЕсли;
    
    // Проверяем формат ГГГГ-ММ-ДД
    Если СтрДлина(СтрокаОчищенная) >= 10 И Сред(СтрокаОчищенная, 5, 1) = "-" И Сред(СтрокаОчищенная, 8, 1) = "-" Тогда
        Год   = Лев(СтрокаОчищенная, 4);
        Месяц = Сред(СтрокаОчищенная, 6, 2);
        День  = Сред(СтрокаОчищенная, 9, 2);
        
        ВремяСтр = "000000";
        // Если в строке также передано время
        Если СтрДлина(СтрокаОчищенная) > 10 Тогда
            ОстатокВремени = СокрЛП(Сред(СтрокаОчищенная, 11));
            ОстатокВремени = СтрЗаменить(ОстатокВремени, ":", "");
            ОстатокВремени = СтрЗаменить(ОстатокВремени, " ", "");
            ВремяСтр = Лев(ОстатокВремени + "000000", 6);
        КонецЕсли;
        
        Попытка
            Возврат Дата(Год + Месяц + День + ВремяСтр);
        Исключение
            Возврат '00010101';
        КонецПопытки;
    КонецЕсли;
    
    Возврат '00010101';
КонецФункции

Если в конфигурации используется Библиотека Стандартных Подсистем (БСП), можно воспользоваться готовыми функциями разбора строковых представлений:

ДатаЗначение = СтроковыеФункцииКлиентСервер.СтрокаВДату(СтрокаИзЭкселя);

Способ 2. Корректировка параметров строки подключения ADO

Рассмотрим, как правильно настроить строку подключения к Excel, чтобы предотвратить появление значений NULL при смешанных типах данных. Для этого в свойствах подключения Extended Properties необходимо явно указать параметры HDR=YES и IMEX=1.

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


ПараметрыПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                       "Data Source=" + ПолноеИмяФайла + ";" +
                       "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"";";

Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open(ПараметрыПодключения);

Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandText = "SELECT * FROM [Лист1$]";

НаборЗаписей = Команда.Execute();

Пока НЕ НаборЗаписей.EOF() Цикл
    ЗначениеКолонки = НаборЗаписей.Fields("ДатаОперации").Value;
    // При IMEX=1 значение гарантированно вернется как строка или дата, без потери данных
    ДатаДляДокумента = ПреобразоватьСтрокуВДату(ЗначениеКолонки);
    
    НаборЗаписей.MoveNext();
КонецЦикла;

Соединение.Close();

Обратите внимание: режим IMEX=1 заставляет драйвер импортировать столбцы со смешанными данными в строковом виде, что исключает сбои чтения и позволяет обработать строковое значение алгоритмом в 1С.

Способ 3. Чтение файла через встроенный ТабличныйДокумент 1С

В современных версиях платформы 1С:Предприятие 8.3 загрузку файлов Excel на сервере можно выполнять штатными средствами без COM-объектов и установки внешних библиотек (в том числе под управлением Linux).

Разберем по шагам, как прочитать файл с помощью объекта ТабличныйДокумент:


ТабДок = Новый ТабличныйДокумент;
// Читаем файл с диска
ТабДок.Прочитать(ПолноеИмяФайла, СпособЧтенияЗначенийТабличногоДокумента.Текст);

ВсегоСтрок = ТабДок.ВысотаТаблицы;
ВсегоКолонок = ТабДок.ШиринаТаблицы;

Для НомерСтроки = 2 По ВсегоСтрок Цикл
    // Считываем текстовое представление ячейки с датой
    ТекстЯчейки = ТабДок.Область(НомерСтроки, ВсегоКолонок).Текст;
    
    ДатаДокумента = ПреобразоватьСтрокуВДату(ТекстЯчейки);
    
    // Заполняем реквизиты объекта или строки таблицы
КонецЦикла;

Данный подход избавляет от зависимости от установленных на сервере драйверов OLEDB и специфики их версий разрядности (32/64 bit).