# Dataset to SQL **URL:** \ **Category:** Ignition\ **Created:** [March 5, 2014, 3:54pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107 "2014-03-05T15:54:26Z")\ **Posts on this page:** 15\ **Page:** 1
**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:** [March 5, 2014, 3:54pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/1 "2014-03-05T15:54:26Z")
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? ---
**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:** [March 5, 2014, 7:03pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/2 "2014-03-05T19:03:46Z")
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. 🙂 ---
**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:** [March 5, 2014, 7:59pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/3 "2014-03-05T19:59:17Z")
It’s SQLServer2008. The csv is attached. ---
**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:** [March 6, 2014, 12:12pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/4 "2014-03-06T12:12:13Z")
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] ``` ---
**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:** [March 6, 2014, 7:51pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/5 "2014-03-06T19:51:01Z")
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 ---
**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:** [March 6, 2014, 8:29pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/6 "2014-03-06T20:29:07Z")
At first glance it appears that there is no space between the table name and the column list. ---
**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:** [March 6, 2014, 8:31pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/7 "2014-03-06T20:31:37Z")
at second glance I also don’t see any values, but maybe we just can’t see any of that in the traceback. ---
**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\ **Post date:** [March 6, 2014, 8:40pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/8 "2014-03-06T20:40:30Z")
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: ```auto query = query.replace("\n","").replace("\r","") ``` ---
**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:** [March 6, 2014, 8:55pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/9 "2014-03-06T20:55:09Z")
No change with removing \n, \r from from query. How should I be defining ‘flag’? ---
**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\ **Post date:** [March 6, 2014, 8:58pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/10 "2014-03-06T20:58:31Z")
JordanCClark has it so a flag of 0 (zero) is ignored. You can initialize the “flag” variable before the for loop: ```auto 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) ``` ---
**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:** [March 6, 2014, 9:19pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/11 "2014-03-06T21:19:53Z")
Okay, that makes sense. Thanks. Still receive the same error “Incorrect syntax near ‘)’.” ---
**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:** [March 6, 2014, 10:24pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/12 "2014-03-06T22:24:37Z")
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. 😉 ---
**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:** [March 6, 2014, 10:34pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/13 "2014-03-06T22:34:20Z")
> [@JordanCClark](#): > > At first glance it appears that there is no space between the table name and the column list. [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: ```auto query="INSERT INTO table " + columns + " VALUES "+ values ``` you got this: ```auto query="INSERT INTO tbleOMS" + columns + " VALUES "+ values ``` Check to see if there is a space after the tablename: ```auto query="INSERT INTO tbleOMS " + columns + " VALUES "+ values ``` ---
**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:** [March 10, 2014, 2:05pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/14 "2014-03-10T14:05:24Z")
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 ---
**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:** [March 10, 2014, 6:02pm UTC](https://forum.inductiveautomation.com/t/dataset-to-sql/8107/15 "2014-03-10T18:02:27Z")
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: ```auto 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?