Importing .xls Files in PQ

[cmajka]

Hi All -

I'm trying pull in the contents of a folder on my network drive, all of which are .xls files. I am using Excel 2016 and the formula =Excel.Workbook([Contents]). However, it is throwing the following error message:

"Expression.Error: The field 'Contents' of the record wasn't found.
Details:
Content=Binary
Name=workbookname.xls"

Any suggestions on what is causing this?

Thanks

 

[gazpage]

Not quite sure I understand what you are trying to do. To combine a bunch of excel files from a folder just go to Get Data -> File -> From Folder and choose the folder. Expand the column of tables and then if needed make changes in the sample query created.

 

[cmajka]

Yes, correct, that is what I was trying to do. However, it would give me the above mentioned error. I saved the files as .xlsx and the PQ import worked just fine, but it's an extra step that I was hoping not to take.

 

[gazpage]

// FilePathFull
"C:\Users\gapage\Desktop\PQ\First.xls" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]
// ImportExcel
let
    Source = Excel.Workbook(File.Contents(FilePathFull), null, true),
    Sheet2 = Source{[Name="Sheet1"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Apple", Int64.Type}, {"Pear", Int64.Type}})
in
    #"Changed Type"
// fnImportExcel
let
    Source = (FilePathFull as text) => let
    Source = Excel.Workbook(File.Contents(FilePathFull), null, true),
    Sheet2 = Source{[Name="Sheet1"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Apple", Int64.Type}, {"Pear", Int64.Type}})
in
    #"Changed Type"
in
    Source
// PQ (3)
let
    Source = Folder.Files("C:\Users\gapage\Desktop\PQ"),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Content"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Folder Path", "Name"}),
    #"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FullPath"),
    #"Invoked Custom Function" = Table.AddColumn(#"Merged Columns", "fnImportExcel", each fnImportExcel([FullPath])),
    #"Expanded fnImportExcel" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnImportExcel", {"Apple", "Pear"}, {"Apple", "Pear"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded fnImportExcel",{"FullPath"})
in
    #"Removed Columns1"

 

[gazpage]

Yes, correct, that is what I was trying to do. However, it would give me the above mentioned error. I saved the files as .xlsx and the PQ import worked just fine, but it's an extra step that I was hoping not to take.

I'm not sure I've ever imported .xls files before, now that I think about it. The 'combine' methodology wasn't working for me at all. I've gone old school and created a function to apply to each file in the folder. Seems to work fine like this for me.

 

[cmajka]

I think there is something else going on here - from what I've read, importing.xls into PQ shouldn't be an issue. When I open the.xls files the client sent me, just in Excel, I get a pop up message stating "The file format and extension of filename.xls don't match. The file could be corrupted or unsafe. Unless you trust the source, don't open it. Do you want to open it anyway?"

The fact that this message pops up on each file they send us tells me they are likely causing the issue when they export it or save it. I think the pop up is what's causing PQ to not want to import it. Well, as mentioned, the .xlsx version works fine, so just one extra step on my end. Thanks for looking into this.

 

[SpillerBD]

I believe you have to use this method for xls files

#"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each Excel.Workbook([Content])),