# Bulk Insert csv into sql

**URL:** <https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276>\
**Category:** Ignition\
**Created:** [June 4, 2018, 12:11pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276 "2018-06-04T12:11:39Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![bwagner](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@bwagner](https://forum.inductiveautomation.com/u/bwagner)\
**Post date:** [June 4, 2018, 12:11pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/1 "2018-06-04T12:11:39Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [June 4, 2018, 2:02pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/2 "2018-06-04T14:02:41Z")

</div>

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](https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017)

---

<div class="post-metadata">

**Author:** ![bwagner](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@bwagner](https://forum.inductiveautomation.com/u/bwagner)\
**Post date:** [June 4, 2018, 3:40pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/3 "2018-06-04T15:40:28Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [June 4, 2018, 3:44pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/4 "2018-06-04T15:44:50Z")

</div>

Can you post the new error?

---

<div class="post-metadata">

**Author:** ![bwagner](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@bwagner](https://forum.inductiveautomation.com/u/bwagner)\
**Post date:** [June 4, 2018, 3:47pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/5 "2018-06-04T15:47:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [June 4, 2018, 4:01pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/6 "2018-06-04T16:01:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bwagner](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@bwagner](https://forum.inductiveautomation.com/u/bwagner)\
**Post date:** [June 4, 2018, 4:37pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/7 "2018-06-04T16:37:10Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [June 4, 2018, 4:51pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/8 "2018-06-04T16:51:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bwagner](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@bwagner](https://forum.inductiveautomation.com/u/bwagner)\
**Post date:** [June 4, 2018, 5:02pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/9 "2018-06-04T17:02:31Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [June 4, 2018, 5:08pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/10 "2018-06-04T17:08:01Z")

</div>

If you aren’t going to use arguments, why not use [system.db.runUpdateQuery](https://docs.inductiveautomation.com/display/DOC79/system.db.runUpdateQuery) instead?

---

<div class="post-metadata">

**Author:** ![bwagner](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@bwagner](https://forum.inductiveautomation.com/u/bwagner)\
**Post date:** [June 4, 2018, 5:35pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/11 "2018-06-04T17:35:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [June 4, 2018, 5:42pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/12 "2018-06-04T17:42:17Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![bwagner](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@bwagner](https://forum.inductiveautomation.com/u/bwagner)\
**Post date:** [June 4, 2018, 6:12pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/13 "2018-06-04T18:12:44Z")

</div>

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).

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [June 4, 2018, 6:27pm UTC](https://forum.inductiveautomation.com/t/bulk-insert-csv-into-sql/18276/14 "2018-06-04T18:27:28Z")

</div>

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