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
hi,Sonali what is the column type in Excel and the database? from both sides keep it as a text value in Excel and also in the database Try uploading and check please
Hi Abhishek Karumuru Thanks for the reply.
Both place column type is "Date" . If I changed to Text its working but as per requirement data needs to be as Date in DB .
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").
hi, Sonali just a thought, initially just dump the data into the temporary/staging table, from there create a procedure and write a script that converts text values into date formats, Using that procedure push the data into actual transaction tables.
tried with custom format but not getting the exact value in DB some converted value is going to DB .
You have to format in expected value.Can you help us with example of yours.
I tested the below two formats m/dd/yyyy and d-mmm-yy but its giving error as below.
29-Apr-24 and 4/29/24
(conn=10647) Incorrect date value: '29-Apr-24' for column `Appian`.`bwauserdobcdt`.`dob` at row 1
(conn=10647) Incorrect date value: '4/29/24' for column `Appian`.`bwauserdobcdt`.`dob` at row 1
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())) ) ) )