Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Excel query не распознает табличный док из 1С
 
Добрый вечер.

Ситуация:
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 раза, а сам запрос начинает работать очень медленно.

скриншоты (imgur)

Т.к. файлы содержат персональные данные, не могу их приложить. Не могу очистить файл от персональных данных, т.к. редактирование в excel приведет к необходимости пересохранить файл из excel (см п.1 со всеми вытекающими)
upd: приложил файл примера. отказывается распознаваться в query. если открыть в экселе, сохранить, закрыть - начнет распознаваться.

Собственно вопросы:
1) верно ли предположение №2 про xlsb под соусом xlsx?
2) верно ли, что query плохо справляется с двоичными книгами excel?
3) сталкивались ли вы с похожей ошибкой импорта данных в запрос? если да, удалось ли решить проблему?
4) в свойствах подключения по умолчанию создается строка - может ли она оказывать влияние на возникновение ошибки?
Код
Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=TDSheet2;Extended Properties=""
5) есть ли возможность дописать запрос вручную так, чтобы он возвращал не ошибку, а все же таблицу. Возможно следует как-то преобразовать источник запросом?

Спасибо
Изменено: tabularasa - 27 Июл 2016 23:13:07
 
1.Нет, у Вас формат xlsx.
2.Нет, без разницы.
3.Это ошибка платформы 1с. Какой у вас релиз, возможно в последних исправили эту ошибку.
4.Не поможет.

Либо работайте с фалами формата xls или сохраните  книгу перед использованием.
Excel + SQL = Activetables
 
Посмотрел сейчас у себя.
Такая же фигня с файлами, которые выгружаешь в формат 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 и внутри вы легко найдете различия по вложенным файлам и папкам.
Excel + SQL = Activetables
 
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 читает без проблем

не любит он xlsb
F1 творит чудеса
 
Максим Зеленский,
Спасибо за развернутый ответ.

Вообще, проблему удалось если не решить, то обойти:
1С формирует таблицу, сохраняет, затем открывает сохраненный файл xlsx и сохраняет еще раз, уже из оболочки excel (в рамках процедуры в 1С).
Программисту спасибо и привет, если читает эту тему 8)
 
Ради информации - столкнулся с этой проблемой после того как пересел на Excel'16, до этого те же файлы которые прекрасно скармливались в Excel'10 + PQ.
 
Слегка оживлю тему.
Данная проблема у меня перестала воспроизводиться в Power Query примерно с февраля на версии Excel 2016 после установки обновлений актуальных на сентябрь 2017 года. Сказать абсолютно точно что это заслуга MS не могу, может и в 1С что-то подкрутили. Но факт на лицо теперь файлы с расширением xlsx я не пересохраняю и все работает без сбоев.
НО, есть один нюанс: если 1С сохраняет в ячейку переменную с фиксированным количеством символов (обычно это ID номенклатур ;), типа должно быть 11 знаков) то в случае когда количество символов меньше чем должно быть - 1С забивает это дело невидимым символом с кодом 160. Ранее при пересохранении файлов Excel самостоятельно эти символы убирал из ячеек, а теперь от этого дела надо делать зачистку.
У меня все.
Страницы: 1
Читают тему (гостей: 1)