Dataset to SQL

Tim · March 5, 2014, 3:54pm

I would like to import data from CSV and insert into MSSQL table. I can import the CSV to a dataset and display in a table component throught the following code:

path = system.file.openFile("csv") if path != None: csv = system.file.readFileAsString(path) csv = csv.replace('\r','') data = system.dataset.fromCSV(csv) event.source.parent.getComponent('Table').data = data

The table component is bound to a SQL table so what I really need to do is insert the data (multiple rows) into the table, but have not found a way to do this. Any suggestions?

JordanCClark · March 5, 2014, 7:03pm

Well, MySQL and SQLServer2008 and later can use something like:

INSERT INTO example (example_id, name, value, other_value) VALUES (100, 'Name 1', 'Value 1', 'Other 1'), (101, 'Name 2', 'Value 2', 'Other 2'), (102, 'Name 3', 'Value 3', 'Other 3'), (103, 'Name 4', 'Value 4', 'Other 4');

Pre-Server2008 would use something like:

INSERT INTO example(example_id, name, value, other_value) SELECT 100, 'Name 1', 'Value 1', 'Other 1' UNION ALL SELECT 101, 'Name 2', 'Value 2', 'Other 2' UNION ALL SELECT 102, 'Name 3', 'Value 3', 'Other 3' UNION ALL SELECT 103, 'Name 4', 'Value 4', 'Other 4'

If you let us know what you’re using, and an example of what is coming in for the csv, I might be able to do something a bit more definitive.

Tim · March 5, 2014, 7:59pm

It’s SQLServer2008. The csv is attached.

JordanCClark · March 6, 2014, 12:12pm

Sorry, Tim, had a machine runoff to finish yesterday.

I’m going to assume that you want all of the values to go into the table (OMSID, WCID, etc).

Using fileinput will let us process the file line by line.

The portion to write to the database is commented out, so you can see the how the query will look in the Script Playground before trying to write to the database.

Hope this helps!

[code]import fileinput

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

if inputFile != None:
fileinput.close()
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 table " + columns + " VALUES "+ values

print query
#system.db.runUpdateQuery(query)
[/code]

Tim · March 6, 2014, 7:51pm

Thanks Jordan. This is very helpful. Can you provide some insight into the error I receive:

Traceback (most recent call last):
File “event:actionPerformed”, line 47, in
), , , false)

java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO tbleOMS(OMSID,WCID,FilePath,POI,POIType,Critical,SortOrder,EffStartDate,EffEndDate,ChangeBy,ChangeDate,Found,FileFound
), , , false)

caused by Exception: Error executing system.db.runUpdateQuery(INSERT INTO tbleOMS(OMSID,WCID,FilePath,POI,POIType,Critical,SortOrder,EffStartDate,EffEndDate,ChangeBy,ChangeDate,Found,FileFound

), , , false)
caused by GatewayException: SQL error for “INSERT INTO tbleOMS(OMSID,WCID,FilePath,POI,POIType,Critical,SortOrder,EffStartDate,EffEndDate,ChangeBy,ChangeDate,Found,FileFound
)”: Incorrect syntax near ‘)’.
caused by SQLServerException: Incorrect syntax near ‘)’.

Ignition v7.6.4 (b2013112117)
Java: Sun Microsystems Inc. 1.6.0_31

JordanCClark · March 6, 2014, 8:29pm

At first glance it appears that there is no space between the table name and the column list.

JordanCClark · March 6, 2014, 8:31pm

at second glance I also don’t see any values, but maybe we just can’t see any of that in the traceback.

adamaustin · March 6, 2014, 8:40pm

I bet the problem is the newlines and line feeds. (The query analyzer might be expecting the entire query on “one line”.) Try getting rid of those:

query = query.replace("\n","").replace("\r","")

Tim · March 6, 2014, 8:55pm

No change with removing \n, \r from from query. How should I be defining ‘flag’?

adamaustin · March 6, 2014, 8:58pm

JordanCClark has it so a flag of 0 (zero) is ignored. You can initialize the “flag” variable before the for loop:

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 table " + columns + " VALUES "+ values
	query = query.replace("\n","").replace("\r","")
	print query
	#system.db.runUpdateQuery(query)

Tim · March 6, 2014, 9:19pm

Okay, that makes sense. Thanks. Still receive the same error “Incorrect syntax near ‘)’.”

JordanCClark · March 6, 2014, 10:24pm

Thanks for catching the flag initialization, Adam!

Try running it from the Script Playground with the print function at the bottom. Then post what the output is.

JordanCClark · March 6, 2014, 10:34pm

[quote="Tim"]
[snip] ...caused by GatewayException: SQL error for "INSERT INTO tbleOMS(OMSID,WCID,FilePath,POI,POIType,Critical,SortOrder,EffStartDate,EffEndDate,ChangeBy,ChangeDate,Found,FileFound
)": Incorrect syntax near ')'.[/quote]

There is no space between tableOMS and the open parentheses. It needs one. If I were to guess, when you replaced:

query="INSERT INTO table " + columns + " VALUES "+ values

you got this:

query="INSERT INTO tbleOMS" + columns + " VALUES "+ values

Check to see if there is a space after the tablename:

query="INSERT INTO tbleOMS " + columns + " VALUES "+ values

Tim · March 10, 2014, 2:05pm

There was not a space between tblOMS and the open parentheses, also a typo existed in the table name. But when corrected receive “Incorrect syntax near ‘VALUES’” error message. It does not appear that values is working as anticipated. Output:

INSERT INTO tblOMS (OMSID,WCID,FilePath,POI,POIType,Critical,SortOrder,EffStartDate,EffEndDate,ChangeBy,ChangeDate,Found,FileFound) VALUES

JordanCClark · March 10, 2014, 6:02pm

Ok, there are the contents of the sample csv file:

#NAMES,,,,,,,,,,,, OMSID,WCID,FilePath,POI,POIType,Critical,SortOrder,EffStartDate,EffEndDate,ChangeBy,ChangeDate,Found,FileFound #TYPES,,,,,,,,,,,, I,I,str,str,str,B,I,date,date,str,date,B,B #ROWS,1,,,,,,,,,,, 5,2,//fhxnas01/pcshare/aseeder/1870_WPLANNER_OMS/1761-16-1.jpg,"""1890c""",Model,1,1,2/12/2014 0:00,12/31/9999 0:00,jb20449,2/20/2014 14:44,0,0 3,2,//fhxnas01/pcshare/aseeder/1870_WPLANNER_OMS/1759-11-1.jpg,977ZH,Base Machine,1,2,2/5/2014 0:00,12/31/9999 0:00,jb20449,2/17/2014 13:25,0,0 4,2,//fhxnas01/pcshare/aseeder/1870_WPLANNER_OMS/1817-12-1.jpg,*,,1,3,2/6/2014 0:00,12/31/9999 0:00,jb20449,2/12/2014 13:47,0,0

This is the output from the script modified by Adam, and using the sample:

INSERT INTO table (OMSID,WCID,FilePath,POI,POIType,Critical,SortOrder,EffStartDate,EffEndDate,ChangeBy,ChangeDate,Found,FileFound) VALUES ('5','2','//fhxnas01/pcshare/aseeder/1870_WPLANNER_OMS/1761-16-1.jpg','1890c','Model','1','1','2/12/2014 0:00','12/31/9999 0:00','jb20449','2/20/2014 14:44','0','0'),('3','2','//fhxnas01/pcshare/aseeder/1870_WPLANNER_OMS/1759-11-1.jpg','977ZH','Base Machine','1','2','2/5/2014 0:00','12/31/9999 0:00','jb20449','2/17/2014 13:25','0','0'),('4','2','//fhxnas01/pcshare/aseeder/1870_WPLANNER_OMS/1817-12-1.jpg','*','','1','3','2/6/2014 0:00','12/31/9999 0:00','jb20449','2/12/2014 13:47','0','0')

This may sound silly, but are there any rows after the #ROWS delimiter in the csv file?