File/Folder name causes SQL Bulk Insert command to fail

Ignition Early Access
1

I've spent a couple days scratching my head trying to figure how to execute a SQL bulk insert command to get csv file data into a table. I was able to do the bulk insert from ssms but not from ignition. I finally figured out the problem this morning and thought I'd share.

When I ran the script

import  system

bulkinsert = "BULK INSERT UsersTemp FROM 'C:\bulk\test.csv' WITH (fieldterminator=',',firstrow=2)"
system.db.runUpdateQuery(bulkinsert,"MFGdbConnection")

I would get an error that including the line below:

Error executing system.db.runUpdateQuery(BULK INSERT UsersTemp FROM 'C:ulk est.csv'

I noticed the error was formatted odd. The '/b' from the folder name '/bulk' and the '/t' from the file name '/test' were changed to '' and ' '.

The resolution was to change the folder and file name to avoid the formatting issue and suddenly it works. My new file location is C:\sulk\sest.csv

Ignition Platform 8.0.0 (b2019040718)

2

That is not a bug. \ is an escape character. For example:

print "Hello\nWorld" will print on 2 lines.

You need to use "C:\\bulk\\test.csv"

1 Like
3

Another option is to mark the string as ‘raw’ by prefixing the string with a single letter r, which means Python will not process escape characters:

initial = 'C:\bulk\test.csv'
escaped = 'C:\\bulk\\test.csv'
raw = r'C:\bulk\test.csv'

print initial, repr(initial)
print escaped, repr(escaped)
print raw, repr(raw)

outputs:

>>>
C:ulk	est.csv 'C:\x08ulk\test.csv'
C:\bulk\test.csv 'C:\\bulk\\test.csv'
C:\bulk\test.csv 'C:\\bulk\\test.csv'
3 Likes
4

Thank you! I wish I knew this 2 days ago :smirk: