Dataweave is not identifying headers in Excel file

Question

I am using Mule 4 and Anypoint Studio 7.

I want to extract data from an Excel spreadsheet but the table I want to extract data from starts at line 4 in the worksheet and the output from Transform message is null which I assume is because it doesn't detect the column names as they are at line 4 and not line 1.

How can I fix this?

Dataweave XML

        <ee:transform doc:name="Transform Message" doc:id="1bdda7fe-2abe-48d3-8bc5-42a94c12b6b9" >
            <ee:message >
                <ee:set-payload ><![CDATA[%dw 2.0
input payload application/xlsx header=true
output application/json
---
{
    "Customers": payload."CUSTOMERS" filter $$ > 2 map ( cUSTOMER , indexOfCUSTOMER ) -> {
        "Type": cUSTOMER.type,
        "Category": cUSTOMER.category
    }
}]]></ee:set-payload>
            </ee:message>
        </ee:transform>

Dataweave code:

%dw 2.0
input payload application/xlsx header=true
output application/json
---
{
    "Customers": payload."CUSTOMERS" filter $$ > 2 map ( cUSTOMER , indexOfCUSTOMER ) -> {
        "Type": cUSTOMER.type,
        "Category": cUSTOMER.category
    }
}

Excel

Thanks for any help

A thought: Can you use a separate transformer before to trim the first 3 lines? Your header would be the same, just change header=false and the body should be payload[3 to -1]. You could then feed the result of that into your script above. — jerney

Ryan Carter Ryan Carter · Accepted Answer · 2019-03-27T11:52:51

You can set the property tableOffset to the column where the data starts for xlsx format.

For Mule 3: In GUI:

Right click on the "Payload" label (left panel).
Click on the "Reader Configuration" option.
GUI opens with multiple options to be set.
Configure the "tableOffset" to the starting cell of the table. in your example A4.

Code example:

<dw:transform-message doc:name="Transform Message" metadata:id="9abf7128-71b8-4610-8fca-7ceda17f852e">
            <dw:input-payload mimeType="application/xlsx">
                <dw:reader-property name="tableOffset" value="A4"/>
            </dw:input-payload>
            <dw:set-payload><![CDATA[%dw 1.0 
%output application/json
---
payload]]></dw:set-payload>
        </dw:transform-message>

For Mule 4, you need to set the reader properties on the event source:

https://docs.mulesoft.com/mule-runtime/4.1/dataweave-formats#reader_writer_properties

For example, if you are reading the xlsx from a file:

<file:listener doc:name="On New File" config-ref="File_Config" outputMimeType='application/xlsx tableOffset="A4"'>
  <scheduling-strategy >
    <fixed-frequency frequency="45" timeUnit="SECONDS"/>
  </scheduling-strategy>
  <file:matcher filenamePattern="myfile.xlsx" />
</file:listener>

Before your transform you could also try using a simple transform to add the new reader properties:

<set-payload value="#[output application/xlsx tableOffset='A4' --- payload]" />
        <!-- Then your normal transformer -->
        <ee:transform xsi:schemaLocation="http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd"
            doc:id="bed303c7-1549-45da-af58-10c4ad937926">

            <ee:message>

                <ee:set-payload><![CDATA[%dw 2.0
output application/json --- payload]]></ee:set-payload>
            </ee:message>
        </ee:transform>

Dataweave is not identifying headers in Excel file

1 Answers