Date value error in Import excel to DB SS in process model

Question

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

Shubham Aware · Answer

Sonali Can you try this change in excel :- Format the date column with a custom text format (e.g., "m/d/yy") to display the desired format . Select your date column. Right-click and choose "Format Cells". Go to the "Number" tab and choose "Custom". Enter the code for your desired format (e.g., mm/dd/yyyy for "04/24/2024").

Chris · Answer

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()))
 )
 )
)