Cannot get a numeric value from a text cell Mule Dataweave

Question

I am reading data from an Excel file (xlsx) using File connector and converting the data to application/java using data weave. I am getting this exception if there are any cells in the excel with type Accounting :

Message : Cannot get a numeric value from a text cell (java.lang.IllegalStateException). Payload : com.mulesoft.weave.reader.DefaultSeekableStream@6fe3727f

<dw:input-payload mimeType="application/xlsx">  
</dw:input-payload>

Here is the dataweave:

%dw 1.0 
%output application/java 
--- 

payload."Sheet1" map ((sheet1 , indexOfSheet1) -> { 
   id: sheet1.id 
})

If the cell format is changed to General, the exception is gone. How can i get this working for excel with cells of Accounting type?

please provide your excel document and the dataweave, that causes the exception — Yevgeniy
%dw 1.0 %output application/java --- payload."Sheet1" map ((sheet1 , indexOfSheet1) -> { id: sheet1.id }) drive.google.com/open?id=0B2VwKPUfZyE0NUJvYzZZNmNIdkk — user94538

ssan ssan · Accepted Answer · 2017-04-04T21:59:45

I created same scenario in my local anypoint studio initially with Studio version 6.2.3 and Mule runtime 3.8.1 EE ran into several issues. I updated runtime to 3.8.3 EE all issues are gone & everything seems to be working fine. Verify if you have any version issues, excel support recently added to dataweave and still in beta.

https://forums.mulesoft.com/questions/42650/ms-excelxls-on-dataweave.html

Here is the flow that worked for me

                <?xml version="1.0" encoding="UTF-8"?>
    <mule xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" xmlns:metadata="http://www.mulesoft.org/schema/mule/metadata" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
        xmlns:spring="http://www.springframework.org/schema/beans" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
    http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
    http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd
    http://www.mulesoft.org/schema/mule/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd">
        <flow name="xslxreaderFlow">
            <file:inbound-endpoint path="C:\files\input" responseTimeout="10000"  doc:name="File"/>
            <dw:transform-message doc:name="Transform Message" metadata:id="aa47f058-609d-4d96-9646-a52accacaf9f">
                <dw:input-payload doc:sample="sample_data\excel_1.xlsx" mimeType="application/xlsx"/>
                <dw:set-payload><![CDATA[%dw 1.0
    %output application/java
    ---

    payload."Sheet1" map ((sheet1 , indexOfSheet1) -> { 
       id: sheet1.ID, 
       amount:sheet1.Amount
    })]]></dw:set-payload>
            </dw:transform-message>
            <logger message="#[message.payloadAs(java.lang.String)]" level="INFO" doc:name="Logger"/>
        </flow>
    </mule>

Output results from console

INFO  2017-04-04 16:46:57,940 [[test].connector.file.mule.default.receiver.01] org.mule.transport.file.FileMessageReceiver: Lock obtained on file: C:\files\input\Book1.xlsx
INFO  2017-04-04 16:47:00,719 [[test].xslxreaderFlow.stage1.02] org.mule.api.processor.LoggerMessageProcessor: [{id=123456, amount=5000.75}, {id=234567, amount=45367.34}]

Here is the log message for your file

INFO  2017-04-06 09:37:38,650 [[test].connector.file.mule.default.receiver.01] org.mule.transport.file.FileMessageReceiver: Lock obtained on file: C:\files\input\file1.xlsx
INFO  2017-04-06 09:37:38,792 [[test].xslxreaderFlow.stage1.03] org.mule.api.processor.LoggerMessageProcessor: [{id=1234, amount=500.5}, {id=456, amount=700.25}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}, {id=, amount=}]

Cannot get a numeric value from a text cell Mule Dataweave

1 Answers