Hi, I try to import around 30 excelsheets by selecting a folder. Each sheet has one or multiple tables. At first the import is succesful (an overview of all sheets). When I go deeper and try to extract the tables from the various sheets at first I got the error saying that I needed Access Engine 2010 X64. After installing that I get the following error: Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataFormat.Error] ? (then a strange Japanese or Chinese sign). '. Hope anybody knows what I mean?

Thanks for your suggestion. The basic functionality (read from folder) works as it should. The problem was something else as it appeared. I use Parallels for Mac to use Windows/Excel on my Mac. The files were stored on iCloud Drive. This combination turned out to be the problem. Once I transferred the files to c:\temp and tried it from there, the issues were gone.

Importing data from folder | Microsoft Fabric Community

8 Replies

Anonymous
Not applicable
9 years ago
Hi lekkerbek,

If you want to load file from folder, you can try to use below method:

let loadSource=(folderPath as text,fileName as text,tableName as text) as table => let Source=Folder.Files(folderPath), file = Source{[#"Folder Path"=folderPath,Name=fileName]}[Content], #"Imported Excel" = Excel.Workbook(file), Sheet = #"Imported Excel"{[Item=tableName,Kind="Sheet"]}[Data], PromoteHeaders = Table.PromoteHeaders(Sheet) in PromoteHeaders in loadSource

Use:

Regards,
Xiaoxin Sheng
- MarcelBeug
  Community Champion
  9 years ago
  Anonymous
  For importing tables from an Excel sheet, your method won't work (Kind = "Sheet").
  What would the additional value be of your method, compared to the regular " files from folder" functionality, as your method reads only 1 sheet from 1 Excel file?
  
  Otherwise lekkerbek also posted a similar question on a Dutch forum (although not mentioning the technical issues he encountered).
  
  And I created this video for him in Dutch (although with the English version of Power BI Desktop):
  
  For people not understanding Dutch, in the video the following steps are illustrated::
  - files are read from a folder
  - only Ecel files are read starting with "Leverancier" (and I illustrate this would also prevent issues from a file being open in Excel)
  - then "combine binaries" is applied (the version that was introduced in November 2016), selecting all objects as each file may contain multiple tables,
  - the exampe query is adjusted for filtering: first only tables are selected, next any tables with a name ending with "_totaal" are excluded, but only if the file contains multiples tables,
  - then I show that the adjustments in the sample query are automatically copied into the function that is used for converting all files to tables.
  - after some finishing touches I suggest to use "Detect Data Type" on the "Transform" tab to apply data types to the columns (that must be selected first), stating that these data types might still need to be verified / corrected.
- MarcelBeug
  Community Champion
  9 years ago
  Anonymous
  Your method won't work as it reads a worksheet from 1 Excel file rather then multiple tables from multiple files in a folder.
  What would be the added value of your method?
  
  Otherwise lekkerbek posted a similar question on a Dutch forum:
  http://www.helpmij.nl/forum/showthread.php/909171-Query-bestanden-in-een-map-ophalen
  and I created a video for him (in Dutch) explaining step by step how files are to be read from a folder.
  For people not understanding Dutch: you can still follow along if you know that:
  - Files are read from a folder,
  - These are filtered on type ".xlsx" and the name must start with "Leverancier" (also to prevent issues with Excel files being open),
  - "Combine binaries" is applied (the version introduced in November 2016), with selection of all objects,
  - The example query is adjusted to filter on tables and
  to exclude table with a name ending with " _totaal", but only if the file contains multiple tables,
  - The function is displayed (not adjusted) to show that the adjustments in the example query are automatically promoted to the function,
  - As finishing touches:
  columns are selected,
  tables are expanded and
  data types are applied by using "Detect data type" on the "Transform" tab for selected columns
  (applied data types to be verified and corrected if so required).
- MarcelBeug
  Community Champion
  9 years ago
  Anonymous
  Your method won't work as it reads a worksheet from 1 Excel file rather then multiple tables from multiple files in a folder.
  What would be the added value of your method?
  
  Otherwise lekkerbek posted a similar question on a Dutch forum (although not mentioning the technical issues he encountered):
  http://www.helpmij.nl/forum/showthread.php/909171-Query-bestanden-in-een-map-ophalen
  
  and I created a video for him (in Dutch) explaining step by step how files are to be read from a folder.
  https://youtu.be/gK1QmTj5UFo
  
  For people not understanding Dutch you can still follow along if you know that:
  - Files are read from a folder,
  - These are filtered on type ".xlsx" and the name must start with "Leverancier" (also to prevent issues with Excel files being open),
  - "Combine binaries" is applied (the version introduced in November 2016), with selection of all objects,
  - The example query is adjusted to filter on tables and
  to exclude table with a name ending with " _totaal", but only if the file contains multiple tables,
  - The function is displayed (not adjusted) to show that the adjustments in the example query are automatically promoted to the function,
  - As finishing touches:
  columns are selected,
  tables are expanded and
  data types are applied by using "Detect data type" on the "Transform" tab for selected columns
  (applied data types to be verified and corrected if so required).