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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • lekkerbek's avatar
      lekkerbek
      Helper IV
      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.