getting this error while loading data to DB table using import excel to DB smart service (conn=246144) Incorrect date value: '4/24/24' for column `Appian`.`bwauserdobcdt`.`dob` at row 1
Discussion posts and replies are publicly visible
These data issues are common in these nodes which translate Excel or CSV directly to the DB, which is why I always recommend using fn!readexcelsheetpaging() instead, which is part of the same Excel Tools plugin. I have had much better success with this method.
With the excel file uploaded to the system, you can use this function in an expression rule to parse it into a CDT, then simply pass the CDT to a Write to Datastore node. Such as:
a!localVariables( local!data: fn!readexcelsheetpaging( ri!doc, 1, a!pagingInfo(1,cons!MAX_ROWS_ALLOWED) /* This function will not work with -1 batch size */ ), if( local!data.totalCount<2, /* First row is header */ {}, reject( rule!APN_isEmpty, a!flatten( a!forEach( items: ldrop(local!data.data,1), /* Remove header */ expression: { if( rule!isEmpty(index(fv!item.values,1,"")), /* Empty row catch */ null, 'type!{urn:com:appian:types}YOUR_CDT_HERE'( id: null, itemNumber: index(fv!item.values,1,""), manufacturer: index(fv!item.values,2,""), model: index(fv!item.values,3,""), desc: index(fv!item.values,4,""), uom: upper(index(fv!item.values,5,"")), qty: index(fv!item.values,6,0), selectedVendor: index(fv!item.values,7,""), price: index(fv!item.values,8,0), dateEntered: rule!convertExcelDate( dateVal: index(split(index(fv!item.values,9,"")," "),1,null) ), ) ) } ) ) ) ) )
Date values still typically need conversion in my experience, hence the sub rule call above for value 9, which works as:
a!localVariables( local!split: split(ri!dateVal,"-"), if( or( rule!APN_isEmpty(local!split), count(local!split)<>3, ), null, date( index(local!split,1,year(today())), index(local!split,2,month(today())), index(local!split,3,day(today())) ) ) )