none
Get & Transform from Workbook Data Source set to "$Workbook$" RRS feed

  • Question

  • When I use Get & Transform and specify a workbook as the source of my table, I get the following XML in the "\xl\connections.xml" file of my Excel file:

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <connections xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><connection id="1" keepAlive="1" name="Query - Sheet1" description="Connection to the 'Sheet1' query in the workbook." type="5" refreshedVersion="6" background="1" saveData="1"><dbPr connection="Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sheet1;Extended Properties=&quot;&quot;" command="SELECT * FROM [Sheet1]"/></connection></connections>

    The "Data Source" does not specify the source workbook location though...only "$Workbook$". How can I retrieve the actual path for the file?


    • Edited by sdebussc Monday, February 26, 2018 7:08 PM
    Monday, February 26, 2018 4:23 PM

Answers

  • Hi sdebussc,

    Thank you for your patience as we researched your question regarding determining the full path to the external source spreadsheet. 

    External data source connection information is stored in the XLSX package in a custom part. You can locate the custom part under the customXML folder of the package. For example: customXml\iem1.xml.

    Contained in item1.xml is a <DataMashup/> element. The definition for the <DataMashup/> element can be found in the [MS-QDEFF]: Query Definition File Format document (https://msdn.microsoft.com/en-us/library/mt577220(v=office.12).aspx).

    In order to work with the data of the <DataMashup/> element you will need to decode the contents as described in the [MS-QDEFF]: Query Definition File Format document. 

    Once the data is decoded, you will need to examine the contents of the PackagePart. Within that package you will find the external data connection information in the Forumlas\Section1.m part.

    Sincerely,
    Will Gregg | open specifications

    Thursday, March 1, 2018 6:36 PM
    Moderator

All replies

  • Hi,

    Thank you for the Excel question on the XML format.  

    We have received the questions and someone will be in contact from the Windows Open Specifications support team. 

    Thanks,

    Nathan Manis

    Monday, February 26, 2018 8:21 PM
    Moderator
  • Hi sdebussc.

    Thank you for raising your question regarding the DataSource connection attribute value of the connections element. 

    I will be investigating the scenario you describe, and will post back the results of the investigation.

    Sincerely,

    Will Gregg | open specifications

     
    Tuesday, February 27, 2018 1:01 PM
    Moderator
  • Hi sdebussc,

    Thank you for your patience as we researched your question regarding determining the full path to the external source spreadsheet. 

    External data source connection information is stored in the XLSX package in a custom part. You can locate the custom part under the customXML folder of the package. For example: customXml\iem1.xml.

    Contained in item1.xml is a <DataMashup/> element. The definition for the <DataMashup/> element can be found in the [MS-QDEFF]: Query Definition File Format document (https://msdn.microsoft.com/en-us/library/mt577220(v=office.12).aspx).

    In order to work with the data of the <DataMashup/> element you will need to decode the contents as described in the [MS-QDEFF]: Query Definition File Format document. 

    Once the data is decoded, you will need to examine the contents of the PackagePart. Within that package you will find the external data connection information in the Forumlas\Section1.m part.

    Sincerely,
    Will Gregg | open specifications

    Thursday, March 1, 2018 6:36 PM
    Moderator