Importing an Excel file using the Import API retur...

michael_e · ‎02-09-2017

Has anyone sucessfully uploaded a local Excel file using the Create Import API Operations? If so, could you share your method?

We are referencing https://msdn.microsoft.com/en-us/library/mt243840.aspx

When we try to use the Create Import API operation to import a local Excel file we are receiving a 400 Bad Request response with an empty message.

We have succesfully used Dataset, Table and Row operations in the PowerBi API so the authorization is set up correctly.

We can import the Excel file manually (through the PowerBI web site) so we know that it is valid.

This is the request we are sending to https://api.powerbi.com

POST /v1.0/myorg/imports HTTP/1.1
Authorization: Bearer <auth-token removed>
datasetDisplayName: TEST_EXCEL_DATASET
nameConflict: Overwrite
User-Agent: Jakarta Commons-HttpClient/3.0.1
Host: api.powerbi.com
Content-Length: 32173
Content-Type: multipart/form-data; boundary=l0mHm36RY5odjNybx3vEqjiq1oJqBsA7v4bbB0n

--l0mHm36RY5odjNybx3vEqjiq1oJqBsA7v4bbB0n
Content-Disposition: form-data; name="TEST_DATASET"; filename="PROPERTIES_RESOURCES_TABLE.xlsx"
Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=ISO-8859-1
Content-Transfer-Encoding: binary

PK�uGJdocProps/core.xml��MK�0��!�v�vq��]DY�(�B:��$��ߛ�ţ��ä��H�ѺA�� >4��n�(*��ڠ�:Z�U��N�(�KB�B��>��+�2��@��-��,:z��Ҽ�q�pD��;H��Y�V�?s��{7,�4Mɔ�\�(��ۛ�y�xP�s%��!��^��cK��+��<�W͖�K�1�b�nX^�VE�~.�W��<��,=��]��~~]}PK��PK�uGJ_rels/.rels��N�0

...

�T��n��_��PK�[h��PK�uGJ��docProps/core.xmlPK�uGJW(^#�FD_rels/.relsPK�uGJ�p��=�`[Content_Types].xmlPK�uGJ�|wؑ��docProps/app.xmlPK�uGJp��&x��xl/sharedStrings.xmlPK�uGJ�2�+}E
gxl/styles.xmlPK�uGJ3�u��xl/workbook.xmlPK�uGJ�i+��Ixl/_rels/workbook.xml.relsPK�uGJ$v��;ncOm xl/worksheets/sheet1.xmlPK�uGJ�[h��wxl/worksheets/sheet2.xmlPK

��y
--l0mHm36RY5odjNybx3vEqjiq1oJqBsA7v4bbB0n--

and the response we are getting back:

HTTP/1.1 400 Bad Request
Content-Length: 14
Content-Type: application/json; charset=utf-8
Server: Microsoft-HTTPAPI/2.0
Strict-Transport-Security: max-age=31536000; includeSubDomains
X-Frame-Options: deny
X-Content-Type-Options: nosniff
RequestId: 331a2751-61a3-4190-8ac3-f18752bbb5b6
Date: Wed, 08 Feb 2017 05:57:05 GMT

{"Message":""}

The only related posts in the forum are for a period of time when the API was unavailable - which is not the case now.

Any suggestions on how to proceed would be helpful.

Eric_Zhang · ‎02-10-2017

@michael_e

Could you please post some snippets of you code?

michael_e · ‎02-12-2017

simplest case is using curl

curl -v --header "Authorization: Bearer <auth-token removed>" -F "upload=@/Users/me/Documents/workspace/test_excel2.xlsx" https://api.powerbi.com/v1.0/myorg/imports

More complicated java snippet

org.apache.commons.httpclient.methods.PostMethod method_FileSend_1 = new org.apache.commons.httpclient.methods.PostMethod(
"https://api.powerbi.com/v1.0/myorg/imports");

org.apache.commons.httpclient.methods.multipart.FilePart file_1_FileSend_1 = new org.apache.commons.httpclient.methods.multipart.FilePart(
"TEST_DATASET",
new java.io.File(
"/Users/me/Documents/devl/workspace/test_excel2.xlsx"),
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"ISO-8859-1");

org.apache.commons.httpclient.methods.multipart.Part[] parts_FileSend_1 = new org.apache.commons.httpclient.methods.multipart.Part[] { file_1_FileSend_1, };
method_FileSend_1
.setRequestEntity(new org.apache.commons.httpclient.methods.multipart.MultipartRequestEntity(
parts_FileSend_1, method_FileSend_1
.getParams()));

method_FileSend_1.addRequestHeader("Authorization", "Bearer "
+ context.access_token);

method_FileSend_1.addRequestHeader("datasetDisplayName",
"TEST_EXCEL_DATASET");

method_FileSend_1.addRequestHeader("nameConflict",
"Overwrite");

boolean isContinue_FileSend_1 = true;
int status_FileSend_1;
String finalURL_FileSend_1 = "https://api.powerbi.com/v1.0/myorg/imports";

try {

status_FileSend_1 = client_FileSend_1
.executeMethod(method_FileSend_1);

if (status_FileSend_1 != org.apache.commons.httpclient.HttpStatus.SC_OK) {
throw new java.lang.Exception("Method failed: "
+ method_FileSend_1.getStatusLine());
}

} catch (java.lang.Exception e) {

throw (e);

}

if (isContinue_FileSend_1) {

System.out.println("Status Line: "
+ method_FileSend_1.getStatusLine());
System.out.println("*** Response Header ***");
org.apache.commons.httpclient.Header[] responseHeaders_FileSend_1 = method_FileSend_1
.getResponseHeaders();

for (int i = 0; i < responseHeaders_FileSend_1.length; i++) {
System.out.print(responseHeaders_FileSend_1[i]);
}

michael_e · ‎02-09-2017

Has anyone sucessfully uploaded a local Excel file using the Create Import API Operation? If so, could you share your method?