Forum Discussion

michael_e's avatar
michael_e
Regular Visitor
9 years ago

Importing an Excel file using the Import API returns a 400 Bad Request error

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(^#�F D_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.

3 Replies

  • michael_e's avatar
    michael_e
    Regular Visitor
    9 years ago

    Has anyone sucessfully uploaded a local Excel file using the Create Import API Operation? 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(^#�F D_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.

    • michael_e's avatar
      michael_e
      Regular Visitor
      9 years ago

      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]);
      }