При интеграции данных из внешних источников, таких как Excel, программисты 1С часто сталкиваются с ситуацией, когда визуально одинаковые значения (например, коды товаров или артикулы) не находят соответствий в базе данных. Вы пишете запрос, передаете в него список значений, полученных из файла, но результат запроса остается пустым. В этой статье мы подробно разберем, почему так происходит, и пройдем по шагам процесс очистки и подготовки данных для гарантированного сопоставления.
Рассмотрим типичную ситуацию. У нас есть непериодический независимый РегистрСведений. Мы считываем колонку из Excel, помещаем данные в СписокЗначений и передаем его в запрос как параметр с условием ГДЕ Код В (&Список). Несмотря на то что коды в файле и в регистре выглядят идентично, 1С не находит совпадений.
Проанализируем ситуацию: проблема почти всегда кроется в "мусорных" символах или различии в типах данных. То, что мы видим в ячейке Excel, — это лишь отображение, которое может сильно отличаться от того, что реально хранится в памяти и передается в 1С.
Самый коварный враг при загрузке строк из Excel — это неразрывный пробел (символ с кодом 160). Обычная функция СокрЛП() удаляет только стандартные пробелы (код 32). Если в строке есть неразрывные пробелы, они останутся на месте, и запрос не сработает.
Давайте разберем, как эффективно очистить строку от лишних символов. Посмотрим на пример кода:
// Очищаем строку от неразрывных пробелов и обычных пробелов
ЧистыйКод = СтрЗаменить(КодИзЭкселя, Символ(160), "");
ЧистыйКод = СокрЛП(ЧистыйКод);
// Дополнительно можно убрать все пробелы внутри строки, если это допустимо для вашего формата
ЧистыйКод = СтрЗаменить(ЧистыйКод, " ", "");
Помимо кода 160, в данных могут встречаться символы табуляции (код 9), перевода строки (код 10) или возврата каретки (код 13). Если вы хотите быть абсолютно уверены в чистоте данных, стоит написать функцию, которая оставляет в строке только разрешенные символы (например, цифры и буквы).
Разберем технический нюанс работы с объектом Excel.Application. Существует два способа получить значение ячейки:
Cells(x, y).Value — возвращает фактическое значение. Если в ячейке число, 1С получит тип Число.Cells(x, y).Text — возвращает строку в том виде, в котором она отображается в ячейке.Если код в Excel длинный (более 11 знаков), программа часто отображает его в экспоненциальном виде (например, 1.23E+11). Если вы используете свойство .Text, вы передадите в 1С именно эту искаженную строку. Мы рекомендуем использовать .Value и программно приводить его к нужному типу в 1С.
Проанализируем структуру вашего регистра. В 1С для типа Строка есть настройка "Фиксированная длина". Если в конфигураторе для поля "Код" установлена длина 20 и снят флаг "Переменная длина", то 1С всегда будет дополнять значение пробелами справа.
Пример: Если в базе хранится код "123", а длина поля — 10 символов, фактически в таблице SQL записано "123 ". Если вы передадите в запрос параметр "123" (без пробелов), условие Код = &Параметр вернет Ложь.
Чтобы решить эту проблему, мы можем:
Формат() для дополнения строки пробелами до нужной длины.ПОДОБНО, хотя это может замедлить работу.Часто коды в системах учета имеют вид "000123". При открытии такого файла в Excel программа может автоматически определить колонку как числовую и обрезать лидирующие нули, оставив просто "123".
Рассмотрим подробнее, как восстановить такие данные. Проанализируем ситуацию: если мы знаем, что код в 1С всегда должен состоять из 6 знаков, мы можем использовать функцию Прав():
// Дополняем код нулями слева до 6 символов
КодДляЗапроса = Прав("000000" + СокрЛП(ЗначениеИзЭкселя), 6);
Если же поле в 1С числовое, а из Excel пришло число с разделителями групп разрядов (например, "1 234"), при преобразовании в строку в 1С могут появиться пробелы. Чтобы их избежать, используйте форматную строку:
// Преобразуем число в строку без группировки разрядов
СтрокаБезПробелов = Формат(ЧислоИзЭкселя, "ЧГ=0");
Передача большого списка значений в параметр &Список — не самый лучший вариант с точки зрения производительности. Если в вашем Excel-файле тысячи строк, SQL-сервер может столкнуться с ограничениями на размер условия WHERE IN (...).
Посмотрим на более профессиональный подход. Мы рекомендуем загружать данные из Excel сначала в ТаблицаЗначений, а затем передавать эту таблицу в запрос для создания Временной таблицы.
Разберем этот метод по шагам:
// 1. Создаем Таблицу Значений с нужной типизацией
ТЗДанных = Новый ТаблицаЗначений;
ТЗДанных.Колонки.Добавить("Код", Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(20)));
// 2. Заполняем ТЗ данными из Excel, очищая их
Для Каждого СтрокаЭксель Из ДанныеФайла Цикл
НоваяСтр = ТЗДанных.Добавить();
НоваяСтр.Код = СтрЗаменить(СтрокаЭксель.Код, Символ(160), "");
НоваяСтр.Код = СокрЛП(НоваяСтр.Код);
КонецЦикла;
// 3. Передаем ТЗ в запрос
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
Запрос.Текст =
"ВЫБРАТЬ
| ВТ_Коды.Код КАК КодИзФайла
|ПОМЕСТИТЬ ВТ_ДанныеExcel
|ИЗ
| &ТЗ КАК ВТ_Коды
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Регистр.Код,
| Регистр.Значение
|ИЗ
| РегистрСведений.ВашРегистр КАК Регистр
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДанныеExcel КАК ВТ_Excel
| ПО Регистр.Код = ВТ_Excel.КодИзФайла";
Запрос.УстановитьПараметр("ТЗ", ТЗДанных);
Результат = Запрос.Выполнить();
Использование Внутреннего соединения (INNER JOIN) с временной таблицей работает значительно быстрее и надежнее, чем поиск по списку значений. Это также позволяет избежать проблем с типизацией, так как вы явно описываете типы колонок во временной таблице.
Подведем итоги нашего исследования. Чтобы успешно сопоставить данные из Excel с регистром 1С, необходимо:
.Value для получения данных и Формат(Значение, "ЧГ=0") для удаления разделителей разрядов.Следуя этим правилам, вы забудете о проблеме "пустых результатов" при загрузке данных и сможете создавать надежные механизмы интеграции.