Creating .xlsx Files For Use in Power BI

[debbiem]

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]

[Reeza]

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;

[debbiem]

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.

[Reeza]

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

[debbiem]

Yes, it is set to binary.

[Reeza]

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!

[debbiem]

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

[SASKiwi]

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.

[debbiem]

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

[Sajid01]

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