Import Excel to DB

Question

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!

Konduru Chaitanya · Answer

Hello Rahul009 , 
 Not sure if this idea will help you considering the amount of data that you are trying to import. You can create expression rule where you read your excel file and then have a temp table/cdt/record which will try to store this. before mapping your excel data, you can convert the date value by creating a custom rule. I had faced a similar issue and I used this technique to overcome.

Mathieu Drouin · Answer

Another way to do it is to import the data in the staging table and simply transform the data using an expression rule and rewrite it back into the proper table either with a Write Record or Write to Data Store Entity Smart Service.

Mathieu Drouin · Answer

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.

amiteshsin · Answer

Hi Rahul009
You can convert the date from Appian by reading the excel file (although this process is heavy in terms of processing)
OR
You 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

Import Excel to DB

Top Replies