On using import excel to database smart service getting this error : Incorrect integer value: '' for column `Appian`.`tablename`.`palletqtyno` at row 1???

Is your palletqtyno field configured to allow null values at the DB level?

Yes null is allowed and data type is integer

Working with Excel import/export can be a little finicky at times, especially related to data types.  I am not that familiar with this node - typically I will utilize fn!readexcelsheet() to import raw data, then cast it to a CDT, then write the CDT to the database with Write to Datastore Entity.  This allows you to avoid some of these issues with going straight to the DB and perform any manipulations, casting, etc in between as needed.

Working with Excel import/export can be a little finicky at times, especially related to data types.  I am not that familiar with this node - typically I will utilize fn!readexcelsheet() to import raw data, then cast it to a CDT, then write the CDT to the database with Write to Datastore Entity.  This allows you to avoid some of these issues with going straight to the DB and perform any manipulations, casting, etc in between as needed.

How do we cast raw data from readexcelSheet() to CDT ...???

Can you please tell me the steps as I am new to appian

I'll include some sample code below.  This reads an excel file and creates a List of CDT, for each item in the file.  The output can be written to the DB using Write to Datastore Entity.  This can also be modified to accept the document as a rule input, then you can save as a rule and call it in your process model, save to CDT and write from there.  

You can test this by uploading one of your Excel files to /design, point a constant to it, then modify this code to reference your constant and the CDT pointing to your table.

a!localVariables(
  local!data: fn!readexcelsheet(cons!CHRIS_TEST_SP_DOC,1,1), /* get the data */
  
  if(
    local!data.success, /* cast when the read was successful */
    a!forEach( 
      items: local!data.result, /* loop all rows in the excel file */
      expression: 'type!{urn:com:gdit:types}COE_Sample_CDT'(
        id: null,
        approval: property(fv!item,"approval",null), /* retrieve values with property() */
        requestByName: property(fv!item,"requestByName",null),
        requestById: property(fv!item,"requestById",null),
      )
    ),
    {}
  )
)

Feel free to play around with it and let us know if you have any additional questions. 

Thanks I will check with this code

Hi 

I am using below expression but it is returning null values from column in excel:

a!localVariables(
local!data: fn!readexcelsheet(cons!Test_cons_Doc,0,2), 

if(
local!data.success, 
a!forEach(
items: local!data.result, 

expression: 'type!{urn:com:appian:types:Brexit}Brexit_shipment_tracker'(

srn_asn_number: property(fv!item,"srnasnnumber",null),
status: property(fv!item,"status",null)
)
),
{}
)
)

am I providing any wrong input ?

For debugging, I would suggest commenting out the if() statement in it's entirety and use just "local!data.result" as the output - that will show us what is being read in.

I have already check that...local!data.result is returning the row with correct data b

Also if I am using the expression as status: fv!item.values[2] instead of using status:property(fv!item,"status",null)  is giving value for that field 

what can be the reason ? may be some issue with property(fv!item,"status",null) expression ??

Ah yes, my sample code needs adjustment - as you have found, the result is actually a list of text string within local!data.result.values, vs a list of dictionary.  fv!item.values[2] or index(fv!item.values,2,null) would be the way to go.

a!localVariables(
  local!data: fn!readexcelsheet(cons!CHRIS_TEST_SP_DOC,0,2), /* get the data */

  if(
    local!data.success, 
    a!forEach( 
      items: local!data.result,
      expression: 'type!{urn:com:gdit:types}COE_Sample_CDT'(
        id: null,
        approval: index(fv!item.values,1,null),
        requestByName: index(fv!item.values,2,null),
        requestById: index(fv!item.values,3,null),
      )
    ),
    {}
  )
)

Yes now the code is working....But i have one doubt, as I got the output but how to write this into database ? 

Great!  If you are utilizing this new rule within a process model, you will save the output into a process variable of type Brexit_shipment_tracker (multiple), then persist to the database utilizing the Write to Data Store Entity smart service.  Note you can also write the data within a SAIL form using a!writeToDataStoreEntity().

Thanks Chris