Hi Everyone,
I am using Import excel to DB smart service to import excel data in Appian. And my requirement if date column in excel is in any format then it should be changed to "dd/mm/yyyy" before inserting into DB. I dont see any option to format the dates in smart service. Can anyone please help me out?
Many Thanks!
Discussion posts and replies are publicly visible
What's the format in the excel?
The requirement is that the user can write into any date format in excel?
Ideally it would be a standard date format for the whole column. Based on that format, you could write an expression to parse the date.
Otherwise, you can also use the parseDate function in https://community.appian.com/b/appmarket/posts/date-and-time-utilities which could work if you don't have a stable format.
Is there any way to validate the dates imported through excel in Appian is valid or not. Does anyone help with custom expression to validate the dates are valid.
Thank you!
Trying to validate a date in "any" format is very tricky. You could try a statistical approach on all the imported values. You would have to identify the used separator and the order of the individual values.
I think it is much easier to make the user select a format when importing.
Hi Rahul009 You can convert the date from Appian by reading the excel file (although this process is heavy in terms of processing)ORYou can get the data in a temp table and then using a stored procedure which could have a cursor logic in it to transform the data (This divides the work load between Appian and Database Engine and is more efficient)You would also need to understand what is the format the date is coming in to write a transformation logic to convert it into intended format