При импорте больших объемов данных из электронных таблиц Microsoft Excel в информационные базы 1С разработчики часто выбирают технологию ADO (ActiveX Data Objects). Данный подход работает в разы быстрее, чем прямое обращение к файлу через COM-объект Excel.Application, поскольку книга Excel считывается напрямую через ядро базы данных как плоская таблица.
Однако при попытке прочитать «широкие» таблицы, содержащие большое количество столбцов, разработчики неизбежно сталкиваются со следующей ошибкой: «Microsoft JET Database Engine: Определено слишком много полей». Программа при этом отказывается считывать данные или обрезает результат работы на 255-й колонке. В этой статье мы подробно разберем, почему возникает это ограничение, как его обойти средствами ADO и какие существуют альтернативные способы решения данной задачи.
Архитектурный лимит в 255 колонок связан не с самой платформой 1С или программой Excel, а со спецификой работы драйверов Microsoft. Когда мы подключаемся к файлу Excel через ADO, провайдер данных (например, Microsoft.Jet.OLEDB.4.0 или более современный Microsoft.ACE.OLEDB.12.0) эмулирует работу реляционной СУБД Microsoft Access.
В движках баз данных Microsoft Jet и Access Connectivity Engine (ACE) существует жесткое системное ограничение: одна таблица или один запрос не могут содержать более 255 полей. Поскольку лист Excel интерпретируется драйвером как стандартная таблица базы данных, любая попытка выполнить классический запрос SELECT * FROM [Лист1$] к документу, имеющему более 255 колонок, приведет к ошибке переполнения структуры полей.
---
Обойти лимит в 255 колонок в рамках одного запроса (Recordset) невозможно. Однако мы можем прочитать данные за несколько проходов, запрашивая столбцы пакетами (частями), а затем объединяя полученные строки в памяти — например, в объекте ТаблицаЗначений.
Для реализации этого метода нам нужно разделить всю ширину листа на непересекающиеся диапазоны. Каждая порция колонок не должна превышать размер в 255 столбцов.
Рассмотрим разбивку колонок на практике:
A до колонки IU (включительно).IV до колонки SP (включительно).Проанализируем пример кода на языке 1С, который последовательно считывает данные из двух диапазонов и объединяет их в единую структуру:
// Шаг 1. Настройка строки подключения.
// Обязательно используем современный провайдер ACE OLEDB для работы с форматом Excel 2007+ (.xlsx)
ПутьКФайлу = "C:\Data\LargeTable.xlsx";
ИмяЛиста = "Лист1";
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 XML;HDR=NO;IMEX=1"";";
ADODBConnection = СоздатьОбъект("ADODB.Connection");
ADODBConnection.CursorLocation = 3;
ADODBConnection.ConnectionString = ConnectionString;
ADODBConnection.Open();
// Шаг 2. Чтение первого пакета данных (колонки 1 - 255)
ADODBCommand = СоздатьОбъект("ADODB.Command");
ADODBCommand.ActiveConnection = ADODBConnection;
ADODBCommand.CommandType = 1;
// Указываем диапазон колонок целиком (A:IU) без жесткой привязки к строкам
ADODBCommand.CommandText = "SELECT * FROM [" + ИмяЛиста + "$A:IU]";
RecordsetPart1 = ADODBCommand.Execute();
// Шаг 3. Чтение второго пакета данных (колонки 256 - 510)
ADODBCommand2 = СоздатьОбъект("ADODB.Command");
ADODBCommand2.ActiveConnection = ADODBConnection;
ADODBCommand2.CommandType = 1;
// Указываем диапазон следующих колонок (IV:SP)
ADODBCommand2.CommandText = "SELECT * FROM [" + ИмяЛиста + "$IV:SP]";
RecordsetPart2 = ADODBCommand2.Execute();
// Теперь в коллекциях RecordsetPart1 и RecordsetPart2 содержатся строки,
// которые можно последовательно перебрать в цикле и соединить в одну Таблицу Значений 1С.
Важное примечание по синтаксису: Обратите внимание на то, что в тексте запроса диапазоны колонок указываются в формате A:IU (без указания номеров строк). Если вы попытаетесь использовать синтаксис вида IU1:PK1000, драйвер ACE OLEDB может выдать ошибку «Объект не найден ядром СУБД...» в случаях, когда реальное заполнение листа строками не совпадает с запрашиваемым интервалом.
---
Если вы применили схему с разделением запросов на диапазоны, но все равно столкнулись с ошибкой отсутствия объекта базы данных на втором шаге, необходимо проверить два ключевых момента:
.xls, который в свойствах подключения обозначается как Excel 8.0) физически поддерживает максимум 256 колонок (последняя колонка имеет индекс IV). Обращение к более широким диапазонам (например, к колонке PK) в файле формата .xls вызовет сбой, поскольку этих колонок в структуре файла физически не существует. Чтобы решить проблему, сохраните файл в формате .xlsx (Excel 12.0) и измените строку подключения.PK), драйвер может посчитать запрашиваемый диапазон некорректным или полностью пустым, выдав ошибку отсутствия объекта.---
При импорте больших таблиц через ADO разработчики часто сталкиваются с тем, что длинные текстовые ячейки при чтении обрезаются до 255 символов. Это происходит из-за того, что OLEDB-провайдер принудительно анализирует только первые 8 строк таблицы (поведение по умолчанию) для определения типов данных в колонках. Если в этих первых строках не нашлось текста длиннее 255 символов, колонке назначается стандартный тип VarChar (строка ограниченной длины), а последующие длинные тексты нещадно усекаются.
Для исправления этой проблемы необходимо предпринять два шага:
IMEX=1 (это указывает драйверу использовать смешанный режим чтения данных).TypeGuessRows на значение 0. При значении 0 драйвер будет сканировать весь лист полностью для корректного определения типов колонок (длинные строки определятся как тип Memo и не будут обрезаться).Путь к параметру в реестре для провайдера ACE 12.0:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
Примечание: для 32-битных приложений, работающих на 64-битной версии операционной системы, данный параметр следует искать в ветке Wow6432Node.
---
Если решение задачи через ADO кажется вам излишне сложным из-за необходимости постоянно пересчитывать буквенные индексы колонок и объединять рекордсеты, рассмотрим альтернативные варианты импорта файлов Excel.
Это классический способ работы с Excel в среде Windows. Он не имеет никаких ограничений на количество считываемых колонок или длину текстовых строк.
Попытка
Эксель = СоздатьОбъект("Excel.Application");
Исключение
Предупреждение("На компьютере не установлен MS Excel!");
Возврат;
КонецПопытки;
Книга = Эксель.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Worksheets(1);
// Читаем значение из 300-й колонки 5-й строки напрямую без ограничений
ЗначениеЯчейки = Лист.Cells(5, 300).Value;
Книга.Close(0);
Эксель.Quit();
Минусы: Крайне низкая скорость работы на больших файлах. Требует обязательной установки пакета Microsoft Office на компьютере, где выполняется код.
Для пользователей платформы 1С v7.7 отличной альтернативой является бесплатная компонента Йоксель. Она написана специально для прямого чтения бинарной структуры файлов Excel на уровне ядра платформы.
Работа с компонентой происходит очень быстро, она полностью независима от установленного MS Office и без проблем считывает неограниченное количество колонок.
Если вы работаете на платформе 1С v8.3, самым оптимальным, быстрым и надежным решением является загрузка файла через встроенный объект платформы ТабличныйДокумент.
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
ВызватьИсключение "Не удалось прочитать файл! Ошибка: " + ОписаниеОшибки();
КонецПопытки;
// Табличный документ прочитан в память платформы нативно.
// Мы можем обращаться к любым колонкам (хоть к 500-й) без ограничений по скорости:
ШиринаТаблицы = ТабДок.ШиринаТаблицы;
Для Стр = 1 По ТабДок.ВысотаТаблицы Цикл
Значение = ТабДок.Область(Стр, 300).Текст;
КонецЦикла;
Данный метод работает чрезвычайно быстро как на клиенте, так и на сервере (в том числе под управлением Linux), не требует сторонних библиотек и легко справляется с любым количеством колонок.