Forum Discussion
Data format error. external table is no tin the expected format for (.xls file format)
Thank you for your quick response,
As per your suggestion, I tried to import xls file from text/csv file. then it showed empty folder.
Below is Mcode while importing .xls file from SharePoint:-
I have tried few ways to import xls file from sharepoint.
I have created empty xls files and saved in sharepoint and imported them. it was successfull. So i used that Mcode renamed files and tried to import. then it gave similar error.
below is the Mcode I used
the error started at "Invoke Custom Function1"
ok, I see ur still using basic sharepoint import.
as I stated, without the exact file it is hard towork on.
So what you can do is to create CSV.Document() command for exact same file (import from text / csv) see what code it generated (the square brackets CSV paraameters)
then what you need to do is to combine formulas in Mcode:
it would look sth like this:
CSV.Document(Sharepoint.files( ... ), [Parameters] )
and try it this way.
Thank you Anonymous for your suggestion.
Below is Mcode from csv file importing.
let
Source = SharePoint.Files("https://sharepoint.com/teams/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Dashboard 2021 has completed") and [Extension] = ".csv"),
#"Dashboard 2021 has completed csv_https://sharepoint com/teams/Report - Flow/Excel_file_Renamed/" = #"Filtered Rows"{[Name="Dashboard 2021 has completed.csv",#"Folder Path"="https://sharepoint.com/teams/Report - Flow/Excel_file_Renamed/"]}[Content],
#"Imported CSV" = Csv.Document(#"Dashboard 2021 has completed csv_https:// sharepoint com/teams/Report - Flow/Excel_file_Renamed/",[Delimiter=";", Columns=44, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}})
in
#"Changed Type"
Below is the Mcode for .xls file, I have replaced [Parameter] values of csv in .xls file. it gave belo error. If i have replaced the parameter wrong, please guide me.
let
Source = SharePoint.Files("https:// sharepoint.com/teams /", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], " Dashboard") and [Extension] = ".xls"),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Name] = " Dashboard 2021 has completed.xls" )),
#" Dashboard 2021 has completed xls_https:// sharepoint com/teams/ Report - Flow/Excel_file_Renamed/" = #"Filtered Rows1"{[Name=" Dashboard 2021 has completed.xls",#"Folder Path"="https://sharepoint.com/teams/ Report - Flow/Excel_file_Renamed/"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"Dashboard 2021 has completed xls_https:// sharepoint com/teams/ Report - Flow/Excel_file_Renamed/",[Delimiter=";", Columns=44, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
#"Imported Excel Workbook"