I created .xlsx files using SAS on a Linux platform. The files are then FTP'd to Windows and read in Power BI. If the files are not opened in Excel and saved then Power BI cannot read the files and generates an error. Has anyone run into this issue and how did you resolve it? Thx!
Here is the SAS code:
proc export data=agt_detail2(drop=csr_id deptname phone_fcr_agent_end_time fox_fcr_exclude_reason)
outfile="/vol3/data/userid/output/fcr/Excluded Calls &runmon. &crm_yr. v7.xlsx"
dbms=xlsx replace;* label;
sheet = "Agent Detail";
Here is the Power BI error:
An error occurred in the ‘Transform File (2)’ query. DataFormat.Error: The document cannot be opened because there is an invalid part with an unexpected content type.
[Part Uri=/xl/worksheets/sheet2.xml],
[Content Type=application/xml],
[Expected Content Type=application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml].
Details:
[Binary]
Thank you for your suggestions. The output file contains basic data and the .xlsx file is created using PROC EXPORT. I am using winSCP as the transfer protocol. Once the file is on a Windows server, it can be opened in Excel without any issues. The goal is to directly read the .xlsx file into Power BI without having to manually open and resave it though. I was wondering if anyone has experienced a similar issue with Power BI.
Yes, it is set to binary.
Thanks for your suggestions. I'll post my final solution.
It's a lot easier to port SAS data to Power BI via MS SQL Server then you don't have to worry about spreadsheet translation issues. That's how we do it anyway.
Thanks for the suggestion. We're exploring this option and will post results asap.
Looks like this xlsx file has only one sheet. Try exporting the data to csv. Power BI should have no issue opening it.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!