# Import script

**URL:** <https://forum.inductiveautomation.com/t/import-script/9457>\
**Category:** Ignition\
**Created:** [December 16, 2014, 9:39pm UTC](https://forum.inductiveautomation.com/t/import-script/9457 "2014-12-16T21:39:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [December 16, 2014, 9:39pm UTC](https://forum.inductiveautomation.com/t/import-script/9457/1 "2014-12-16T21:39:41Z")

</div>

I have this script which allows me to import data from CSV and insert into SQL (credit - JordanCClark). Imports typically have ~1000 rows. Following the import I have a SQL while loop which goes back through and updates the SortOrder. This update is inefficient and is causing delays in processing, and contributing to the occasional deadlock. I would like to be able to accomplish this in the original insert, but don’t know how to do that. Any help is greatly appreciated.

[code]event.source.parent.Loading = 1  
wcid = event.source.parent.WCID  
changeby = system.tag.getTagValue("[Client]OverrideUsername")

import fileinput

inputFile=system.file.openFile(‘csv’)

if inputFile != None:  
fileinput.close()  
values = “”  
flag = 0  
for lineIn in fileinput.input(inputFile):  
flagTest=lineIn[:-(len(lineIn)-5)] #Extract first five characters of the line.  
if flag==1:  
columns=’(’ + lineIn + ‘)’ #column list for query  
flag=0 #Reset flag  
if flag==2:  
values+="(’" + lineIn.replace(’"’,’’).replace(",","’,’") + “’),” #add set of values. Strip away all double quotes,  
# add single quotes to all values,  
# wrap in parentheses,  
# and pop a comma on the end.  
# The comma prepares for the next set of values.  
if flagTest ==’#NAME’: #Set flag according to '#" line delimiter  
flag=1  
elif flagTest == ‘#ROWS’:  
flag=2  
elif flagTest == ‘#TYPE’:  
flag=0  
fileinput.close()  
values=values[:-1] #Strip off last comma after all the values have been processed  
query=“INSERT INTO tblOMS\_AX " + columns + " VALUES “+ values  
query = query.replace(”\n”,"").replace("\r","")  
print query  
system.db.runUpdateQuery(query)

sql = “UPDATE tblOMS\_AX SET WCID=?, ChangeBy=?, ChangeDate=GETDATE() WHERE WCID IS NULL”  
system.db.runPrepUpdate(sql, [wcid, changeby])

sql = “”“DECLARE @mycount INT  
SET @mycount = (SELECT count(\*) FROM tblOMS\_AX WHERE SortOrder IS NULL AND WCID = ?)  
WHILE @mycount \> 0  
BEGIN  
UPDATE TOP (1) tblOMS\_AX SET SortOrder=(SELECT ISNULL(MAX(SortOrder),0) + 1 FROM tblOMS\_AX WHERE WCID = ?) WHERE WCID=? AND SortOrder IS NULL  
SET @mycount = @mycount - 1  
END”""  
system.db.runPrepUpdate(sql, [wcid, wcid, wcid])

system.db.refresh(event.source.parent,“Data”)  
event.source.parent.Loading = 0[/code]  
[Import format.csv](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/fb700710c30edc0a0d1b2cb094cd8e7c4a75136b.csv) (479 Bytes)

---

<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:** [December 17, 2014, 2:21am UTC](https://forum.inductiveautomation.com/t/import-script/9457/2 "2014-12-17T02:21:55Z")

</div>

Hi Tim,

Looking at the attachment, I see that it doesn’t have the same columns as the first go around. Will some files have SortOrder and others not?

---

<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:** [December 17, 2014, 3:34am UTC](https://forum.inductiveautomation.com/t/import-script/9457/3 "2014-12-17T03:34:33Z")

</div>

I’ve reworked it so that it checks if WCID, ChangeBy, or SortOrder columns exist-- I’m gathering that they usually don’t-- and add colums to the query as needed. If this is closer to what you need, you may be able to get rid of both UPDATE queries.

[code]#event.source.parent.Loading = 1  
wcid = 555 #event.source.parent.WCID  
changeby=‘Jordan’ #system.tag.getTagValue("[Client]OverrideUsername")

import fileinput

inputFile=system.file.openFile(‘csv’)

if inputFile != None:  
fileinput.close()  
values = “”  
flag = 0  
sortOrder = 1  
for lineIn in fileinput.input(inputFile):  
flagTest=lineIn[:-(len(lineIn)-5)] #Extract first five characters of the line.  
if flag==1:  
columns = ‘(’ + lineIn # Initial column list for query  
columnList = lineIn.replace("\n","").replace("\r","").split(’,’) # list of column names. We can use this  
# to check if a cloumn exists  
if ‘WCID’ not in columnList: # Check if WCID exists  
columns += ‘,WCID’ # Add to column Names, if needed  
if ‘ChangeBy’ not in columnList: # Check if ChangeBy exists  
columns += ‘,ChangeBy’ # Add to column Names, if needed  
if ‘SortOrder’ not in columnList: # Check if SortOrder exists  
columns += ‘,SortOrder’ # Add to column Names, if needed  
columns += ‘)’ # Add right-hand parenthesis  
flag=0 #Reset flag  
if flag==2:  
values+="(’" + lineIn.replace(’"’,’’).replace(",","’,’") # Initial Values  
if “WCID” not in columnList: # Check if WCID exists  
values += “’,’” + str(wcid) # Add value, if needed  
if ‘ChangeBy’ not in columnList: # Check if ChangeBy exists  
values += “’,’”+ str(changeby) # Add value, if needed  
if ‘SortOrder’ not in columnList: # Check if SortOrder exists  
values += “’,’” + str(sortOrder) # Add value, if needed  
sortOrder += 1  
values += “’),” # wrap in parentheses,  
# and pop a comma on the end.  
# The comma prepares for the next set of values.  
if flagTest ==’#NAME’: #Set flag according to '#" line delimiter  
flag=1  
elif flagTest == ‘#ROWS’:  
flag=2  
elif flagTest == ‘#TYPE’:  
flag=0  
fileinput.close()  
values=values[:-1] #Strip off last comma after all the values have been processed  
query=“INSERT INTO tblOMS\_AX " + columns + " VALUES “+ values  
query = query.replace(”\n”,"").replace("\r","")  
print query

# system.db.runUpdateQuery(query)

# sql = “UPDATE tblOMS\_AX SET WCID=?, ChangeBy=?, ChangeDate=GETDATE() WHERE WCID IS NULL”

# system.db.runPrepUpdate(sql, [wcid, changeby])

# 

# sql = “”"DECLARE @mycount INT

# SET @mycount = (SELECT count(\*) FROM tblOMS\_AX WHERE SortOrder IS NULL AND WCID = ?)

# WHILE @mycount \> 0

# BEGIN

# UPDATE TOP (1) tblOMS\_AX SET SortOrder=(SELECT ISNULL(MAX(SortOrder),0) + 1 FROM tblOMS\_AX WHERE WCID = ?) WHERE WCID=? AND SortOrder IS NULL

# SET @mycount = @mycount - 1

# END"""

# system.db.runPrepUpdate(sql, [wcid, wcid, wcid])

# 

# system.db.refresh(event.source.parent,“Data”)

# event.source.parent.Loading = 0

[/code]

---

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [December 17, 2014, 3:37pm UTC](https://forum.inductiveautomation.com/t/import-script/9457/4 "2014-12-17T15:37:51Z")

</div>

Works well, much more efficient. Thanks Jordan!

Final code:

[code]event.source.parent.Loading = 1  
wcid = event.source.parent.WCID  
changeby = system.tag.getTagValue("[Client]OverrideUsername")  
sql = “SELECT ISNULL(MAX(SortOrder),0) + 1 FROM tblOMS\_AX WHERE WCID = ‘%s’” %(wcid)  
sortOrder = system.db.runScalarQuery(sql, “AX\_MES”)

import fileinput

inputFile=system.file.openFile(‘csv’)

if inputFile != None:  
fileinput.close()  
values = “”  
flag = 0  
for lineIn in fileinput.input(inputFile):  
flagTest=lineIn[:-(len(lineIn)-5)] #Extract first five characters of the line.  
if flag==1:  
columns = ‘(’ + lineIn # Initial column list for query  
columnList = lineIn.replace("\n","").replace("\r","").split(’,’) # list of column names. We can use this  
# to check if a cloumn exists  
if ‘WCID’ not in columnList: # Check if WCID exists  
columns += ‘,WCID’ # Add to column Names, if needed  
if ‘ChangeBy’ not in columnList: # Check if ChangeBy exists  
columns += ‘,ChangeBy’ # Add to column Names, if needed  
if ‘ChangeDate’ not in columnList: # Check if SortOrder exists  
columns += ‘,ChangeDate’ # Add to column Names, if needed  
if ‘SortOrder’ not in columnList: # Check if SortOrder exists  
columns += ‘,SortOrder’ # Add to column Names, if needed  
if ‘Carrier’ not in columnList: # Check if Carrier exists  
columns += ‘,Carrier’ # Add to column Names, if needed  
columns += ‘)’ # Add right-hand parenthesis  
flag=0 #Reset flag  
if flag==2:  
values+="(’" + lineIn.replace(’"’,’’).replace(",","’,’") # Initial Values  
if “WCID” not in columnList: # Check if WCID exists  
values += “’,’” + str(wcid) # Add value, if needed  
if ‘ChangeBy’ not in columnList: # Check if ChangeBy exists  
values += “’,’”+ str(changeby) # Add value, if needed  
if ‘ChangeDate’ not in columnList: # Check if ChangeDate exists  
values += “’,”+ ‘GETDATE()’ # Add value, if needed  
if ‘Carrier’ not in columnList: # Check if Carrier exists  
values += “,”+ ‘0’ # Add value, if needed  
if ‘SortOrder’ not in columnList: # Check if SortOrder exists  
values += “,’” + str(sortOrder) # Add value, if needed  
sortOrder += 1  
values += “’),” # wrap in parentheses,  
# and pop a comma on the end.  
# The comma prepares for the next set of values.  
if flagTest ==’#NAME’: #Set flag according to '#" line delimiter  
flag=1  
elif flagTest == ‘#ROWS’:  
flag=2  
elif flagTest == ‘#TYPE’:  
flag=0  
fileinput.close()  
values=values[:-1] #Strip off last comma after all the values have been processed  
query=“INSERT INTO tblOMS\_AX " + columns + " VALUES “+ values  
query = query.replace(”\n”,"").replace("\r","")  
#print query  
system.db.runUpdateQuery(query)

system.db.refresh(event.source.parent,“Data”)  
event.source.parent.Loading = 0[/code]