Dataformat error входные данные не удалось распознать как допустимый документ excel сведения binary
"внешняя таблица не в ожидаемом формате."обычно происходит при попытке использовать файл Excel 2007 со строкой подключения, использующей: Microsoft.Реактивный.Oledb для.4.0 и расширенные свойства=Excel 8.0
использование следующей строки подключения, похоже, устраняет большинство проблем.
Спасибо за этот код :) я действительно ценю это. Работать на меня.
поэтому, если у вас есть diff версия файла Excel, получите имя файла, если его расширение .XLSX-файл используйте этот:
и если это .в XLS использовать:
(У меня слишком низкая репутация, чтобы комментировать, но это комментарий к записи JoshCaba, используя Ace-engine вместо Jet для Excel 2007)
Это относится и к Excel 2010.
просто добавляйте свое дело. Мой файл xls был создан функцией экспорта данных с веб-сайта, расширение файла xls, он может быть нормально открыт MS Excel 2003. Но и Microsoft.Реактивный.Oledb для.4.0 и Microsoft.ТУЗ.Oledb для.12.0 получено исключение" внешняя таблица не в ожидаемом формате".
наконец, проблема в том, что, как сказано в исключении, "это не в ожидаемом формате". Хотя это имя расширения-xls, Но когда я открываю его с помощью текстового редактора, это на самом деле хорошо сформированный html-файл, все данные находятся в
| - ячейкой. Тогда я думаю, что могу разобрать его в html-формате. |
У меня была такая же проблема(используя ACE.OLEDB) и что разрешило это для меня была эта ссылка:
суть в том, что установка нескольких версий office и различных Office sdk, сборок и т. д. привел к Ацеоледбу.ссылка dll в реестре, указывающая на папку OFFICE12 вместо OFFICE14 в
C:\Program Файлы\Общие Файлы\Microsoft Общий\OFFICE14\ACEOLEDB.Dll файлы
кроме того, вы можете изменить раздел реестра, изменив путь dll в соответствии с версией Access.
Access 2007 должен использовать OFFICE12, Access 2010-OFFICE14 и Access 2013-OFFICE15
(ОС: 64-битный офис: 64bit) или (ОС: 32-битный офис: 32бит)
ключ: HKCR\CLSID\InprocServer32\
Имя Значения: (По Умолчанию)
Значение Данных: C:\Program Файлы\Общие Файлы\Microsoft Общий\OFFICE14\ACEOLEDB.Dll файлы
(OS: 64bit офис: 32bit)
ключ: HKCR\Wow6432Node\CLSID\InprocServer32\
Имя Значения: (По Умолчанию)
значение данных: C:\Program файлы (x86)\Общие файлы\Microsoft Общий\OFFICE14\ACEOLEDB.Dll файлы
Я также видел эту ошибку при попытке использовать сложные косвенные () формулы на импортируемом листе. Я заметил это, потому что это была единственная разница между двумя книгами, где один импортировал, а другой нет. Оба были 2007+ .XLSX файлы, и 12.0 двигатель был установлен.
Я подтвердил, что это была проблема:
- создание копии файла (все еще была проблема, так что это не было какой-то сохранить-как разница)
- опции все ячейки в листе с косвенными формулами
- вставка только как значения
и ошибка исчезла.
Я получал ошибки со сторонним и Oledb чтением книги XLSX. Проблема, как представляется, скрытый лист, который вызывает ошибку. Отображение листа позволило импортировать книгу.
вместо OleDb вы можете использовать взаимодействие Excel и открыть лист только для чтения.
файл может быть заблокирован другим процессом, вам нужно скопировать его, а затем загрузить его, как он говорит, в это в должности
рекомендация сохранить как Excel 2003
Если у вас все еще есть эта проблема, то проверьте свои разрешения, я пробовал многие из этих предложений, и моя конкретная проблема заключалась в том, что файл, который я хотел обработать, находился под контролем источника, и поток не имел разрешений, мне пришлось изменить все разрешения папки, и он начал работать (я обрабатывал много файлов там). Он также соответствует многим предложениям, таким как изменить имя файла или проверить, что файл не loicked другим процессом.
Я надеюсь, что это помочь тебе.
Ace поддерживает все предыдущие версии Office
этот код работает хорошо!
Это может произойти, если книга защищена паролем. Существуют некоторые обходные пути для удаления этой защиты, но большинство примеров, которые вы найдете в интернете, устарели. В любом случае простое решение-снять защиту книги вручную, иначе используйте что-то вроде OpenXML для программного удаления защиты.
столкнулся с той же проблемой и нашел этот поток. Ни одно из предложений выше не помогло, за исключением комментария @Smith к принятому ответу 17 апреля ' 13.
фон моей проблемы достаточно близок к @zhiyazw - в основном пытается установить экспортированный файл Excel (SSRS в моем случае) в качестве источника данных в пакете dtsx. Все, что я сделал, немного повозившись, это переименовал рабочий лист. Он не должен быть строчным, как предложил @Smith.
Я полагаю, туз OLEDB ожидает, что файл Excel будет следовать определенной структуре XML, но каким-то образом Службы Reporting Services об этом не знают.
этот адрес файла excel может иметь неправильное расширение. Вы можете изменить расширение с xls на xlsx или наоборот и повторить попытку.
При попытке внести изменения в базу данных службы Power BI, например, добавив дополнительные таблицы в базу данных SQL, пользователи Power BI могут столкнуться с различными ошибками формата данных. Некоторые из ошибок включают DataFormat.Error: Мы достигли конца буфера или power bi dataformat.error внешней таблицы не в ожидаемом формате .
Если вы также обеспокоены этими ошибками Power BI, вот несколько советов по устранению неполадок, чтобы решить проблему с несколькими ошибками Dataformat.er.
1. DataFormat.Error: мы достигли конца буфера
Проверьте размер файла
- Если ошибка возникает при попытке импортировать данные из нескольких файлов одновременно, это может быть связано с проблемами с размером файла.
- Проверьте размер файла JSON, чтобы убедиться, что он не связан с размером вашего файла.
Подожди, подожди и подожди!
- Если это временная проблема, то нет смысла пытаться устранить проблему вне вашей зоны комфорта.
- Пользователи сообщают, что ошибка формата данных была устранена автоматически через день или два.
- Итак, обратитесь в службу поддержки Power BI, если проблема подходит к концу.
Если проблема не устранена, выполните следующие действия.
- Если вы делаете PowerQuery, попробуйте отказаться от него и настроить промежуточную таблицу в базе данных SQL, которая анализирует JSON с помощью T-SQL.
2. Power BI dataformat.error внешняя таблица не в ожидаемом формате
Сохраните файл в Excel
- Если вы пытаетесь использовать файл Excel, импортированный из стороннего программного обеспечения, такого как бухгалтерское программное обеспечение, то в нем могут быть незначительные ошибки схемы XML.
- Хотя эти ошибки могут игнорироваться приложением Excel, но это приводит к ошибке при использовании с Power Query.
- Одним из способов решения этой проблемы является открытие проблемного файла Excel в приложении Excel и его повторное сохранение.
- Теперь импортируйте тот же файл в Power Query и проверьте, не возникает ли ошибка снова.
- Это может занять много времени, если у вас есть много файлов для работы. Однако, в качестве обходного пути, вы можете решить проблему, пока не будет найдено надежное исправление.
Изменить тип в прикладных шагах
Если проблема не устранена, попробуйте удалить начальный измененный тип данных для даты из числа в текст.
При обновлении данных могут возникать ошибки двух типов.
Удаленного Однако ошибки, которые приходят из удаленного внешнего источника данных, являются другими. Произошла что-то в системе, которая может быть на улице, на полпути по всему миру или в облаке. Для таких типов ошибок требуется другой подход. Распространенные удаленные ошибки:
Не удалось подключиться к службе или ресурсу. Проверьте подключение.
Не удалось найти файл, к который вы пытаетесь получить доступ.
Сервер не отвечает и, возможно, находится в состоянии обслуживания.
Это содержимое не доступно. Возможно, он был удален или временно недоступен.
Подождите. данные загружаются.
Получить сведения о справке На сайте Office справки и обучения. Она не только содержит большой объем справки, но и сведения об устранении неполадок. Дополнительные сведения см. в устранении и обходных решениях недавних проблем в Excel для Windows.
Использование технического сообщества Используйте веб-Community Майкрософт для поиска обсуждений, относящихся к вашей проблеме. Весьма вероятно, что вы не первый, кто испытывает проблему, другие люди занимаются ее решением и даже могут найти решение. Дополнительные сведения см. в Microsoft Excel Community и Office Answers Community.
Поиск в Интернете Используйте предпочитаемую поисковую система для поиска дополнительных сайтов в Интернете, которые могут предоставлять обсуждения или подсказки. Это может быть отнимает много времени, но это может привести к более широкой сети для того, чтобы найти ответы на наиболее сложные вопросы.
Обратитесь в Office поддержки На этом этапе, скорее всего, вы понимаете проблему гораздо лучше. Это поможет вам сосредоточиться на беседе и сократить время, затраченное на поддержку Майкрософт. Дополнительные сведения см. в Microsoft 365 и Office службе поддержки клиентов.
Возможно, вам не удастся устранить проблему, но вы можете точно определить, в чем заключается проблема, чтобы помочь другим понять ситуацию и решить ее за вас.
Проблемы со службами и серверами Скорее всего, причина — периодические ошибки сети и связи. Лучше всего подождите и попробуйте еще раз. Иногда проблема просто утихает.
Изменения расположения или доступности База данных или файл были перемещены, повреждены, переведены в автономный режим на обслуживание или аварийно сбой базы данных. Дисковые устройства могут быть повреждены, а файлы будут потеряны. Дополнительные сведения см. в этой Windows 10.
Изменения в проверке подлинности и конфиденциальности Неожиданно может произойти, что разрешение больше не работает или в параметр конфиденциальности было внося изменение. Оба события могут препятствовать доступу к внешнему источнику данных. Обратитесь к администратору или администратору внешнего источника данных, чтобы узнать, что изменилось. Дополнительные сведения см. в настройкахи разрешениях источника данных и Настройка уровней конфиденциальности.
Открытые или заблокированные файлы Если открыт текст, CSV или книга, изменения, внесенные в файл, не включаются в обновление до тех пор, пока файл не будет сохранен. Кроме того, если файл открыт, он может быть заблокирован и к нему нельзя получить доступ, пока он не будет закрыт. Это может произойти, если другой человек использует версию Excel. Попросите их закрыть файл или проверить его. Дополнительные сведения см. в статьи Разблокировкафайла, заблокированного для редактирования.
Изменения схем на заднем Кто-то изменяет имя таблицы, имя столбца или тип данных. Это почти никогда не разумно, может иметь огромное влияние и особенно опасно для баз данных. Одной из них является то, что группа управления базами данных наила правильные средства контроля, чтобы избежать этого, но происходят спапцы.
Блокирование ошибок при сложении запросов Power Query пытается повысить производительность, когда это возможно. Для более производительности и емкости часто бывает лучше выполнить запрос к базе данных на сервере. Этот процесс называется сгибом запроса. Тем не менее Power Query блокирует запрос, если существует вероятность компрометации данных. Например, слияние определено между таблицей книги и SQL Server таблицей. Для конфиденциальности данных книги за установлено SQL Server конфиденциальность данных организации. Поскольку политика конфиденциальности является более строгой, чем в организации, Power Query блокирует обмен информацией между источниками данных. Сгиб запроса происходит за кадром, поэтому вас может удивить, когда возникает ошибка блокировки. Дополнительные сведения см. взадачах Основные сведения о сгибе запросов, Сгибзапросов и Сгиб с помощью диагностики запросов.
Часто с помощью Power Query вы можете точно определить, в чем заключается проблема, и устранить ее самостоятельно.
Переименованные таблицы и столбцы Изменения исходных имен таблиц и столбцов или столбцов почти наверняка приводят к проблемам при обновлении данных. Запросы используют имена таблиц и столбцов для формировать данные практически на каждом этапе. Не изменяйте или удаляйте исходные имена таблиц и столбцов, если только их не нужно использовать в источнике данных.
Изменения типов данных Изменение типа данных иногда может привести к ошибкам или непредвиденным результатам, особенно в функциях, для которых в аргументах требуется определенный тип данных. Примерами могут быть замена текстового типа данных в числовой функции или попытка вычисления с нечисловой типом данных. Дополнительные сведения см. в теме Добавление и изменение типов данных.
Преобразования Вы попытались преобразовать ячейку, содержащую 0, в целое число.
Математические Вы пытаетесь умножить текстовое значение на числовое значение.
Объединения Вы попытались объединить строки, но одна из них числовая.
Безопасно экспериментируйте и итерации Если вы не уверены, что преобразование может иметь отрицательное влияние, скопируйте запрос, проверьте изменения и итерации с помощью вариантов команды Power Query. Если команда не работает, просто удалите созданное вами шаг и попробуйте еще раз. Чтобы быстро создать образец данных с одной схемой и структурой, создайте Excel таблицу из нескольких столбцов и строк и импортировать их (выберите данные > Из таблицы илидиапазона). Дополнительные сведения см. в таблицах Создание таблицы и Импорт из Excel таблицы.
Когда вы впервые будете понять, что можно делать с данными в редакторе Power Query, вам может показаться, что вы ребенок в конфетном магазине. Но не хочется есть все конфеты. Вы хотите избежать преобразования, которое может непреднамеренно вызывать ошибки обновления. Некоторые операции, например перемещение столбцов в другое место таблицы, не должны приводить к ошибкам в обновлении, так как Power Query отслеживает столбцы по их именам.
Другие операции могут привести к ошибкам обновления. Одним из общих правил может быть ваш световой свет. Не внося существенных изменений в исходные столбцы. Чтобы безопасно воспроизвести столбец, скопируйте исходный столбец с командой(Добавитьстолбец, Настраиваемый столбец, Дублировать столбец и так далее), а затем внести изменения в скопированную версию исходного столбца. Вот операции, которые иногда могут привести к ошибкам обновления, и некоторые из лучших методик, которые помогут ухладить работу.
Повышение эффективности за счет максимально ранней фильтрации данных в запросе и удаления ненужных данных для уменьшения лишней обработки. Кроме того, с помощью автофильтра можно искать или выбирать определенные значения, а также использовать фильтры для определенных типов, доступные в столбцах даты, даты и времени и времени (например, Месяц,Неделя,День).
Типы данных и заглавные колонок столбцов
Power Query автоматически добавляет в запрос два шага сразу после первого шага: "Продвиганые заглавные колонок", которая преобразует первую строку таблицы в заглавный, и Changed Type(Измененный тип), который преобразует значения из типа Данных Any в тип данных на основе проверки значений из каждого столбца. Это удобно, но иногда может потребоваться явно контролировать это поведение, чтобы предотвратить ошибки случайного обновления.
Дополнительные сведения см. в статьях Добавление и изменение типов данных и Повысить или понизить их в строках и столбцах.
Переименование столбца
Избегайте переименования исходных столбцов. Используйте команду Переименовать для столбцов, добавленных другими командами или действиями.
Разделить столбец
Разделение копий исходного столбца, а не исходного столбца.
Объединение столбцов
Объединять копии исходных столбцов, а не исходных.
Удаление столбца
Если нужно сохранить небольшое количество столбцов, используйте выбор столбца, чтобы сохранить нужные.
Рассмотрим разницу между удалением столбцов и удалением других столбцов. Когда вы удаляете другие столбцы и обновляете данные, новые столбцы, добавленные в источник данных после последнего обновления, могут остаться незащищенными, так как они будут считаться другими столбцы при повторном выполнении в запросе шага Удалить столбец. Такая ситуация не возникает при явном удалите столбец.
Наконечник Скрыть столбец (как в Excel) не Excel. Однако если у вас много столбцов и вы хотите скрыть многие из них, чтобы сосредоточиться на своей работе, вы можете сделать следующее: удалить столбцы, запомнить созданный шаг, а затем удалить его перед загрузкой запроса обратно на таблицу.
Дополнительные сведения см. в статье Удаление столбцов.
Замена значения
При замене значения источник данных не редактируется. Вместо этого нужно изменить значения в запросе. При следующем обновлении данных ищемые значения могут немного измениться или перестать быть там, поэтому команда Заменить может не работать так, как планировалось изначально.
Дополнительные сведения см. в области Замена значений.
Pivot и Unpivot
При использовании команды Столбец сводной сводной столбца при сводном столбце может возникнуть ошибка, при этом не агрегируются значения, но возвращается больше одного значения. Такая ситуация может возникнуть после операции обновления, которая меняет данные несмежным образом.
Используйте команду Открепить другие столбцы, если известны не все столбцы и вы хотите, чтобы новые столбцы, добавленные во время обновления, также были неотвечены.
Используйте команду Открепить только выбранный столбец, если вы не знаете количество столбцов в источнике данных и хотите, чтобы выбранные столбцы оставались неотвеченными после обновления.
Дополнительные сведения см. в статьях Сводные столбцы и Ото всех столбцов.
Предотвращение ошибок Если внешним источником данных управляет другая группа в организации, им необходимо знать о вашей зависимости от них и избегать изменений в их системах, которые могут привести к проблемам ниже. Фиксировать влияние на данные, отчеты, диаграммы и другие артефакты, которые зависят от данных. Настройте линии связи, чтобы убедиться в том, что они понимают последствия, и примите необходимые меры, чтобы обеспечить бесперебойную работу. Находите способы создания элементов управления, которые минимизируют ненужные изменения и предугадать последствия необходимых изменений. Конечно, это легко сказать и иногда сложно сделать.
Future-proof with query parameters Используйте параметры запроса для уменьшения изменений, например расположения данных. Вы можете создать параметр запроса, чтобы заменить новое расположение, например путь к папке, имя файла или URL-адрес. Существуют и другие способы уменьшения проблем с помощью параметров запроса. Дополнительные сведения см. в теме Создание запроса с параметрами.
Анализ ошибок Power Query, обработка исключений (ошибка)
Power Query M Язык, использование error Указывает на ненормальную ошибку при обработке выражения вычисления.
M Исключения в языке обычно возникают из-за того, что операторы и функции сталкиваются с ошибками или используют неверные выражения.
1. Распространенные ошибки Power Query
(1) Функция ввела неверный параметр: Expression.Error: невозможно преобразовать значение XXX в тип XXX.
(2) Ошибка типа выражения: Expression.Error: невозможно применить оператор XXX к типам XXX и XXX.
Выражение + используется для сложения чисел, но следующий пример используется для добавления текста, вызывающего ошибку.
(3) Имя не может быть распознано (регистр и орфографические ошибки): Expression.Error: Имя XXX не может быть распознано
По сравнению с именем функции в Common Errors (1) имя функции number.totext введено неправильно в зависимости от регистра, что приводит к ошибкам.
(4) Ошибка преобразования данных: DataFormat.Error: невозможно преобразовать в XXX
Столбец суммы продаж преобразован в числовой тип, но он содержит текстовые данные, что вызывает ошибку.
2. Обработка исключений на языке M (Ошибка)
можно использовать try Выражения для тестирования, анализа и обработки исключений ошибок. При возникновении ошибки try В выражении можно указать значение, которое может использоваться для указания причины ошибки.
（ 1 ） Try Структура выражения:
tryResult = try Заявления, которые могут быть ненормальными otherwise Значение результата, когда произошла ошибка
（ 2 ） try Результат, возвращаемый выражением
Когда ошибка не возникла
Try Вернуть запись record С полями HasError 、 Value 。
из их HasError Ценность FALSE ， Value Да" Заявления, которые могут быть ненормальными "Результат расчета
Когда возникает ошибка
потому как “ считать 1” Столбец не существует, ошибка вычисления выражения.
Try Вернуть запись record С полями HasError 、 Error 。
из их HasError Ценность TRUE ， Error Это рекорд record
из их Message В поле указана неверная информация (она уже запрошена на китайском, как правило, более понятная)
（ 3 ) Словосочетание otherwise Время, try Результат, возвращаемый выражением
Когда ошибка не возникла
Возвращается результат вычисления «утверждения, которое может быть ненормальным».
Без совпадения otherwise из try По сравнению с оператором, нет необходимости передавать возвращаемый результат Value Прочтите поле еще раз. Выражение предложения проще и компактнее.
Когда возникает ошибка
потому как “ считать 1” Столбец не существует, ошибка вычисления выражения.
Результат расчета возвращен otherwise После значения.
три, использовать try Исключение обработки выражения
(1) Повышение отказоустойчивости кода
В Power Query с помощью приведенных выше выражений try исключения могут создаваться и обрабатываться.
(2) Пользовательское исключение выброса
В соответствии с фактическим использованием вы можете настроить генерируемую информацию об исключении, просто используйте ошибку и формат записи.
Читайте также: