Ситуация: 1С8 запросом формирует табличный документ и сохраняет его в папку в формате "xlsx" В Excel query создается запрос на подключение к этому файлу. В редакторе запроса, при попытке провалиться в табличную часть документа, возникает ошибка (скриншоты ниже).
Обойти проблему не получается, но обнаружилось, что: 1) если сгенерированный файл имеет размер, скажем 1,3 МБ, то стоит его открыть в excel и сохранить под тем же именем, как он обретет вес в 1,6 МБ, и при этом полностью исчезнет проблема с добавлением данных в запрос 2) судя по всему 1С создает бинарный файл excel, который скорее xlsb, чем xlsx 3) попытка сравнить средствами [inquire - compare files] два файла, один из которых подвергся пересохранению через excel, а другой - в первозданном виде, дала обескураживающий результат (см скрины). При этом каждое из отличий при сравнении в excel посредством ссылка=ссылка возвращет ИСТИНА. Судя по всему, разница в способе хранения данных в файлах разного типа. Нашел этому косвенное подтверждение тут. Не понял, по каком принципу выводятся отличия (ведь иногда число во второй таблице больше, а иногда меньше, а иногда в текстовых ячейках невидимые отличия) 4) если для выгрузки из 1С задавать формат "xls", данные корректно можно загрузить в query, НО размер файла увеличивает в 3-4 раза, а сам запрос начинает работать очень медленно.
Т.к. файлы содержат персональные данные, не могу их приложить. Не могу очистить файл от персональных данных, т.к. редактирование в excel приведет к необходимости пересохранить файл из excel (см п.1 со всеми вытекающими) upd: приложил файл примера. отказывается распознаваться в query. если открыть в экселе, сохранить, закрыть - начнет распознаваться.
Собственно вопросы: 1) верно ли предположение №2 про xlsb под соусом xlsx? 2) верно ли, что query плохо справляется с двоичными книгами excel? 3) сталкивались ли вы с похожей ошибкой импорта данных в запрос? если да, удалось ли решить проблему? 4) в свойствах подключения по умолчанию создается строка - может ли она оказывать влияние на возникновение ошибки?
5) есть ли возможность дописать запрос вручную так, чтобы он возвращал не ошибку, а все же таблицу. Возможно следует как-то преобразовать источник запросом?
Посмотрел сейчас у себя. Такая же фигня с файлами, которые выгружаешь в формат 2007. Совет: выгружайте в txt. Размер файла, у меня по крайне мере, даже меньше, чем xlsx.
Спасибо за ответы, а то я вчера успел отчаяться, что тишина полная
PowerBoy, 1) Допустим, но с чем связано изменение размера после пересохранения? 2) Я потыкался сейчас и обнаружилось, что а) если условно длинные данные (тысяч 30 строк) скопировать как значения в новую книгу excel, сохранить ее в двоичном формате, то файл не откроется. Если удалить и оставить тысяч 6 строк, откроется. Если эти строки в источнике продублировать и превратить в 12 тыс строк - не откроется (то есть дело не в содержимом) б) даже короткий файл из 1С дает ошибку 3) 1С:Предприятие 8.2 (8.2.19.130) но для создания файла примера я пользовался http://v8.1c.ru/metod/fileworkshop.htm
StepanWolkoff, я пробовал одни и те же данные выгружать во всех форматах чтобы оценить разницу в размере. очень большой разрыв. xlsx предлагает наименьший объем. скрина под рукой нет, но txt сильно (в несколько раз) тяжелее. возможно на малом объеме txt меньше, но выгружается полтора десятка столбцов и десятки тыс строк с данными.
Размер файла xlsx меньше xls потому что он упакованный. Переименуйте у файла расширение xlsx в zip и распакуйте архиватором. Кстати тоже самое можете проделать и с файлом полученным от 1с. Сравните файлы от 1с и excel и внутри вы легко найдете различия по вложенным файлам и папкам.
tabularasa, это ошибка схемы XML при выгрузке из 1С. Как вы верно заметили, чтобы работало - надо перед загрузкой в PQ открыть файл в Екселе, просто сохранить и всё заработает.
Цитата
1) верно ли предположение №2 про xlsb под соусом xlsx?
нет
Цитата
2) верно ли, что query плохо справляется с двоичными книгами excel?
скажем так, он их не любит сильно (это относится к XLSB). Особенно это касается импорта из умных таблиц - PQ далеко не всегда будет их видеть. где-то видел даже пост на эту тему с подробным разбором и методами если не лечения, то хотя бы постановки диагноза
Цитата
3) сталкивались ли вы с похожей ошибкой импорта данных в запрос? если да, удалось ли решить проблему?
см. выше. аналогичная проблема в Power BI. лечится только пересохранением. Тикет в МС я кидал, сказали "thank you for your cooperation".
Цитата
4) в свойствах подключения по умолчанию создается строка - может ли она оказывать влияние на возникновение ошибки?
нет
Цитата
5) есть ли возможность дописать запрос вручную так, чтобы он возвращал не ошибку, а все же таблицу. Возможно следует как-то преобразовать источник запросом?
вряд ли. Не, ну если очень хочется позаморачиваться с разбором XML, или с какими-то бинарными преобразованиями... но смысл? Проще и быстрее исправлять эту проблему так, как описано выше.
Цитата
tabularasa написал: если условно длинные данные (тысяч 30 строк) скопировать как значения в новую книгу excel, сохранить ее в двоичном формате, то файл не откроется. Если удалить и оставить тысяч 6 строк, откроется. Если эти строки в источнике продублировать и превратить в 12 тыс строк - не откроется (то есть дело не в содержимом)
создал книгу, забил СЛУЧМЕЖДУ в 30000 строк, сохранил в 2 вариантах: xlsx и xlsb. никаких проблем (разве что xlsb чуть дольше по ощущениям).
Забил СЛУЧМЕЖДУ в 1048576 строк, файл xlsx размером 16 Мб, файл xlsb - чуть больше 10 Мб. xlsb читать отказалась (DataFormat.Error: External table is not in the expected format.) xlsx читает без проблем
Вообще, проблему удалось если не решить, то обойти: 1С формирует таблицу, сохраняет, затем открывает сохраненный файл xlsx и сохраняет еще раз, уже из оболочки excel (в рамках процедуры в 1С). Программисту спасибо и привет, если читает эту тему
Ради информации - столкнулся с этой проблемой после того как пересел на Excel'16, до этого те же файлы которые прекрасно скармливались в Excel'10 + PQ.
Слегка оживлю тему. Данная проблема у меня перестала воспроизводиться в Power Query примерно с февраля на версии Excel 2016 после установки обновлений актуальных на сентябрь 2017 года. Сказать абсолютно точно что это заслуга MS не могу, может и в 1С что-то подкрутили. Но факт на лицо теперь файлы с расширением xlsx я не пересохраняю и все работает без сбоев. НО, есть один нюанс: если 1С сохраняет в ячейку переменную с фиксированным количеством символов (обычно это ID номенклатур , типа должно быть 11 знаков) то в случае когда количество символов меньше чем должно быть - 1С забивает это дело невидимым символом с кодом 160. Ранее при пересохранении файлов Excel самостоятельно эти символы убирал из ячеек, а теперь от этого дела надо делать зачистку. У меня все.
написал: 1С8 запросом формирует табличный документ и сохраняет его в папку в формате "xlsx"В Excel query создается запрос на подключение к этому файлу.В редакторе запроса, при попытке провалиться в табличную часть документа, возникает ошибка
Только столкнулся с такой проблемой с файлами из 1с. Ошибка - DataFormat.Error: Входные данные не удалось распознать как допустимый документ Excel. Открываю файлы, жму Ctrl + S, закрываю. После этого PQ видит листы. Прошу программистов Vba сделать макрос, который откроет в папках файлы xlsx и их пересохранить . Две папки: C:\a и C:\b Версия PQ - 2.98
Sub OtkritVseKnigi()
'Шаг 1:Объявляем переменные
Dim MyFiles As String
'Шаг 2: Укажите нужную папку
MyFiles = Dir("C:\a\*.xlsx")
Do While MyFiles <> ""
'Шаг 3: Открываем файлы один за другим
Workbooks.Open "C:\a\" & MyFiles
'Код макроса с действиями
'MsgBox ActiveWorkbook.Name
ActiveWorkbook.Save
ActiveWorkbook.Close SaveChanges:=True
'Шаг 4: Следующий файл в папке
MyFiles = Dir
Loop
End Sub