Error uploading data using Import CSV to Database Smart Service

Hi Dave,

When we designed similar application where thousands of rows needs to be validated , and multiple tables needs to be updated sequentially. We picked up asynchronous approach.
1.User uploads the file and the process starts
2.Validate the file format on success inform users that they will be notified via email about the status of the request.
3. Appian process Orchestrates the rest of process .i.e. Import CSV data to db , validating the data , writing the results in another table, etc., just by executing Stored procedures from appian process . Data is held at the DB level with unique identifiers for each transactions.
4. users receive Emails with results .

Hi Dave,

When we designed similar application where thousands of rows needs to be validated , and multiple tables needs to be updated sequentially. We picked up asynchronous approach.
1.User uploads the file and the process starts
2.Validate the file format on success inform users that they will be notified via email about the status of the request.
3. Appian process Orchestrates the rest of process .i.e. Import CSV data to db , validating the data , writing the results in another table, etc., just by executing Stored procedures from appian process . Data is held at the DB level with unique identifiers for each transactions.
4. users receive Emails with results .

Thanks for responding. I have 10 different csv files, all to be inserted(not update) to each respective table. So no transformation or manipulations or validations.

1. Did you use different database and not MYSQL? I need to have the data in SQL server.
2. We have constraints in the tables. Did you had any constraints? eg. Table2 is id is a FK to the Table1 PK. which means no insertion can happen(async scenario) in Table2 until all data in Table1 is inserted
3. If answer to 1 is Yes, How did you passed data from Appian to Stored Proc?

Thanks
Nirav

Hi Dave,

1.We had used SQL Server.
2.We didn't had such constraints .

But, the approach we followed might be helpful, hence sharing it .

1.We used Transact SQL in Stored proc to create a unique table at run time for importing the file data ,where table name and column names are passed from Appian to DB via Stored proc.
2.Once table is created pass the table name to this plugin and import data from CSV
3.For each transaction Table name and a unique identifier (GUID ID ) is held in separate table for reference and maintenance
4.So appian know the table name and uses it in further stored proc to move data from these temp table to actual table with unique identifier for each file specific data. So Data for a specific file load are uniquely identifiable with a unique ID that Appian had passed .