topic Re: Creating .xlsx Files For Use in Power BI in Microsoft Integration with SAS

Creating .xlsx Files For Use in Power BI

debbiem — Tue, 26 Oct 2021 16:19:55 GMT

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]

Re: Creating .xlsx Files For Use in Power BI

Reeza — Tue, 26 Oct 2021 16:29:03 GMT

Try it with a very basic data set and see if it generates the same issues. If not, it's more related to the data than to the file type SAS is creating. If it still errors out with a basic export then its how the file is generated or transferred.

This will allow you to identify the source of the issue more clearly to start debugging it.

proc export data=sashelp.cars

outfile="/vol3/data/userid/output/fcr/cars.xlsx"

dbms=xlsx replace;
RUN;

Re: Creating .xlsx Files For Use in Power BI

debbiem — Tue, 26 Oct 2021 16:46:00 GMT

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.

Re: Creating .xlsx Files For Use in Power BI

Reeza — Tue, 26 Oct 2021 16:47:52 GMT

In your WINSCP transfer try setting it as a binary file transfer and see if you still have issues.

Re: Creating .xlsx Files For Use in Power BI

debbiem — Tue, 26 Oct 2021 16:51:58 GMT

Yes, it is set to binary.

Re: Creating .xlsx Files For Use in Power BI

Reeza — Tue, 26 Oct 2021 18:08:49 GMT

Some googling shows this may be a PowerBI problem...I've seen this issue with other SAS generated files being pushed to other systems but if it's binary transferred and Excel is reading it fine, I think the file is being created properly.

There is a VBS script you can use to automate the open/save as but I would also be looking for a better solution myself. Good luck finding a resolution!

Re: Creating .xlsx Files For Use in Power BI

debbiem — Tue, 26 Oct 2021 20:27:54 GMT

Thanks for your suggestions. I'll post my final solution.

Re: Creating .xlsx Files For Use in Power BI

SASKiwi — Tue, 26 Oct 2021 23:12:56 GMT

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.

Re: Creating .xlsx Files For Use in Power BI

debbiem — Thu, 28 Oct 2021 13:06:03 GMT

Thanks for the suggestion. We're exploring this option and will post results asap.

Re: Creating .xlsx Files For Use in Power BI

Sajid01 — Sun, 03 Jul 2022 01:30:53 GMT

Looks like this xlsx file has only one sheet. Try exporting the data to csv. Power BI should have no issue opening it.