Forum Discussion

lekkerbek's avatar
lekkerbek
Helper IV
9 years ago
Solved

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 ...
Need Help
  • lekkerbek's avatar
    lekkerbek
    9 years ago

    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.

Anonymous's avatar
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's avatar
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).