Import script

Ignition
1

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 (479 Bytes)

2

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?

3

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]

4

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]