Загрузка номенклатуры из Excel без мусора в справочнике: девять проверок до записи

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

Почти все такие истории начинаются с одного решения: писать в базу сразу, а ошибки искать потом. Ниже разберу другой порядок, при котором файл сначала целиком проверяется, пользователь видит таблицу с номерами строк Excel и текстом ошибки, и только после этого в базу уходят строки без ошибок. Фрагменты кода взяты из внешней обработки для УНФ 3.0 (БСП 3.1), но подход переносится на любую конфигурацию, где есть справочник номенклатуры, классификатор единиц и регистр штрихкодов.

Откуда берется мусор

Ошибки в таких файлах однотипные. Колонки стоят в другом порядке, чем в прошлый раз, или называются "Ед." вместо "Единица измерения". Штрихкод записан числом, и Excel показывает его как 4,60123E+12, а при чтении через табличный документ можно получить строку с пробелами между разрядами. Единица написана словом "штуки", которого нет в классификаторе. Ставка НДС указана как 18%, потому что шаблон файла не обновляли много лет. Один и тот же артикул встречается дважды, потому что файл собирали из двух прайсов.

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

Общая схема: проверка и запись раздельно

Обработка работает в два прохода.

1. Пользователь выбирает файл. Обработка читает его в таблицу значений, проверяет каждую строку и показывает результат: статус "Новый", "Обновить" или "Ошибка", номер строки Excel и текст ошибки. В базу на этом шаге не пишется ничего.
2. По кнопке "Загрузить" строки еще раз проверяются (пока пользователь смотрел на таблицу, в базе могли что-то поменять), и записываются только строки без ошибок. Каждая строка пишется в своей транзакции, поэтому ошибка в одной строке не откатывает остальные.

Товар ищется по артикулу. Нашелся, значит обновляется, не нашелся, значит создается. Благодаря этому повторная загрузка того же файла не плодит дубли, а исправленный файл можно просто загрузить еще раз.

Обе проверки вызывают одну и ту же функцию КонтекстПроверки. Это важно: если логика проверки и логика записи живут в разных местах, рано или поздно они разъедутся, и в базу попадет строка, которую проверка пропустила бы.

Колонки по заголовкам, а не по номерам

Первое правило: никогда не привязываться к номеру колонки. Первая строка файла считается шапкой, а заголовок приводится к ключу без регистра, пробелов, точек, дефисов и звездочек. Так "Ед. изм.", "ед изм" и "Ед.изм" дают один и тот же ключ.

Функция КлючЗаголовка(Знач Текст)
	Текст = НРег(СокрЛП(Текст));
	Для Каждого Символ Из СтрРазделить(" ,.,-,_,*", ",") Цикл
		Текст = СтрЗаменить(Текст, Символ, "");
	КонецЦикла;
	Возврат Текст;
КонецФункции

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

Второе правило касается чисел. Штрихкод и артикул в Excel часто хранятся числом. Если взять у области табличного документа свойство Текст, можно получить экспоненциальную запись или разделители групп разрядов. Поэтому для числовых ячеек значение форматируется явно.

Функция ТекстЯчейки(ТабДок, НомерСтроки, НомерКолонки)
	Область = ТабДок.Область(НомерСтроки, НомерКолонки, НомерСтроки, НомерКолонки);
	Если Область.СодержитЗначение И ТипЗнч(Область.Значение) = Тип("Число") Тогда
		Возврат Формат(Область.Значение, "ЧГ=0; ЧРД=.");
	КонецЕсли;
	Возврат СокрЛП(Область.Текст);
КонецФункции

Файл читается через ТабличныйДокумент.Прочитать со способом чтения "Значение". Для этого содержимое файла пишется во временный файл на сервере, поэтому при регистрации в дополнительных обработках безопасный режим выключен. Полностью пустые строки отбрасываются, чтобы хвост из пустых строк внизу листа не превращался в десятки ошибок "пустое наименование".

Девять проверок до записи

Все проверки собраны в одной функции, которая проходит по таблице один раз и для каждой строки собирает массив ошибок. В строке может быть несколько ошибок сразу, и пользователь увидит их все, а не только первую.

Наименование. Пустое наименование не пропускаем. Наименование длиннее 100 символов тоже: длинный текст должен уходить в "Полное наименование", а краткое остается коротким, каким его видят в списках и документах.

Артикул. Пустой артикул считается ошибкой, потому что по нему товар находится при повторной загрузке. Дубль артикула внутри файла ловится соответствием "артикул → номер строки", и в тексте ошибки сразу указано, где он встретился впервые: "артикул повторяется, первый раз в строке 3". Сравнение идет без учета регистра и крайних пробелов.

Единица измерения. Из классификатора единиц одним запросом выбираются наименование, полное наименование и код, и все три значения кладутся в одно соответствие. Поэтому в файле можно писать "шт", "Штука" или код ОКЕИ 796. А вот "штуки" в классификаторе нет, и это ошибка, а не повод молча поставить единицу по умолчанию.

Ставка НДС. Текст очищается от пробелов и знака процента. Значения 20 и 22 дают вид ставки "Общая", 10 дает "Пониженная", 0 дает "Нулевая", а "без НДС" и прочерк дают "Без НДС". Ставка 18% ошибкой будет: лучше остановить загрузку, чем записать товару неверную ставку, которую потом найдут только при проверке декларации.

Штрихкод. Проверяется формат (только цифры, длина 8 или 13), контрольная цифра, дубль внутри файла и то, что этот штрихкод уже не принадлежит другому товару в базе. Владельцы штрихкодов выбираются из регистра одним запросом по всем штрихкодам файла, а не запросом на каждую строку.

Контрольная цифра EAN-13

Самая полезная проверка из девяти, потому что ошибку в штрихкоде глазами не видно. Последняя цифра EAN-8 и EAN-13 вычисляется из остальных: цифры берутся справа налево, без последней, с весами 3 и 1 по очереди. Контрольная цифра дополняет сумму до ближайшего числа, кратного десяти.

Сумма = 0;
Для Позиция = 1 По Длина - 1 Цикл
	Цифра = Число(Сред(Штрихкод, Длина - Позиция, 1));
	Сумма = Сумма + ?(Позиция % 2 = 1, Цифра * 3, Цифра);
КонецЦикла;
Контрольная = (10 - Сумма % 10) % 10;
Если Контрольная <> Число(Прав(Штрихкод, 1)) Тогда
	Возврат СтрШаблон(НСтр("ru = 'штрихкод %1: неверная контрольная цифра, должна быть %2'"), Штрихкод, Контрольная);
КонецЕсли;

В тестовом файле в строке 23 стоит штрихкод 4604963948095. Формат правильный, 13 цифр, но контрольная цифра должна быть 4, а не 5. Скорее всего, при наборе ошиблись в одной из цифр. Такой код примут все формы, а касса не найдет товар. Обработка пишет, какая цифра должна быть, и это сразу подсказывает, что ошибка именно в наборе, а не в формате.

Перед подсчетом проверяется, что в строке только цифры и их ровно 8 или 13. Для этого есть готовая функция БСП СтроковыеФункцииКлиентСервер.ТолькоЦифрыВСтроке. Строка "46012A4567890" из строки 27 тестового файла отсекается на этом шаге: латинская A вместо цифры обычно появляется, когда штрихкод перепечатывали с бумаги.

Запись: транзакция на строку и повторная проверка

При нажатии "Загрузить" контекст проверки строится заново. Между проверкой и загрузкой может пройти и минута, и час: кто-то успел завести товар с таким же артикулом или привязать штрихкод. Повторная проверка дешевле, чем разбор, откуда в базе взялся дубль.

Для Каждого СтрокаТаблицы Из Таблица Цикл
	Если СтрокаТаблицы.Статус = "Ошибка" Тогда
		Продолжить;
	КонецЕсли;
	Разбор = Контекст.Разбор.Получить(СтрокаТаблицы.СтрокаФайла);
	НачатьТранзакцию();
	Попытка
		Ссылка = ЗаписатьТовар(СтрокаТаблицы, Разбор, Контекст);
		ЗафиксироватьТранзакцию();
	Исключение
		ОтменитьТранзакцию();
		СтрокаТаблицы.Статус = "Ошибка";
		СтрокаТаблицы.Ошибки = НСтр("ru = 'не записано: '") + КраткоеПредставлениеОшибки(ИнформацияОбОшибке());
		Итог.НеЗаписано = Итог.НеЗаписано + 1;
		Продолжить;
	КонецПопытки;
	// дальше счетчики «Создано» и «Обновлено»
КонецЦикла;

Внутри ЗаписатьТовар для существующего товара ставится управляемая блокировка на ссылку, и только потом берется объект. Для нового товара сначала вызывается Заполнить(Неопределено), чтобы сработали значения по умолчанию самой конфигурации, а затем заполняются тип номенклатуры, единица, категория и вид ставки. Группа ищется по наименованию среди групп и создается в корне, если ее нет.

Отдельно стоит сказать о проверке заполнения. Перед записью вызывается ПроверитьЗаполнение, и если конфигурация чем-то недовольна, ее сообщения забираются через ПолучитьСообщенияПользователю и переносятся в колонку ошибок этой строки. Пользователь видит не всплывающее окно с непонятно какой строкой, а конкретную строку Excel с текстом, что не так. Штрихкод пишется в регистр сведений через менеджер записи с замещением.

Что получилось на тестовом файле

Из 50 строк тестового файла проверка останавливает 9, остальные 41 уходят на загрузку. Девять ошибок такие:

1. Строки 5 и 40: пустое наименование и наименование длиннее 100 символов.
2. Строки 9 и 36: артикул повторяется (первый раз он встретился в строке 3) и пустой артикул.
3. Строка 14: единицы "штуки" нет в классификаторе.
4. Строка 18: ставка НДС "18%" не распознана.
5. Строки 23, 27 и 31: неверная контрольная цифра, буква в штрихкоде и дубль штрихкода из строки 20.

Эти же правила, кроме проверок по базе, у меня есть отдельным скриптом на Python. Он удобен, когда файл уже прислали, а доступа к базе еще нет: можно заранее сказать, сколько строк загрузятся и что нужно исправить в файле. Ожидаемый ответ на тестовом файле записан эталоном, и скрипт падает, если находит не то, что заложено.

Про честность проверки. Модули обработки прошли синтаксический контроль на учебной платформе, правила проверки прогнаны на тестовом файле, но тексты запросов и имена реквизитов окончательно проверяются только исполнением в конкретной базе. Поэтому первую загрузку любой обработкой, этой или любой другой, стоит делать на копии базы. Это пять минут, а откат загрузки в рабочей базе может занять день.

Какой файл просить у поставщика

Половину ошибок проще не ловить, а не допускать. Когда файл присылают регулярно, имеет смысл один раз отправить поставщику или менеджеру шаблон и пару правил к нему.

• Первая строка - заголовки, названия колонок как в шаблоне, порядок не важен.
• Штрихкод в формате "Текстовый", чтобы Excel не превращал его в число и не съедал ведущие нули у EAN-8.
• Единица измерения так, как она записана в классификаторе базы: "шт", "кг", "упак". Список единиц лучше приложить к шаблону.
• Ставка НДС числом без знака процента или словами "без НДС".
• Один товар - одна строка, без объединенных ячеек и промежуточных итогов.

Объединенные ячейки и строки "Итого" внутри списка отдельно не проверяются: строка итога без артикула все равно остановится на проверке пустого артикула, но лучше, чтобы ее в файле не было вовсе.

Чего обработка не делает

Чтобы не создавать ложных ожиданий, перечислю, что в нее сознательно не вошло. Цены не загружаются: для них нужен документ установки цен и выбор вида цен, это отдельная задача со своими проверками. Характеристики, серии и картинки тоже не загружаются. Ставки 5% и 7% для поставщиков на упрощенке в разбор НДС не включены: если они встречаются в ваших файлах, их нужно добавить в функцию разбора вместе с соответствующими видами ставок конфигурации. Группы ищутся только по наименованию и создаются в корне, вложенность групп из файла не строится. Если в вашей базе включены характеристики или в справочнике есть обязательные дополнительные реквизиты, проверку и запись нужно дописать под них, и для этого как раз пригодится колонка ошибок: сообщения проверки заполнения туда уже переносятся.

Короткий чек-лист

Если отдать бухгалтеру или менеджеру один абзац из этой статьи, то такой. Перед загрузкой файл должен пройти проверку без записи. Колонки ищутся по заголовкам. Числа читаются со строгим форматом. Пустые и повторяющиеся артикулы и штрихкоды, неизвестные единицы, непонятные ставки НДС и штрихкоды с неверной контрольной цифрой останавливаются с номером строки Excel. Запись идет только для чистых строк, по одной транзакции на строку, и повторная загрузка того же файла обновляет товары, а не создает новые.

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