Добрый день. Стоит задача выполнять раз в месяц корректировку данных элементов справочника контрагенты путем загрузки актуальной информации из файла EXCEL. Накидал обработку для загрузки, но т.к объем файла Excel больше 600000 строк, чтение этого файла длится уже около часа и не известно сколько будет длиться еще. М.б кто-нибудь сталкивался с подобным? Как можно оптимизировать время работы обработки?

EXCEL ADO

еще comsafearray можно, если через ADO данные корежит (сложное форматирование)

Смотря что ты там накидал (с)

600 000 строк по 1 секунде =
10 000 минут ,а это (/60) =
    167 часов ,а это (/24) =
      7 суток или 21 рабочий день

К 50 посту окажется, что тормозит вовсе не чтение из Excel :)))

https://cloud.mail.ru/public/A3XN/wG4ygV2LY


Ссылка на обработку. Ничего сверхестественного нет.

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

(5) код покажи
качать никто и ничего не будет

ТаблицаЗагрузкиИзЭксель.Очистить();

    СтруктураФайлаДляРазбора = ПрочитатьXLSФайл(ФайлЗагрузки);
    
    Если СтруктураФайлаДляРазбора = Неопределено Тогда
        Сообщить("Файл загрузки не содержит данных!",СтатусСообщения);
        Возврат;
    КонецЕсли;
    
    Excel=СтруктураФайлаДляРазбора.Excel;
    WorkBook=СтруктураФайлаДляРазбора.WorkBook;
    Worksheet=WorkBook.Worksheets(1);
    
    //если первая строка или номер колонки наименование не указаны,
    //то пытаемся выяснить их расположение самостоятельно
    Если НомерПервойСтроки=0 или НомерКолонкиНаименование=0 Тогда
        
        //для этого, во-первых, попробуем найти номер строки шапки таблицы
        НомерСтрокиШапкиТаблицы=НайтиСтрокуШапкиТаблицыПоНаличиюЛинииГраницы(Worksheet);
        
        //если номер строки шапки таблицы не удалось узнать, но при этом указан номер первой строки,
        //то предполагаем что строка с шапкой перед первой строкой
        Если НомерСтрокиШапкиТаблицы=0 и НомерПервойСтроки>1 Тогда
            НомерСтрокиШапкиТаблицы=НомерПервойСтроки;
        КонецЕсли;

        //если нашли номер строки шапки таблицы, то попробуем найти номера колонок наименование, ИНН, КПП и т.д.
        Если НомерСтрокиШапкиТаблицы<>0 Тогда
            СтруктураНазванийПеременныхИНазванийКолонок=Новый Структура;
            
            СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиНаименование","Наименование");
            СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиНаименованиеПолное","Полное наименование");
            СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиИНН","ИНН");
            СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиКПП","КПП");
            СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиОГРН","ОГРН");
            СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиАдрес","Адрес");
            СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиСтрана","Страна");
            СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиТелефон","Телефон");
            
            НомерПоследнейКолонкиШапкиТаблицы=50;
            СтруктураЗначенийПеременных=НайтиНомераКолонок(Worksheet,СтруктураНазванийПеременныхИНазванийКолонок,НомерСтрокиШапкиТаблицы,НомерПоследнейКолонкиШапкиТаблицы);
            
            Если СтруктураЗначенийПеременных.Свойство("НомерКолонкиНаименование") Тогда
                НомерКолонкиНаименование=СтруктураЗначенийПеременных.НомерКолонкиНаименование;
            КонецЕсли;
            Если СтруктураЗначенийПеременных.Свойство("НомерКолонкиНаименованиеПолное") Тогда
                НомерКолонкиНаименованиеПолное=СтруктураЗначенийПеременных.НомерКолонкиНаименованиеПолное;
            КонецЕсли;
            Если СтруктураЗначенийПеременных.Свойство("НомерКолонкиИНН") Тогда
                НомерКолонкиИНН=СтруктураЗначенийПеременных.НомерКолонкиИНН;
            КонецЕсли;
            Если СтруктураЗначенийПеременных.Свойство("НомерКолонкиКПП") Тогда
                НомерКолонкиКПП=СтруктураЗначенийПеременных.НомерКолонкиКПП;
            КонецЕсли;
            Если СтруктураЗначенийПеременных.Свойство("НомерКолонкиЮрАдрес") Тогда
                НомерКолонкиЮрАдрес=СтруктураЗначенийПеременных.НомерКолонкиЮрАдрес;
            КонецЕсли;
            Если СтруктураЗначенийПеременных.Свойство("НомерКолонкиФактАдрес") Тогда
                НомерКолонкиФактАдрес=СтруктураЗначенийПеременных.НомерКолонкиФактАдрес;
            КонецЕсли;
            Если СтруктураЗначенийПеременных.Свойство("НомерКолонкиПочтАдрес") Тогда
                НомерКолонкиПочтАдрес=СтруктураЗначенийПеременных.НомерКолонкиПочтАдрес;
            КонецЕсли;
            Если СтруктураЗначенийПеременных.Свойство("НомерКолонкиТелефон") Тогда
                НомерКолонкиТелефон=СтруктураЗначенийПеременных.НомерКолонкиТелефон;
            КонецЕсли;
            
            //если номер первой строки неизвестен, но при удалось выяснить номер строки шапки таблицы,
            //то предполагаем что номер первой строки следует сразу после строки с шапкой таблицы
            Если НомерПервойСтроки=0 и НомерСтрокиШапкиТаблицы<>0 Тогда
                НомерПервойСтроки=НомерСтрокиШапкиТаблицы+1;
            КонецЕсли;
            
        КонецЕсли;
        
    КонецЕсли;
    
    //если ничего выяснить не удалось, то сообщаем об ошибке
    Если НомерПервойСтроки=0 или НомерКолонкиНаименование=0 Тогда
        ОчиститьСообщения();
        Сообщение=Новый СообщениеПользователю;
        Сообщение.УстановитьДанные(ЭтотОбъект);
        Если НомерПервойСтроки=0 Тогда
            Сообщение.Поле="НомерПервойСтроки";
            Сообщение.Текст="Не указан номер первой строки. Укажите в настройках";
            Сообщение.Сообщить();
        КонецЕсли;
        Если НомерКолонкиНаименование=0 Тогда
            Сообщение.Поле="НомерКолонкиНаименование";
            Сообщение.Текст="Не указан номер колонки наименование. Укажите в настройках";
            Сообщение.Сообщить();
        КонецЕсли;
        Сообщить("Для работы обработки обязательно наличие номера первой строки и номера колонки ""Наименование""."+Символы.ПС+
        "Номера остальных колонок укажите, если они присутствуют в файле.");
        ЗакрытьФайл(WorkBook,Excel);
        Возврат;
    КонецЕсли;
    
    СмещениеСтрок=НомерПервойСтроки;
    СчетчикСтрок=1;
    Пока НЕ ПроверитьЧтоДошлиДоКонцаФайла(Worksheet,СмещениеСтрок,НомерКолонкиНаименование) Цикл
        
       Наименование=СокрЛП(Строка(Worksheet.Cells(СмещениеСтрок,НомерКолонкиНаименование).Value));
       Если НомерКолонкиНаименованиеПолное>0 Тогда
           НаименованиеПолное=СокрЛП(Строка(Worksheet.Cells(СмещениеСтрок,НомерКолонкиНаименованиеПолное).Value));
       Иначе
           НаименованиеПолное="";
       КонецЕсли;
       Если НомерКолонкиИНН>0 Тогда
           ИНН=СокрЛП(Строка(Worksheet.Cells(СмещениеСтрок,НомерКолонкиИНН).Value));
       Иначе
           ИНН="";
       КонецЕсли;
       ИНН=СтрЗаменить(ИНН,Символ(160),"");
       Если НомерКолонкиСтрана>0 Тогда
           Страна=СокрЛП(Строка(Worksheet.Cells(СмещениеСтрок,НомерКолонкиСтрана).Value));
       Иначе
           Страна="";
       КонецЕсли;
       Страна=СтрЗаменить(Страна,Символ(160),"");
       Если НомерКолонкиАдрес>0 Тогда
           ЮрАдрес=СокрЛП(Строка(Worksheet.Cells(СмещениеСтрок,НомерКолонкиАдрес).Value));
             ФактАдрес=СокрЛП(Строка(Worksheet.Cells(СмещениеСтрок,НомерКолонкиАдрес).Value));
              ПочтАдрес=СокрЛП(Строка(Worksheet.Cells(СмещениеСтрок,НомерКолонкиАдрес).Value));
       Иначе
           ЮрАдрес="";
           ФактАдрес="";
           ПочтАдрес="";
       КонецЕсли;
       Если НомерКолонкиТелефон>0 Тогда
           Телефон=СокрЛП(Строка(Worksheet.Cells(СмещениеСтрок,НомерКолонкиТелефон).Value));
       Иначе
           Телефон="";
       КонецЕсли;
        
       Если НЕ ПустаяСтрока(Наименование) Тогда
           Новаястрока=ТаблицаЗагрузкиИзЭксель.Добавить();
           Новаястрока.НомерСтроки=СчетчикСтрок;
           Новаястрока.Наименование=Наименование;
           Новаястрока.НаименованиеПолное=НаименованиеПолное;
           Новаястрока.ИНН=ИНН;
           Новаястрока.Страна=Страна;
           Новаястрока.ЮрАдрес=ЮрАдрес;
           Новаястрока.ФактАдрес=ФактАдрес;
           Новаястрока.ПочтАдрес=ПочтАдрес;
           Новаястрока.Телефон=Телефон;
           СчетчикСтрок=СчетчикСтрок+1;    
       КонецЕсли;
        
        СмещениеСтрок=СмещениеСтрок+1;

    КонецЦикла;
    
    ЗакрытьФайл(WorkBook,Excel);
    
    Если ТаблицаЗагрузкиИзЭксель.Количество()=0 Тогда
        Сообщить("Не удалось прочитать данные из файла.");
    КонецЕсли;

КонецПроцедуры

Функция ПрочитатьXLSФайл(ФайлЗагрузки)
    
    СтруктураФайлаДляРазбора=Новый Структура;
    
    Попытка
        Excel = Новый ComObject("Excel.Application");
        Excel.DisplayAlerts = Ложь;
    Исключение
        ОбщегоНазначенияКлиентСервер.СообщитьПользователю("не удалось создать объект Excel");
        Возврат Неопределено;
    КонецПопытки;
    СтруктураФайлаДляРазбора.Вставить("Excel",Excel);
    
    Попытка
        Excel.WorkBooks().Open(ФайлЗагрузки);
    Исключение
        ОбщегоНазначенияКлиентСервер.СообщитьПользователю("не удалось открыть файл");
        Возврат Неопределено;
    КонецПопытки;
    СтруктураФайлаДляРазбора.Вставить("WorkBook",Excel.ActiveWorkBook);
    
    Возврат СтруктураФайлаДляРазбора;
    
КонецФункции

(9)[Excel.Application]
не обсуждается
см (1)

Насколько я помню в ADO нужно данные вытягивать запросом типо

ТекстЗапроса =
   "SELECT
   |    *
   |FROM
   |    [" + ИмяЛистаВФайле + "$" + ДиапазонЯчеек + "]" ;

НО. В моей ситуации неизвестен диапазон, т.к файл постоянно меняется.

(11) нет
набор записей имеет EOF и диапазон ему не нужен

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

(12) Есть возможность скинуть линк на пример для лл?

(13) ни о чем

(1) (15) Ладно, будем изучать. Спасибо.

У меня эксель читается 100 000 строк в минуту при заполненных 15 колонках

Но читать эксель одно, а синхронизировать потом в 1С это соввершенно другое

а те5перь прикинь как ты будешь делать запрос потом в базу 1С чтобы найти эти 600 000 строк.

Вот о чем думать надо

(16) 1-15 люди тебе написали то что вообще роли не играет.

"Пристрелите его уже кто-нибудь, а?"

(0) В 1С-е Файл - Открыть и выбрать Excel файл. Долго открывает?

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

Из другой системы нужно выгружать не "больше 600000 строк", а только изменения. И не в Excel, а в XML в формате КД.

xlsx файл это архив с xml-ками. Распаковываешь и читаешь что тебе нужно непосредственно из xml-к.

(22) и как это на скорость повлияет? - ответ - никак.
Любые переборы циклами в 1С будут тормозить.

И опять таки что толку от чтения, если я уверен на синхронизации - он трое суток будет 600 000 элементов синхронизировать

Небось еще и в цикле запросы будет фигачить)))

(0) только не говори что ты читаешь файл и одновременно в том же цикле в 1С ищешь.

(0) Так как все тут ничерта не шарят - даю подсказку. На бутылку офигенного коньяка.

1) В 1С делаешь табличное поле.
2) Открываешь свой файл в экселе
3) Копируешь таблицу всю
4) в 1С в табличное поле в краянюю ячейку становишся - нажимаешь вставить Ctrl+V

5) и бах - оно у тебя в 1С

6) Читаешь табличное поле кодом 1С - читать будет в 100500 раз быстрее

Только как я уже три раза писал все фигня.
Главное - синхронизация с тем чт ов 1С

Сохрани исходный файл в csv  и обрабатывай как текст

600000 дофига. как они умудрились такой файл создать. у меня был на 120000 так он открывался в екселе минут десять.
Лучше использовать другой формат.

(0) я грузил 400 тыс строк. использовал АДО. все делалось в разумное время.

(0) EXCEL не предназначен для таких объемов. Для работы с данными лучше использовать базы данных.

(25) Начиная с платформы 8.3.6 можно программно:
ТабличныйДокумент.Прочитать(ИмяФайлEXCEL, СпособЧтенияЗначенийТабличногоДокумента);

(25) "3) Копируешь таблицу всю "

тут затык будет .

(31) Прочитай программно со второго листа :)

Не помню сколько строк в реестре лекарственных средств, но там тоже, что то в десятках или мб даже в сотнях тысяч строк. Залетает за минуты. Мысль в том, что бы уменьшить количество обращений к ком объекту, если у тебя много строк, но мало колонок, считывай колонками данные в табличный документ и раскуривай потом его. Чтение ускоряется в разы. Правда от обработки данных все равно никуда не деться. Может делать в фоне порциями..
Вот статья http://catalog.mista.ru/public/20090/ с обсуждением и разными вариантами.

(30) Если клиент хочет. Почему нет? )) БД предназначена для многократной обработки данных. Excel нужно загружать раз в месяц в БД и там обрабатывать.

Если (20) происходит относительно быстро, то файл можно загружать программно (31). Далее нужно правильно распорядиться данными. Если не писать быдлокод, а постараться его оптимизировать, получится относительно быстро.

Начиная с excel 2007 в формате xlsx можно хранить:
1 048 576 строк и  16 384 колонок. По сути это формат хранения OpenXML, соответственно для разбора таких файлов нужно использовать внешние библиотеки так называемые 'sax parsers'.

(33) В исходных условиях про второй лист не сказано.

убить того, кто наружу данные для обмена выкидывает в экселе при таких объемах.

сохранить в текстовом файле с разделителями и парсить в 1с как текст уже предлагали? 600000 строк текста в тз 1с скушает за несколько секунд

(38) Причем медленно... Как раз за то время, пока грузится инфа из Excel.

xlsx это тот же xml можно читать как текстовый файл. так в чем разница?