Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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.
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
@v-shex-msft
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?
Hi @MarcelBeug,
I improve the function to load all excel files to one table.
let LoadAllExcelFile= (FilePath as text) as table => let Source = Folder.Files(FilePath), #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"), Custom = Table.SelectColumns(Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content])),"Custom"), #"Expanded Custom" = Table.ExpandTableColumn(Custom, "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}) in #"Expanded Custom" in LoadAllExcelFile
Result:
Regards,
Xiaoxin sheng
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.
@v-shex-msft
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.
This post gets rejected when I include the link to the video, but you can still access it via post #7 in the linked post in the Dutch forum.
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).
@v-shex-msft
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).
@v-shex-msft
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).
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.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |