Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lekkerbek
Helper IV
Helper IV

Importing data from folder

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?

 

 

Knipsel.PNG

1 ACCEPTED 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.

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

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:

 

Capture2.PNG

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@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?

Specializing in Power Query Formula Language (M)

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:

Capture.PNG

 

Regards,

Xiaoxin sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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).

Specializing in Power Query Formula Language (M)

@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).

Specializing in Power Query Formula Language (M)

@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).

Specializing in Power Query Formula Language (M)

@v-shex-msft

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.

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.