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