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
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.
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