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

I am trying to use import excel to database smart service for importing my data from excel into appian table.

In my CDT i have columns of text and integer type. But when trying to use the excel file with integer column as blank it is throwing me error as:

Incorrect integer value: '' for column `Appian`.`testBT`.`palletqtyno` at row 1???

where testBt is my tablename and palletqtyno is my column name whose data type is defined as integer in CDT

  Discussion posts and replies are publicly visible

Parents Reply
  • 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. 

Children