Mass Data upload using CSV File and Validations

Ignition
1

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.

2

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.

3

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…?
4
  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.

5

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.

6

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.

7

Are you trying to do this in <=7.9 or >8.0?

The system.* functions are slightly different depending on your answer.

8

Thank you @pturmel, will play around jython and see where i end up.

9

Not clear on your question, can you elaborate more.

10

What version of Ignition are you using.

11

8.0.15

12

Here's a start on the CSV reading (importing);

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.

13

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.)

14

So what would you like to happen, if first row has errors insert 99 rows?

15

Yes, i would like to display the Errors for the specific records and then still Insert the rest of them.

16

Pseudo code;

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 :slight_smile:

1 Like
17

Nah. That's why it is called pseudo-code. :innocent:

2 Likes