Forum Discussion
Data format error. external table is no tin the expected format for (.xls file format)
Hello,
from power automate every day a file is created in .xls format.
and this file is connected o power query editor(other excel) in sharepoint. But if I save same file in .xlsx format it works. is there any possiblity to automate the process?
So is there a way to solve this error.
9 Replies
Hi there!
Shame that you didnt post Mcode for this query.
Without the code I can just give you this advise:
Power automate: since it is creating the file, surly there must be a way to save it as XLSX.
If this for any reason is not doable:
in power query, you need to change your M code a bit.
This Mcode works with XSLX file right?
This means the query is using to formulas: Excel.Workbook(FileContent( ... ))
However in my expierience with XLS file it actualy needs CSV.Document formula.
So:
1. Open blank work book.
2. using power query - import this XLS filee using from text/ CSV
3. Copy the formula from formula bar from power Query.
4. Close and Save.
5. go into your file with the error.
6. your current importing formula (probobly called Source -> enter the formula bar.
7. Replace current formula with:
try (Current formula) otherwise NewCopiedformula
Im sorry but without more info / code i cannot do more.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"
hmmm,
Question:
in the first querry you have imported CSV file into power query.
in the second code you want to import XLS file thats acualy the CSV file from query before.
so: why cant you reference the CSV importing query?
Since you have excel file that is importing CSV file, you can either connect directly to that XLSX file / Power BI file instead of trying to import again xls file.
In PQ in1 file there is no need to doubly import files like this.
however, if the CSV importing file is in separete workbook, you can create connection to that XLSX / power BI file tothat specific query resultAnonymous ,
Regarding the point you mentioned so: why cant you reference the CSV importing query?, I didnot understand (do you mean, while importing .xls file, can I refer it to csv importing query?).
Firstly this .xls file is extracted and saved in sharepoint every day using power automate flow.
To import xlsx or csv file in power query, I have to manually save xls file as xlsx or csv. to avoid this manual work, I am looking for a solution to import xls file which is sharepoint.
This is the reason, I cannot import xlsx or csv format file in power qery in this scenario.
secondly the data is not in dataverse, it is a workbook file in sahrepoint folder.
ok let me try this way:
You posted a code that is importing data from CSV file: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"
This is Mcode, which means it can be found in 1 of 2 places:
1. Excel file
2. Power BI file
So if for any reason you need this refference in atnoher excel file, you can simply use Excel.Workbook(Folder.Files())
or excel workbook (sharepoint.files() )
also the formula CSV.Document do not have point to CSV files alone but to txt and XLS files also
