# Mass Data upload using CSV File and Validations **URL:** \ **Category:** Ignition\ **Created:** [February 5, 2021, 9:11pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087 "2021-02-05T21:11:48Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![maruthi.addala](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@maruthi.addala](https://forum.inductiveautomation.com/u/maruthi.addala)\ **Post date:** [February 5, 2021, 9:11pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/1 "2021-02-05T21:11:48Z")
Hello Experts, Looking for some idea’s on this and i am New to Ignition. One of the Ask is, users want to Upload a sheet that contains around 70 columns of data into SQL Server DB through Ignition, which i made a code, but my question is “How can i Validate the Data before i Insert the Data into Base Tables.” Any suggestions around that Please. Thank you. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 5, 2021, 9:46pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/2 "2021-02-05T21:46:16Z")
Well, when you open the file, you’ll have to loop through the rows extracting the content from the CSV. Validate those rows as you read them in, inside this loop. If the rows are independent of each other, you can use runPrepUpdate() to insert each row after validation. If you need to make sure they are all ok before inserting any of them, you’ll store your validated rows in a temporary (in memory) list during the read loop, throwing some error if any row fails validation. If they all pass, you’ll use a second loop to read the list of rows and insert them into your DB. ---
**Author:** ![maruthi.addala](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@maruthi.addala](https://forum.inductiveautomation.com/u/maruthi.addala)\ **Post date:** [February 5, 2021, 10:19pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/3 "2021-02-05T22:19:51Z")
Thank you for your suggestion, so… Open CSV File/Find out the Count of the Rows/Validate the data in Row/Insert Row data into DB. Is that correct assumption…? But… 1. Assuming everything above is done in Python script right…? 2. Will that process be very slow…? 3. How do i show the user the Errors…? once i finish the Validations…? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 5, 2021, 10:46pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/4 "2021-02-05T22:46:37Z")
1. Jython, actually. 2. Not particularly. It’ll almost certainly be limited by your DB speed, not jython. Or possibly limited by memory capacity if the CSV files are very large. 3. I would construct a window/view to show progress. That window/view could have a text area to which you can write any validation failures. Or you could make the process two steps: read and display in a table, highlighting failures, then a separate button to send to the DB. FWIW, I wouldn’t bother counting the rows separately. Just read them in, validating and counting as you go, until end of file. ---
**Author:** ![maruthi.addala](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@maruthi.addala](https://forum.inductiveautomation.com/u/maruthi.addala)\ **Post date:** [February 6, 2021, 1:56pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/5 "2021-02-06T13:56:06Z")
Thank you @pturmel. Well looks like this is more complex than i thought it is, i need to first learn the Jython. Just curious, can this not be done python…? Thank you. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 6, 2021, 3:06pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/6 "2021-02-06T15:06:10Z")
Strictly speaking, Jython _ **is** _ python. Just a different implementation. The most common python is CPython, which is a native implementation in numerous operating systems. Jython is an implementation that runs inside a Java Virtual Machine–exactly what Ignition needs. You could do this process in CPython, as it supports CSV reading and DB operations, but it would run outside Ignition, and you’d need to use a CPython-compatible user interface technology. If you want to leverage Ignition’s user interface and platform resources, it has to be the jython flavor of python. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 6, 2021, 3:41pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/7 "2021-02-06T15:41:28Z")
Are you trying to do this in \<=7.9 or \>8.0? The system.\* functions are slightly different depending on your answer. ---
**Author:** ![maruthi.addala](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@maruthi.addala](https://forum.inductiveautomation.com/u/maruthi.addala)\ **Post date:** [February 6, 2021, 5:06pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/8 "2021-02-06T17:06:08Z")
Thank you @pturmel, will play around jython and see where i end up. ---
**Author:** ![maruthi.addala](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@maruthi.addala](https://forum.inductiveautomation.com/u/maruthi.addala)\ **Post date:** [February 6, 2021, 5:07pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/9 "2021-02-06T17:07:01Z")
Not clear on your question, can you elaborate more. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 6, 2021, 7:48pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/10 "2021-02-06T19:48:20Z")
What version of Ignition are you using. ---
**Author:** ![maruthi.addala](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@maruthi.addala](https://forum.inductiveautomation.com/u/maruthi.addala)\ **Post date:** [February 6, 2021, 9:07pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/11 "2021-02-06T21:07:24Z")
8.0.15 ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 6, 2021, 11:45pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/12 "2021-02-06T23:45:33Z")
Here's a start on the CSV reading (importing); > [@How do i import CSV file into Table](http://forum.inductiveautomation.com/t/how-do-i-import-csv-file-into-table/34830): > > Hello, i have tried to import a CSV file into the table but i have experienced ValueError. This is the script on the button: filepath= system.file.openFile(“csv”) string= system.file.readFileAsString(filepath) data = system.dataset.fromCSV(string) even.source.parent.getComponent(‘Table’).data = data This is the data i would like to import into the table [image] Search the forum for more, but heed my prior advice, if the content you read is for the 7.9 platform the tools used are slightly different. ---
**Author:** ![maruthi.addala](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@maruthi.addala](https://forum.inductiveautomation.com/u/maruthi.addala)\ **Post date:** [February 7, 2021, 2:06pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/13 "2021-02-07T14:06:03Z")
Hello @Matrix_Engineering, i was able to Import data and Insert into SQL Server table without any issue’s, but the Major thing i was after is “How to do data validations (Either at application level or at DB Level)”. At app level as mentioned by @pturmel he suggested to use the Jython. But at DB Level, even if i do Validations, by inserting data in a Temp Table before i load into Actual base tables, i am not quiet sure how to bring the Validation errors into Ignition screen from SQL Server.(May be i think i can do a Check constraint at DB Level and show the error, using default, but the thing is if CSV File is having 100 records and 70 columns, if first record is having error, the entire upload gets cancelled.) ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 7, 2021, 2:12pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/14 "2021-02-07T14:12:46Z")
> [@maruthi.addala](#): > > if CSV File is having 100 records and 70 columns, if first record is having error, the entire upload gets cancelled So what would you like to happen, if first row has errors insert 99 rows? ---
**Author:** ![maruthi.addala](https://avatars.discourse-cdn.com/v4/letter/m/b2d939/32.png) [@maruthi.addala](https://forum.inductiveautomation.com/u/maruthi.addala)\ **Post date:** [February 7, 2021, 4:35pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/15 "2021-02-07T16:35:08Z")
Yes, i would like to display the Errors for the specific records and then still Insert the rest of them. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 7, 2021, 4:43pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/16 "2021-02-07T16:43:19Z")
Pseudo code; ```auto for x in range(0,numRows): if rowData == validationChecks: append to dbTempTableWrite elif rowData != validationChecks: append to errorLog system.db.suitableFunction ("insert dbTempTableWrite into foo") system.db.suitableFunction ("insert errorLog into foo_fighters") ``` Phil will be along in a minute to rip my pseudo code to shreds 🙂 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 7, 2021, 5:10pm UTC](https://forum.inductiveautomation.com/t/mass-data-upload-using-csv-file-and-validations/43087/17 "2021-02-07T17:10:09Z")
> [@Matrix\_Engineering](#): > > rip my pseudo code to shreds Nah. That's why it is called _pseudo_-code. 😇