# Tag History to CSV and Back how do i fix ignition crash?

**URL:** <https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124>\
**Category:** Ignition\
**Created:** [June 16, 2023, 7:54am UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124 "2023-06-16T07:54:48Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\
**Post date:** [June 16, 2023, 7:54am UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/1 "2023-06-16T07:54:48Z")

</div>

Greetings,

I have a script that successfully generates a tag historian from a CSV file. However, I'm encountering an issue when attempting to restore the CSV file to the SQL database.  
The problem arises due to the sheer volume of data, as there are millions of rows in the file. I'm curious if anyone knows of a viable solution to efficiently transfer such a large number of rows back into the database.

```auto
csvRead = csv.reader(csvFile),
csvDataset = []
for row in csvRead:
    csvDataset.append(row)
csvFile.close()

```

antone now how to make this faster?

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [June 16, 2023, 10:03am UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/3 "2023-06-16T10:03:14Z")

</div>

Please format code as code using the \</\> button. It converts this ...

> [@Remo\_Aerts](#):
>
> csvRead = csv.reader(csvFile)  
> csvDataset =  
> for row in csvRead:  
> csvDataset.append(row)  
> csvFile.close()

... into this ...

```auto
csvRead = csv.reader(csvFile)
csvDataset =
for row in csvRead:
    csvDataset.append(row)
csvFile.close()

```

... but you seem to be missing something on the second line.

---

<div class="post-metadata">

**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\
**Post date:** [June 16, 2023, 10:14am UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/4 "2023-06-16T10:14:41Z")

</div>

tnx for the tip.

what i was missing was just a emty List.  
but that was not the problem.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 16, 2023, 11:03am UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/5 "2023-06-16T11:03:29Z")

</div>

Some consideration points:

- It will try to put the entire dataset into memory.
- As a general rule the db will only allow inserts of up to 1000 rows.

I would suggest breaking your process into 1000 row chunks.

---

<div class="post-metadata">

**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\
**Post date:** [June 16, 2023, 11:11am UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/6 "2023-06-16T11:11:37Z")

</div>

hello tnx for your reply.

I think the Database isn't the problem here.  
because this is an insert per row.

but ignition crashes when the csv becomes a List.

and i don't now how to change this.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 16, 2023, 11:20am UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/7 "2023-06-16T11:20:05Z")

</div>

If you are inserting per row, then you don't need to make csvDataset. Just insert the row right away.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 16, 2023, 11:30am UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/8 "2023-06-16T11:30:12Z")

</div>

Inserting a row at a time is going to be less efficient. A series of ops should be something like:

```python
csvRead = csv.reader(csvFile),
csvDataset = []
count = 0
for row in csvRead:
    csvDataset.append(row)
    count += 1
    if count >= 1000:
    	insert_into_DB_here()
    	csvDataset = []
    	count = 0
if count>0:
	last_insert_into_db()

csvFile.close()

```

If you use system.db.runPrepUpdate to insert your rows, I do have a helper function to create the question marks for you.

> [@Pass parameter to tag.writeAsync](https://forum.inductiveautomation.com/t/pass-parameter-to-tag-writeasync/43485/13):
>
> Sorry, I was pulled into a meeting as I was typing this, but if it helps, I do have a function to generate question marks for prep inserts. def prepInsertQmarks(numOfGroups, groupSize): ''' Generate a string of grouped question marks for prepUpdate inserts example: Generate three groups of four question marks: prepInsertQmarks(3, 4) returns (?,?,?,?),(?,?,?,?),(?,?,?,?) ''' group = '(' + ','.join(['?'] \* groupSize) + ')' return ','.join([group] \* numOfGroups) query = """ …

---

<div class="post-metadata">

**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:** [June 16, 2023, 11:38am UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/9 "2023-06-16T11:38:00Z")

</div>

> [@JordanCClark](#):
>
> `count >= 1000`

Consider not doing quite so many. IIRC, JDBC supports only a few thousand question marks per statement.

---

<div class="post-metadata">

**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\
**Post date:** [June 16, 2023, 11:47am UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/10 "2023-06-16T11:47:20Z")

</div>

tnx i think this schould work.

i am only not sure how to do this. but will try

---

<div class="post-metadata">

**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\
**Post date:** [June 16, 2023, 12:21pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/11 "2023-06-16T12:21:39Z")

</div>

how mutch will be the best?

---

<div class="post-metadata">

**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\
**Post date:** [June 16, 2023, 12:28pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/12 "2023-06-16T12:28:11Z")

</div>

well it works ignition doesn't crash anymore. tnx

---

<div class="post-metadata">

**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\
**Post date:** [June 16, 2023, 12:30pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/13 "2023-06-16T12:30:05Z")

</div>

then one more ting  
2 mil of rows take around 15 minutes. and that is only one day.

is there any way to make this progress faster?  
because this is only a small tag historian for us.  
my script right now is:

```auto

def runAction(self, event):
	import csv
	
	# get file path of upload and then convert csv data to dataset
	csvPath = "C:/test historyFri Jun 16 09 02 22 CEST 2023/sqlt_data_1_20230611.csv"
	
	csvFile = open(csvPath, 'r')
	
		
	csvRead = csv.reader(csvFile)
	csvDataset = []
	count = 0
	for row in csvRead:
		print "test"
		csvDataset.append(row)
		count += 1
	
		if count >= 1000:
			for row in range(len(csvDataset)):
				tagid = csvDataset[row][0] 
				intvalue = csvDataset[row][1]
				if intvalue == "":
					intvalue = None  
				floatvalue = csvDataset[row][2]
				if floatvalue == "":
					floatvalue = None  
				stringvalue = csvDataset[row][3]
				if stringvalue == "":
					stringvalue = None  
				datevalue = csvDataset[row][4]
				if datevalue == "":
					datevalue = None  
				dataintegrity = csvDataset[row][5]
				t_stamp = csvDataset[row][6]
		
				# insert orders into sql table
				query = """use mijn_database INSERT INTO sqlt_data_1_2023_06 (tagid ,intvalue, floatvalue, stringvalue,datevalue,dataintegrity,t_stamp) 
						 VALUES (?,?,?,?,?,?,?)"""
				args = [tagid,intvalue, floatvalue, stringvalue,datevalue,dataintegrity,t_stamp]
				system.db.runPrepUpdate(query, args)
			csvDataset = []
			count = 0
	if count>0:
		for row in range(len(csvDataset)):
			tagid = csvDataset[row][0] 
			intvalue = csvDataset[row][1]
			if intvalue == "":
				intvalue = None  
			floatvalue = csvDataset[row][2]
			if floatvalue == "":
				floatvalue = None  
			stringvalue = csvDataset[row][3]
			if stringvalue == "":
				stringvalue = None  
			datevalue = csvDataset[row][4]
			if datevalue == "":
				datevalue = None  
			dataintegrity = csvDataset[row][5]
			t_stamp = csvDataset[row][6]
	
			# insert orders into sql table
			query = """use mijn_database INSERT INTO sqlt_data_1_2023_06 (tagid ,intvalue, floatvalue, stringvalue,datevalue,dataintegrity,t_stamp) 
					 VALUES (?,?,?,?,?,?,?)"""
			args = [tagid,intvalue, floatvalue, stringvalue,datevalue,dataintegrity,t_stamp]
			system.db.runPrepUpdate(query, args)

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 16, 2023, 1:59pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/14 "2023-06-16T13:59:45Z")

</div>

> [@JordanCClark](#):
>
> Inserting a row at a time is going to be less efficient.

Insert multiple rows at once. But, as @pturmel said, it may only support a few thousand question marks. I modified your script, shown below. Start with 500 rows at a time, then raise or lower depending on how it runs.

```python
def runAction(self, event):
	import csv

    def prepInsertQmarks(numOfGroups, groupSize):
	''' 
	    Generate a string of grouped question marks for prepUpdate inserts
	    example: Generate three groups of four question marks:
	    prepInsertQmarks(3, 4) returns (?,?,?,?),(?,?,?,?),(?,?,?,?)
	'''
		group = '(' + ','.join(['?'] * groupSize) + ')'
		return ','.join([group] * numOfGroups)
	
	# get file path of upload and then convert csv data to dataset
	csvPath = "C:/test historyFri Jun 16 09 02 22 CEST 2023/sqlt_data_1_20230611.csv"
	
	csvFile = open(csvPath, 'r')
	
		
	csvRead = csv.reader(csvFile)
	csvDataset = []
	count = 0
	query = """use mijn_database INSERT INTO sqlt_data_1_2023_06 (tagid ,intvalue, floatvalue, stringvalue,datevalue,dataintegrity,t_stamp) 
			   VALUES {}"""

	for row in csvRead:
		print "test"
        # using extend instead of append adds values as a flat list to use in runPrepUpdate() later
		csvDataset.extend(row)
		count += 1
		# Based on @pturmel's information, check for 500 rows instead of 1000.
		# Reduce the count threshold value if an error occurs
		if count >= 500:
			qmarks = prepInsertQmarks(count, len(row))
			# insert orders into sql table
			system.db.runPrepUpdate(query.format(qmarks), csvDataset)
			csvDataset = []
			count = 0
	if count>0:
		qmarks = prepInsertQmarks(count, len(row))
		# insert orders into sql table
		system.db.runPrepUpdate(query.format(qmarks), csvDataset)

```

---

<div class="post-metadata">

**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\
**Post date:** [June 16, 2023, 2:08pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/15 "2023-06-16T14:08:57Z")

</div>

i don't understand the function prepInsertQmarks  
but if i try to run the code above it gives a error

---

<div class="post-metadata">

**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\
**Post date:** [June 16, 2023, 2:12pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/16 "2023-06-16T14:12:00Z")

</div>

wait it worked only thing to change was 500 = 200  
otherwise the error was  
caused by SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

tnx u so mutch

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 16, 2023, 2:21pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/17 "2023-06-16T14:21:27Z")

</div>

> [@Remo\_Aerts](#):
>
> 2100

There, we now know for sure what the hard limit is. 🙂

EDIT: So, for seven columns, you could bump it up to 300 rows. But I'd maybe put it to 280. Why tempt fate? 😆

---

<div class="post-metadata">

**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:** [June 16, 2023, 2:23pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/18 "2023-06-16T14:23:29Z")

</div>

> [@JordanCClark](#):
>
> we now know for sure what the hard limit is

Varies by brand and version IIRC.

---

<div class="post-metadata">

**Author:** ![Remo\_Aerts](https://avatars.discourse-cdn.com/v4/letter/r/e19adc/32.png) [@Remo\_Aerts](https://forum.inductiveautomation.com/u/Remo_Aerts)\
**Post date:** [June 16, 2023, 2:24pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/19 "2023-06-16T14:24:45Z")

</div>

did try 250 did't work but witin 1 minute is fast enouf for me. tnx

---

<div class="post-metadata">

**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\
**Post date:** [June 22, 2023, 4:04pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/20 "2023-06-22T16:04:32Z")

</div>

Wouldn't this be one of those cases where you would prefer `system.db.runSFPrepUpdate` instead of `system.db.runPrepUpdate`, or am I misunderstanding the purpose of that function?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 22, 2023, 6:45pm UTC](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124/21 "2023-06-22T18:45:47Z")

</div>

I'd tink you'd still be limited by the number of parameters you can send at one time.

[Next page](https://forum.inductiveautomation.com/t/tag-history-to-csv-and-back-how-do-i-fix-ignition-crash/75124.md?page=2)