Страницы: 1
RSS
Power Query и файлы xlsb в чем проблема? почему иногда пишет о какой-то ошибке?
 
Всем доброго времени суток, столкнулся с тем, что в запросе Power Query который обрабатывает xlsb файл возникает ошибка сразу после открытия файла с запросом.
Кто сталкивался? Как решается эта проблема?
На англоязычных сайтах просто советуют принудительно обновить и якобы помогает.
Прошу отзывов только тех, кто сталкивался с этим.
Изменено: Konstantin Zhi - 13.07.2019 22:25:47
 
Цитата
Konstantin Zhi написал:
Кто сталкивался? Как решается эта проблема?
Так вы воспользуйтесь поиском по форуму и сразу увидите кто и сколько раз сталкивался. Обсуждался вопрос раз 10 уже.
Для подключения к бинарным файлам PQ использует, если мне не изменяет мой склероз, ACE.OLEDB. И вот у этой штуковины регулярно возникают проблемы при обращении к файлам в бинарном формате, это xlsb и xls. И даже если считать инфу получается, то делает это очень медленно.
Так что настоятельно рекомендую в качестве источника данных использовать текстовые файлы - никаких глюков и скорость чтения раз в 5 выше чем из того же xlsx.
Изменено: PooHkrd - 15.07.2019 09:15:22
Вот горшок пустой, он предмет простой...
 
не знаю поверите Вы мне или нет, я искал по форуму, только в запросе я переставил xlsb и Power Query - мне ничего не нашло..
За рекомендацию - спасибо, но я вынужден использовать именно xlsb формат, т.к. в том файле есть UDF, она обрабатывает первичные данные (выгрузку из 1с) возвращая цифру отступа в ячейке - это единственный способ потом обработать строки в запросе.

Правильно я понял Ваш ответ по сути, что нормального решения проблемы нет?
 
Konstantin Zhi, а кто мешает UDF запихать в xlsm формат? И никаких клюков с запросами PQ.
Цитата
Konstantin Zhi написал:
это единственный способ потом обработать строки в запросе
Это вам здесь такое сказали?
Покажите пример файла и обрисуйте задачу, которую нужно в PQ реализовать. Но это уже в новой теме.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
а кто мешает UDF запихать в xlsm формат
если так сделать, то постоянно будут выскакивать сообщения, что эта книга содержит макросы - что не удобно.. как-то так..
Вы, вероятно, не очень внимательно прочитали.. UDF определяет цифру отступа... Это как-бы формат ячейки.. А Power Query форматы не видит...
 
Цитата
Konstantin Zhi написал:
Power Query форматы не видит.
на этом форуме увидит :)  
 
Цитата
Konstantin Zhi написал:
А Power Query форматы не видит...
А вот это - спорное утверждение. Например вот тема от Максима Зеленского с функцией, которая как раз определяет иерархию вложенности уровней на основе группировки строк. Функция лезет в xml структуру файла, находит для каждой строки вложенность и джойнит её уже к содержимому этих строк.
Так что возвращаемся к моему посту за №4.  ;)
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
на основе группировки строк
в исходном файле не будет группировки строк, все равно выкладывать?
 
Почему бы и нет. Выкладывайте. Только в новой теме, как новую задачу.
Вот горшок пустой, он предмет простой...
 
Вроде бы нашел решение проблемы. Можно обращаться к бинарникам и не ловить при этом ошибки о некорректном формате. Для этого придется использовать функцию Binary.Buffer(). Как это выглядит? При создании запроса к файлу автоматически генерится код вида
Код
= Excel.Workbook( File.Contents("C:\Folder\file.xlsb" ), null, true)

Так вот его нужно переделать так:
Код
= Excel.Workbook( Binary.Buffer( File.Contents("C:\Folder\file.xlsb" ) ), null, true )

В таком случае все содержимое файла уедет в память и дальнейшее обращение функции Excel.Workbook будет происходить именно с памятью, а не с диском. Таким образом мы избегаем конфликта этой функции, которая пытается сделать предпросмотр данных через коннектор ACE.oledb, который к этому не очень предрасположен. Т.е. сначала коннектор полностью грузит содержимое файла в память, и только потом уже PQ лезет в память своими потными ручонками.
Проверял, схема рабочая, НО выгрузка очень медленная. А с более-менее большими файлами работать просто мучение. Так что если уж очень надо, то пользуйтесь, но лучше все же текст или xlsx. Возможно такой же прием поможет избежать и глюков при работе с файлами формата xls, но это не проверял, так что не гарантирую.
Всех благ.
Изменено: PooHkrd - 10.06.2020 18:16:13
Вот горшок пустой, он предмет простой...
 
Цитата
Konstantin Zhi написал:
если так сделать, то постоянно будут выскакивать сообщения, что эта книга содержит макросы - что не удобно..

Есть два способа решения этого:

1) Файл-> Параметры-> Центр управления безопасностью-> Параметры центра управления безопасностью-> Параметры конфиденциальности-> снять флажок с Удалять персональные данные из свойств файла при сохранении
2) Описан в этой теме пользователем ZVI :

Вот этот код нужно записать в модуль ЭтаКнига персональной книги макросов,  сохранить эту книгу макросов и перезагрузить Excel:  

Код
Private WithEvents App As Application  
 
Private Sub Workbook_Open()  
 Set App = Application  
End Sub  
 
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)  
 On Error Resume Next  
 If Wb.RemovePersonalInformation Then Wb.RemovePersonalInformation = False  
End Sub  
Изменено: Dyroff - 12.06.2020 10:20:58
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Попробовал еще один вариант. Вроде тоже отрабатывает без ошибок, и так же медленно. Но по идее такой подход должен жрать меньше памяти:
Напоминаю, что это строка кода, которую генерит UI при обращении к файлу.

Код
= Excel.Workbook( File.Contents("C:\Folder\file.xlsb" ), null, true)

а можно поменять true на false
Код
= Excel.Workbook( File.Contents("C:\Folder\file.xlsb" ), null, false)

Таким образом опять же отключается частичный просмотр файла со стороны Excel.Workbook и пропадает конфликт между коннектором который тащит данные собственно из файла и функцией, которая уже разбирается в бинарнике, где там и что лежит.
А вообще щас попробовал через обновленный O365 тягать бинарники - открывает только в путь безо всяких ухищрений и ошибок. Так что может и починили чего все таки.

З.Ы. Потестировал данный способ в течении месяца, оказался не надёжным. Иногда ошибки таки вылезают. Вариант с Binary.Buffer надежен как скала. Ошибок нет. Но какой же он ме-е-едленный.
Изменено: PooHkrd - 07.10.2020 13:58:18
Вот горшок пустой, он предмет простой...
 
С бинарниками (xlsb и xls) есть и другая проблема, недавно где-то пробежала инфа от разработчиков, что данные из них посредством этого ACE читаются не по фактическому значению, а по отображаемому. иными словами, читается не Cell.Value, а Cell.Text
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
читается не Cell.Value, а Cell.Text
Тоже погонял эту тему, и как говорится, тут тоже не все так однозначно.  :D Тестировал только с xlsb
Например, если тягать данные без использования Binary.Buffer не с листа, а из "умной" таблицы, то тащит именно Cell.Value. Но тогда ждите ошибки.  ;)
А вот после загона бинарника в память, читать можно откуда угодно, тащит исключительно Cell.Text. Зато без ошибок!
Легко проверяется на считывании столбцов с датами.
В результате код присваивающий типы столбцам должен быть разным для вариантов с буфером и без.   :idea:
В общем чего они там по-наворотили, без поллитры хрен разберешься.
Вот горшок пустой, он предмет простой...
 
PQ на сколько я знаю около 4-5 лет, жалко, что они за столько лет не могут решить эту проболему
 
New, 7 годков уже.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал: = Excel.Workbook( Binary.Buffer( File.Contents("C:\Folder\file.xlsb" ) ), null, true )
Добрый день! Не подскажите, как такое применить к папке?
 
Glutton, лучше не надо к папке. Будет медленно, и очень большая вероятность, что оперативки не хватит. Один файл в случае крайней необходимости еще можно. Настоятельно рекомендую переделать источники в xlsx либо в текст.
Вот горшок пустой, он предмет простой...
 
Спасибо! Да, видимо, это единственное здравое решение, а так хотелось выкроить шесть шапок из овцы.  
Страницы: 1
Наверх