Bulk Insert csv into sql

Ignition
1

I’m trying to bulk insert a csv file. I am using a timed gateway script. Below is the code, and the error I am getting. Any help is appreciated.

inputPath = ‘C:/IgniteFTP/ScheduleData/’

for fname in os.listdir(inputPath):
path = os.path.join(inputPath, fname)
if os.path.isdir(path):
continue
else:
bulkinsert = “”“BULK INSERT ScheduleData
FROM “”” + path + “”"
WHERE (
rowterminator = \n,
fieldterminator = ,^,
firstrow = 2 )
“”"
system.db.runPrepUpdate(bulkinsert)

ERROR
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:357)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:255)

at sun.reflect.GeneratedMethodAccessor105.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(BULK INSERT ScheduleData
FROM C:/IgniteFTP/ScheduleData/053018hw.LIS
WHERE (
rowterminator =
,
fieldterminator = ,^,
firstrow = 2 )
, [null], , , false, false)

2

At first glance it looks like you need to add apostrophes to your csv path as well as terminators. Also, use WITH instead of WHERE

https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017

3

It looks like I got it cleaned up to have the single quotes and the WITH, still getting the error. code changes below.

import os, shutil, csv
inputPath = ‘C:/IgniteFTP/ScheduleData/’
newLine = r’\n’

for fname in os.listdir(inputPath):
path = os.path.join(inputPath, fname)
if os.path.isdir(path):
# skip directories
continue
else:
bulkinsert = “”“BULK INSERT ScheduleData
FROM '”"" + path + “”"’
WITH (
rowterminator = ‘""" + newLine + “”"’,
fieldterminator = ‘,^’,
firstrow = 2
);"""
system.db.runPrepUpdate(bulkinsert)

4

Can you post the new error?

5

Here is the new error. Thanks for the help

Traceback (most recent call last):
File “<TimerScript:GatewayScripts/ScheduleData @1,000ms >”, line 18, in
FROM ‘C:/IgniteFTP/ScheduleData/053018hw.LIS’
WITH (
rowterminator = ‘\n’,
fieldterminator = ‘,^’,
firstrow = 2
);, [null], , , false, false)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:357)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:255)

at sun.reflect.GeneratedMethodAccessor105.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(BULK INSERT ScheduleData
FROM ‘C:/IgniteFTP/ScheduleData/053018hw.LIS’
WITH (
rowterminator = ‘\n’,
fieldterminator = ‘,^’,
firstrow = 2
);, [null], , , false, false)

7.9.4 (b2017082911)
Oracle Corporation 1.8.0_151

6

This is running as a gateway script, so you need to specify the database connection to use in system.db.runPrepUpdate.

1 Like
7

Different error now.

import os, shutil, csv
inputPath = ‘C:/IgniteFTP/ScheduleData/’
newLine = r’\n’

for fname in os.listdir(inputPath):
path = os.path.join(inputPath, fname)
if os.path.isdir(path):
# skip directories
continue
else:
bulkinsert = “”“BULK INSERT ScheduleData
FROM '”"" + path + “”"’
WITH (
rowterminator = ‘""" + newLine + “”"’,
fieldterminator = ‘,^’,
firstrow = 2
);"""
system.db.runPrepUpdate(bulkinsert, “SQLServer”)

Error
Traceback (most recent call last):
File “<TimerScript:GatewayScripts/ScheduleData @1,000ms >”, line 18, in
at com.inductiveautomation.ignition.common.TypeUtilities.coerce(TypeUtilities.java:1311)

at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.coerce(PyArgumentMap.java:108)

at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.interpretPyArgs(PyArgumentMap.java:66)

at com.inductiveautomation.ignition.common.script.builtin.PyArgumentMap.interpretPyArgs(PyArgumentMap.java:36)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:243)

at sun.reflect.GeneratedMethodAccessor105.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

java.lang.ClassCastException: java.lang.ClassCastException: Cannot coerce value ‘SQLServer’ into type: class [Ljava.lang.Object;

8

system.db.runPrepUpdate expects args as the second parameter and this is not optional. Database connection name goes after that.

9

I put null in as an argument, but now am getting a different error.
import os, shutil, csv
inputPath = ‘C:/IgniteFTP/ScheduleData/’
newLine = r’\n’
arg = ‘NULL’
for fname in os.listdir(inputPath):
path = os.path.join(inputPath, fname)
if os.path.isdir(path):
# skip directories
continue
else:
bulkinsert = “”“BULK INSERT ScheduleData
FROM '”"" + path + “”"’
WITH (
rowterminator = ‘""" + newLine + “”"’,
fieldterminator = ‘,^’,
firstrow = 2
);"""
system.db.runPrepUpdate(bulkinsert, [arg], “SQLServer”)

Traceback (most recent call last):
File “<TimerScript:GatewayScripts/ScheduleData @1,000ms >”, line 18, in
FROM ‘C:/IgniteFTP/ScheduleData/053018hw.LIS’
WITH (
rowterminator = ‘\n’,
fieldterminator = ‘,^’,
firstrow = 2
);, [NULL], SQLServer, , false, false)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:357)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:255)

at sun.reflect.GeneratedMethodAccessor105.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(BULK INSERT ScheduleData
FROM ‘C:/IgniteFTP/ScheduleData/053018hw.LIS’
WITH (
rowterminator = ‘\n’,
fieldterminator = ‘,^’,
firstrow = 2
);, [NULL], SQLServer, , false, false)

10

If you aren’t going to use arguments, why not use system.db.runUpdateQuery instead?

11

With runupdatequery it looks like i am getting the same error

Traceback (most recent call last):
File “<TimerScript:GatewayScripts/ScheduleData @1,000ms >”, line 18, in
FROM ‘C:/IgniteFTP/ScheduleData/053018hw.LIS’
WITH (
rowterminator = ‘\n’,
fieldterminator = ‘,^’,
firstrow = 2
);, SQLServer, , false)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:357)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:182)

at sun.reflect.GeneratedMethodAccessor405.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(BULK INSERT ScheduleData
FROM ‘C:/IgniteFTP/ScheduleData/053018hw.LIS’
WITH (
rowterminator = ‘\n’,
fieldterminator = ‘,^’,
firstrow = 2
);, SQLServer, , false)

7.9.4 (b2017082911)
Oracle Corporation 1.8.0_151

12

If you have SSMS installed can you try running the bulk insert statement directly there?

BULK INSERT ScheduleData
FROM 'C:/IgniteFTP/ScheduleData/053018hw.LIS'
WITH (
rowterminator = '\n',
fieldterminator = ',^',
firstrow = 2 
);
13

This is what I get when trying to run from ssms. I think it might have to do with the sql account not having access to the file
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file “C:\IgniteFTP\ScheduleData\053018hw.LIS” could not be opened. Operating system error code (null).

14

Yep, sounds like permissions issues. Please post back and let us know if that resolves everything!