Skip to main content
S
shubhamy3376
July 2, 2023
Question

Import Excel to DB

  • July 2, 2023
  • 16 replies
  • 0 views

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!

    16 replies

    stefanhelzle0001
    stefanhelzle0001
    Brainy
    July 2, 2023

    You need to solve this using a stored procedure, run after the initial import, that transforms your data from a staging table into the final table.

    S
    shubhamy3376Author
    July 2, 2023

    Thanks [mention:126a676c85024855948336691b0a7b92:e9ed411860ed4f2ba0265705b8793d05],

    Could you please explain it a bit more. Do you have any sample procedure to transform data.

    Thanks! 

    stefanhelzle0001
    stefanhelzle0001
    Brainy
    July 2, 2023

    I do not have any example at hand, but there is tons of info on that matter in the internet.

    mathieud0001
    mathieud0001
    Brainy
    July 3, 2023

    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.

    S
    shubhamy3376Author
    July 3, 2023

    Thanks everyone,

    Any idea how to convert any date format to "dd/mm/yyyy" using expression rule.

    mathieud0001
    mathieud0001
    Brainy
    July 3, 2023

    What's the format in the excel?

    A
    amiteshs3330
    July 10, 2023

    Hi [mention:bb176b67c26c4e89b2173f39160c9f46:e9ed411860ed4f2ba0265705b8793d05] 
    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


    Terms & ConditionsAccessibility statement