topic Problem updating Excel file with API REST in Developer

Problem updating Excel file with API REST

jerome_dal — Wed, 13 Apr 2022 07:37:52 GMT

Hello,

I want to upload an Excel/CSV file on an on-premise PBI portal using the API REST.

I'm able to upload a new file, but cannot succeed in overwriting it.

I use On-Premise Power BI Report Server Version 1.8.7485.35104 (May 2020).

Is there any limitation on API REST with the On-Premise version?

I did some tests using the PowerShell exemple, and the documentation on the SwaggerHub.

This PS code works well for a new file:

$ReportPortalUri = 'myreportportal'
$catalogItemsUri = $ReportPortalUri + "/api/v2.0/ExcelWorkbooks"
$uploadItemPath = 'myfile'

$bytes = [System.IO.File]::ReadAllBytes($uploadItemPath)
$payload = @{
"@odata.type" = "#Model.ExcelWorkbook";
"Content" = [System.Convert]::ToBase64String($bytes);
"ContentType"="";
"Name" = 'myfilename';
"Path" = 'myfilepath';
} | ConvertTo-Json
Invoke-WebRequest -Uri $catalogItemsUri -Method Post -Body $payload -ContentType "application/json" -UseDefaultCredentials

Then, I did some tests using the PATCH method and the GUID of the file.

...

$catalogItemsUri = $ReportPortalUri + "/api/v2.0/ExcelWorkbooks" + "(myguid)"

...

Invoke-RestMethod -Uri $catalogItemsUri -Method Patch -Body $payload -ContentType 'application/json' -UseDefaultCredentials -UseBasicParsing -Verbose

This works when I use simple attributes, such as "Description" in the $payload

But this does not work when I use the "Content" attribute, the same way as for a new file : I got error "System.InvalidOperationException: Nullable object must have a value". What am I missing here?

I also did some tests using the POST method on Model.Upload function with multipart/form-data content type.

But I got error "Unsupported Media Type". What am I missing here?

Any exemple on how to use the Model.Upload function?

Any feedback on using the API REST with a On-Premise version?

Re: Problem updating Excel file with API REST

Anonymous — Mon, 18 Apr 2022 07:48:36 GMT

Hi @jerome_dal ,

According to the offical blog, I think Power BI only support us to import .xlsx file from OneDrive for Business.

Supported content:

To import an .xlsx file from OneDrive for Business, include the content type application/json in the request headers. Include ImportInfo with filePath set to the .xlsx file path in the request body.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Problem updating Excel file with API REST

jerome_dal — Wed, 04 May 2022 13:28:58 GMT

Hello,

It's possible to import Excel/CSV files with the On Premise version. There was a bug with the "May 2020" version preventing the upload with overwrite.

I tested with "January 2022" version and it is ok now for the PATCH method on ExcelWorkbooks(guid).

But I still have problem using the POST method on ExcelWorkbooks(guid)/Model.Upload to handle with files larger than 25Mb. I get a "(415) Unsupported Media Type" from the report server.

Any idea?

Does anyone have an exemple of code using method ExcelWorkbooks(guid)/Model.Upload?