Как прочитать данные из Excel через ADO в 1С и отобрать только заполненные строки?

← На главную

При интеграции 1С с внешними системами часто возникает задача импорта данных из файлов Microsoft Excel. Использование технологии ADO (ActiveX Data Objects) является одним из наиболее производительных и надежных способов решения этой задачи. Этот метод работает значительно быстрее, чем запуск COM-объекта Excel, и не требует установки самого пакета MS Office на сервере, где выполняется код 1С.

Однако при составлении SQL-запросов к файлам Excel разработчики часто сталкиваются со специфическими ошибками синтаксиса СУБД. В этой статье мы подробно разберем, почему стандартные функции полнотекстового поиска (такие как CONTAINS) не работают при чтении Excel через ADO, и рассмотрим правильные, производительные способы отбора только заполненных строк.

Выясняем причину ошибки: почему не работает CONTAINS

В исходном запросе была сделана попытка использовать следующую конструкцию:

SELECT * FROM [A8:BB] WHERE contains([F8],'?')

При выполнении этого кода система возвращает ошибку синтаксиса: "Ошибка синтаксиса (пропущен оператор) в выражении запроса...".

Проанализируем причину ее возникновения. Функция CONTAINS является специализированным предикатом полнотекстового поиска, который используется в «тяжелых» СУБД вроде Microsoft SQL Server. Драйвер Microsoft ACE OLEDB (или его предшественник Jet OLEDB), который используется для подключения к файлам Excel, не поддерживает полнотекстовый поиск. Синтаксис SQL для файлов Excel базируется на упрощенном диалекте Access SQL, где функция CONTAINS попросту отсутствует.

Также обратите внимание на именование колонок. Поскольку в строке подключения указано свойство HDR=NO, первая строка файла не воспринимается драйвером как заголовок. В этом случае драйвер автоматически присваивает колонкам системные имена по шаблону F1, F2, F3 и так далее. В нашем примере фильтрация происходит по колонке номер 8, то есть [F8].

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

Решение №1: Использование проверки на NULL и пустую строку

В базах данных и при чтении таблиц Excel через ADO пустая ячейка чаще всего интерпретируется как значение NULL (отсутствие значения), а не как пустая строка ''. Простая проверка на неравенство пустой строке не даст нужного результата, если ячейка действительно пуста.

Наиболее надежный способ — это комбинированная проверка на значение NULL и на отсутствие символов пробелов с помощью функции TRIM. Разберем этот синтаксис:


ТекстЗапроса = "SELECT * FROM [A" + Объект.ПерваяИнформационнаяСтрокаЭкселя + ":BB] 
               |WHERE ([F8] IS NOT NULL) AND (TRIM([F8]) <> '')";

В данном условии:

Конструкция [F8] IS NOT NULL отсекает ячейки, которые физически не содержат данных и возвращают неопределенное состояние.
Конструкция TRIM([F8]) <> '' удаляет все ведущие и замыкающие пробелы из значения ячейки и проверяет, осталось ли в ней хоть что-то. Это позволяет отсеять строки, которые визуально кажутся пустыми, но на самом деле содержат один или несколько пробелов.

Решение №2: Проверка длины строки через функцию LEN

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


ТекстЗапроса = "SELECT * FROM [A" + Объект.ПерваяИнформационнаяСтрокаЭкселя + ":BB] 
               |WHERE LEN(TRIM([F8])) > 0";

Этот способ компактен и отлично поддерживается движком ACE OLEDB. Запрос вернет только те строки, в которых длина строкового представления значения в колонке F8 после удаления пробелов строго больше нуля.

Решение №3: Правильное использование оператора LIKE

В ходе обсуждений на форумах часто предлагают использовать оператор LIKE с шаблонами поиска. Однако здесь кроется важная деталь. При работе через ADO драйвер ACE/Jet использует стандарт ANSI-92. В этом режиме привычные для Microsoft Access спецсимволы маски * (любое количество символов) и ? (один символ) не работают.

Вместо них необходимо использовать стандартные символы подстановки SQL:

% — заменяет любое количество произвольных символов (аналог *).
_ — заменяет ровно один произвольный символ (аналог ?).

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


ТекстЗапроса = "SELECT * FROM [A" + Объект.ПерваяИнформационнаяСтрокаЭкселя + ":BB] 
               |WHERE [F8] LIKE '%'";

Этот запрос отберет все строки, где колонка F8 содержит хотя бы один символ. Тем не менее, использование LIKE считается менее производительным по сравнению с прямой проверкой IS NOT NULL или вычислением LEN, поэтому рекомендуется использовать первые два решения.

Решение №4: Безопасное приведение типов данных

Иногда файлы Excel содержат разнородные данные. В одной и той же колонке могут находиться как текстовые значения, так и числа или даты. При применении строковых функций (таких как TRIM или LEN) к чисто числовым ячейкам драйвер ADO может выдать ошибку несоответствия типов данных.

Чтобы обезопасить выполнение запроса, мы можем принудительно приводить значения к строковому типу с помощью функции CStr и использовать условную функцию IIF для обработки значений NULL:


ТекстЗапроса = "SELECT * FROM [A" + Объект.ПерваяИнформационнаяСтрокаЭкселя + ":BB] 
               |WHERE LEN(TRIM(IIF([F8] IS NULL, '', CStr([F8])))) > 0";

Давайте подробно разберем по шагам, как работает эта вложенная конструкция:

Функция IIF([F8] IS NULL, '', CStr([F8])) проверяет ячейку на равенство NULL. Если ячейка пуста, она возвращает пустую строку ''. Если ячейка заполнена, значение принудительно преобразуется в строку с помощью функции CStr.
Функция TRIM удаляет лишние пробелы из полученного строкового значения.
Функция LEN вычисляет длину итоговой строки, а условие > 0 отсекает все пустые строки.

Этот подход является наиболее защищенным от сбоев, связанных со спецификой заполнения исходного файла Excel пользователями.

Влияние параметра IMEX=1 на чтение пустых ячеек

При настройке подключения через ADO крайне важен параметр IMEX=1 в строке соединения (Intermixed columns). Он указывает драйверу, что при наличии разнородных типов данных в одной колонке их все необходимо считывать как текстовые строки.

Однако помните о важной особенности работы Microsoft ACE OLEDB. Драйвер определяет тип данных в колонке на основе анализа первых строк таблицы. Количество анализируемых строк задается системным параметром реестра Windows TypeGuessRows (по умолчанию анализируется первые 8 строк). Если в вашем файле в колонке F8 первые 8 строк окажутся абсолютно пустыми, драйвер может неверно определить тип данных для всей колонки и в последующих строках возвращать значение NULL, даже если там будет текст. По возможности следите за тем, чтобы импортируемый файл содержал заполненные данные в первой же строке области чтения.

Пример готовой функции на платформе 1С

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


&НаСервере
Процедура ПрочитатьФайлExcelЧерезADO(ИмяФайла)

    СтрПодключения = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + ИмяФайла + "; Extended Properties = ""Excel 12.0;HDR=NO;IMEX=1"";";
    
    Connection = Новый COMОбъект("ADODB.Connection");
    Попытка
        Connection.Open(СтрПодключения);
    Исключение
        ЗаписьЖурналаРегистрации("Импорт Excel", УровеньЖурналаРегистрации.Ошибка, , , "Ошибка подключения к файлу через ADO: " + ОписаниеОшибки());
        ВызватьИсключение "Не удалось подключиться к файлу Excel. Возможно, он заблокирован другим процессом.";
    КонецПопытки;
    
    НаборДанных = Новый COMОбъект("ADODB.RecordSet");
    
    // Формируем безопасный запрос с фильтрацией пустых строк по колонке F8
    ТекстЗапроса = "SELECT * FROM [Лист1$] WHERE LEN(TRIM(IIF([F8] IS NULL, '', CStr([F8])))) > 0";
    
    Попытка
        НаборДанных.Open(ТекстЗапроса, Connection);
    Исключение
        Connection.Close();
        ЗаписьЖурналаРегистрации("Импорт Excel", УровеньЖурналаРегистрации.Ошибка, , , "Ошибка выполнения SQL-запроса: " + ОписаниеОшибки());
        ВызватьИсключение "Ошибка при чтении данных из листа Excel. Проверьте структуру файла.";
    КонецПопытки;
    
    // Создаем таблицу значений для выгрузки результатов
    ТаблицаРезультат = Новый ТаблицаЗначений;
    // Создаем колонки динамически на основе полей рекордсета
    Для НомерПоля = 0 По НаборДанных.Fields.Count - 1 Цикл
        ИмяПоля = НаборДанных.Fields.Item(НомерПоля).Name;
        ТаблицаРезультат.Колонки.Добавить(ИмяПоля);
    КонецЦикла;
    
    // Обходим выборку и заполняем таблицу значений
    Пока НЕ НаборДанных.EOF() Цикл
        НоваяСтрока = ТаблицаРезультат.Добавить();
        Для НомерПоля = 0 По НаборДанных.Fields.Count - 1 Цикл
            ИмяПоля = НаборДанных.Fields.Item(НомерПоля).Name;
            НоваяСтрока[ИмяПоля] = НаборДанных.Fields.Item(НомерПоля).Value;
        КонецЦикла;
        НаборДанных.MoveNext();
    КонецЦикла;
    
    // Обязательно закрываем соединения после работы
    НаборДанных.Close();
    Connection.Close();
    
    // Дальнейшая обработка полученных данных
    // ...
    
КонецПроцедуры

В этом примере мы использовали имя листа по умолчанию [Лист1$]. Если в вашем коде используется явное указание диапазона ячеек (например, [A8:BB]), убедитесь, что имя листа или диапазона указано корректно и соответствует структуре вашего файла Excel.

Применение рассмотренных методов фильтрации данных непосредственно на уровне SQL-запроса существенно снизит нагрузку на оперативную память сервера 1С, поскольку в систему будут передаваться только те строки, которые действительно содержат полезную информацию и готовы к обработке.