Bulk Upload of Data into Appian DB(Dealing with Lakhs of Records)

Hi All,

I would be getting data from External system in an Excel sheet where I need to insert all the data into Appian DB

OriginalPostID-231862

  Discussion posts and replies are publicly visible

  • Is there any easy way where I can insert the data without affecting the Performance of the Server, and also I know that this would be done in batches and also in Off hours, Just wanted to check if there is any smart services or easy way to do this.
  • Could you generate SQL INSERT statements using Excel formulas? Inserting these records via a script will always perform better than kicking off Appian processes to ingest the data.
  • To tag onto colton's post above, I would recommend using an external tool to generate the SQL insert statements. The website sqlizer.io can take in an excel file and generate the code, as long as the excel file is formatted properly.
  • Thanks, Brett! I didn't know this existed.
  • Good suggestions. Just to add, I do bulk updates nightly for 30,000 rows. Data is loaded into Appian via CSV file on the server, loaded into a CDT, CDT manipulated to retrieve unique keys then ran through a Write to DS node. This is off hours, but works fine and the only thing to watch out for is that you schedule this away from server checkpointing (notorious for creating errors when timing conflicts with large DB activity).
  • Thanks everyone I would be posting my approach which I have took soon
  • 0
    A Score Level 1
    in reply to harshav
    Hi harshav ,

    Did you finalize any best approach to upload bulk data using Appian, if so can you please share your thoughts.

    Regards,
    Balaji.R
  • Use the Appian DB import feature to import the data from excel to a table if the data structure in excel is in par with the table.
    It makes your job quick, a lot easier and would not impact appian performance.
  • 0
    A Score Level 1
    in reply to Alok Patro
    Hi Alok,

    Thanks for your response, I agree with your point of using the DB import feature which is available but if in case there is a requirement of providing a user interface to the end user and the user may upload excel sheet with data around 10K to 20K rows then do we have any effective way of handling this in Appian or the existing excel smart service can handle this wisely?

    Regards,
    Balaji.R
  • 0
    Certified Lead Developer
    in reply to harshav
    Hi,

    You can also look for 'Import CSV to Database' Smart Service if you are fine to have a CSV file but this smart service has its own limitations as well so ultimately depends on your use case. It is pretty fast.

    Thanks.
    Hitesh
1 2