# Script error importing file to SQL datanase

**URL:** <https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828>\
**Category:** Ignition\
**Tags:** scripting, ignition81\
**Created:** [October 4, 2022, 11:32pm UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828 "2022-10-04T23:32:19Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![batman01793](https://avatars.discourse-cdn.com/v4/letter/b/858c86/32.png) [@batman01793](https://forum.inductiveautomation.com/u/batman01793)\
**Post date:** [October 4, 2022, 11:32pm UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/1 "2022-10-04T23:32:20Z")

</div>

I am trying to import a ttx file in to a sql table on change of file.

I am getting the following error

```auto
File "<module:Importing.File>", line 47, in CheckFileLanded Priority, WO, WC, Material, Description, Plant, StartTime_Planned, EndTime_Planned, Labour, Printed, Batch ) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 1, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 112901], IGNITION_SQL_SERVER, , false, false) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268) at jdk.internal.reflect.GeneratedMethodAccessor292.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO [dbo].[mes_work_orders_DEM2]( Priority, WO, WC, Material, Description, Plant, StartTime_Planned, EndTime_Planned, Labour, Printed, Batch ) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 1, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 112901], IGNITION_SQL_SERVER, , false, false)

Traceback (most recent call last):
  File "<TimerScript:BERLIN_QDDC/Importing/File @100,000ms >", line 1, in <module>
  File "<module:Importing.File>", line 47, in CheckFileLanded
						Priority,
						WO,
						WC,
						Material,
						Description,
						Plant,
						StartTime_Planned,
						EndTime_Planned,
						Labour,
						Printed,
						Batch
						) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 1, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 112901], IGNITION_SQL_SERVER, , false, false)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)
	at jdk.internal.reflect.GeneratedMethodAccessor292.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO [dbo].[mes_work_orders_DEM2](
						Priority,
						WO,
						WC,
						Material,
						Description,
						Plant,
						StartTime_Planned,
						EndTime_Planned,
						Labour,
						Printed,
						Batch
						) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 1, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 112901], IGNITION_SQL_SERVER, , false, false)

8.1.17 (b2022051210)
Azul Systems, Inc. 11.0.15`

`

```

Please see the gateway script here

````auto
type or paste code here
```import shutil, os, time
from datetime import datetime

fileINP = 'DEM2_01.txt'
baseDIR = '\FTP\\PowerBI\\ZMKBR02A\\'
fileDST = 'Processed_DEM2\\'
tagLast = '[MES_SCADA]Parameters/DED2_LAST_IMPORT_ORDER'

def CheckFileLanded():
	firstRow = True
	sql = 'SELECT COUNT(*) FROM mes_work_orders_DEM2_hist WHERE WO = ?'
	current = os.path.getmtime(baseDIR + fileINP) 
	i = 0
		
	if datetime.fromtimestamp(current).strftime('%Y-%m-%d %H:%M:%S') == system.tag.readBlocking(tagLast)[0].value:
		print('No New File')
		return

	system.tag.writeBlocking([tagLast], [datetime.fromtimestamp(current).strftime('%Y-%m-%d %H:%M:%S')])						
	system.util.getLogger('Import (WO_dem2)').info('Import Started at: {}'.format(system.date.format(system.date.now(),'yyyy-MM-dd HH:mm:ss')))	

	with open(baseDIR + fileINP,'r') as f:
		for line in f:
			i += 1
			cols = [x.strip() for x in line.replace('\n','').split('\t')]
			if firstRow:
				firstRow = False
				
				system.db.runUpdateQuery("DELETE FROM [dbo].[mes_work_orders_DEM2] WHERE [Status] = 'Import'")
				
				colPriority	= cols.index('Priority')
				colMRP = cols.index('MRP')
				colWC = cols.index('Sched.')
				colWO = cols.index('Order Numb')
				colPlant	= cols.index('Plnt')
				colMat = cols.index('Material')
				colDesc = cols.index('Description')
				colLabour	= cols.index('Labor')
				colStart	= cols.index('Sched. Sta')
				colFinish	= cols.index('Sched. Fin')
				colPrinted	= cols.index('Printed')
				colBatch	= cols.index('Batch')
			
			else:
				if system.db.runScalarPrepQuery(sql, [cols[colWO]]) == 0:
					system.db.runPrepUpdate("""INSERT INTO [dbo].[mes_work_orders_DEM2](
						Priority,
						WO,
						WC,
						Material,
						Description,
						Plant,
						StartTime_Planned,
						EndTime_Planned,
						Labour,
						Printed,
						Batch
						) VALUES(?,?,?,?,?,?,?,?,?,?,?)""",
						[
						cols[colPriority],
						cols[colWO],
						cols[colWC],
						cols[colMat],
						cols[colDesc],
						cols[colPlant],
						cols[colStart],
						cols[colFinish],
						cols[colLabour].replace(',',''),
						cols[colPrinted],
						cols[colBatch],
						])				
	f.close	
	
	system.util.getLogger('Import (WO_dem2)').info('Records ({}) imported at: {}'.format(i,system.date.format(system.date.now(),'yyyy-MM-dd HH:mm:ss')))
	shutil.copy(baseDIR + fileINP, baseDIR + fileDST + datetime.fromtimestamp(current).strftime('%Y%m%d%H%M%S_') + fileINP)

````

type or paste code here

```auto

Not sure why it's not working....
any ideas?
```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 4, 2022, 11:44pm UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/2 "2022-10-04T23:44:11Z")

</div>

Please provide the rest of the error, particularly the "caused by" section(s).

---

<div class="post-metadata">

**Author:** ![batman01793](https://avatars.discourse-cdn.com/v4/letter/b/858c86/32.png) [@batman01793](https://forum.inductiveautomation.com/u/batman01793)\
**Post date:** [October 5, 2022, 6:40am UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/3 "2022-10-05T06:40:09Z")

</div>

```auto
at org.python.core.PyException.doRaise(PyException.java:211)

at org.python.core.Py.makeException(Py.java:1638)

at org.python.core.Py.makeException(Py.java:1642)

at org.python.core.Py.makeException(Py.java:1646)

at org.python.core.Py.makeException(Py.java:1650)

at org.python.pycode._pyx112.CheckFileLanded$1(:76)

at org.python.pycode._pyx112.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:173)

at org.python.core.PyBaseCode.call(PyBaseCode.java:119)

at org.python.core.PyFunction. __call__ (PyFunction.java:406)

at org.python.pycode._pyx111.f$0(:1)

at org.python.pycode._pyx111.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:173)

at org.python.core.PyCode.call(PyCode.java:18)

at org.python.core.Py.runCode(Py.java:1687)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:788)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:797)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:740)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:778)

at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:90)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: org.python.core.PyException: java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO [dbo].[mes_work_orders_DEM2]( Priority, WO, WC, Material, Description, Plant, StartTime_Planned, EndTime_Planned, Labour, Printed, Batch ) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 1, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 112901], IGNITION_SQL_SERVER, , false, false)

... 26 common frames omitted`Preformatted text`

```

---

<div class="post-metadata">

**Author:** ![batman01793](https://avatars.discourse-cdn.com/v4/letter/b/858c86/32.png) [@batman01793](https://forum.inductiveautomation.com/u/batman01793)\
**Post date:** [October 5, 2022, 7:47am UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/4 "2022-10-05T07:47:29Z")

</div>

```auto
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 1, in File "", line 47, in CheckFileLanded Priority, WO, WC, Material, Description, Plant, StartTime_Planned, EndTime_Planned, Labour, Printed, Batch ) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 001, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 0000112901], IGNITION_SQL_SERVER, , false, false) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268) at jdk.internal.reflect.GeneratedMethodAccessor292.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO [dbo].[mes_work_orders_DEM2]( Priority, WO, WC, Material, Description, Plant, StartTime_Planned, EndTime_Planned, Labour, Printed, Batch ) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 001, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 0000112901], IGNITION_SQL_SERVER, , false, false)

at org.python.core.PyException.doRaise(PyException.java:211)

at org.python.core.Py.makeException(Py.java:1638)

at org.python.core.Py.makeException(Py.java:1642)

at org.python.core.Py.makeException(Py.java:1646)

at org.python.core.Py.makeException(Py.java:1650)

at org.python.pycode._pyx119.CheckFileLanded$1(:76)

at org.python.pycode._pyx119.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:173)

at org.python.core.PyBaseCode.call(PyBaseCode.java:119)

at org.python.core.PyFunction. __call__ (PyFunction.java:406)

at org.python.pycode._pyx118.f$0(:1)

at org.python.pycode._pyx118.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:173)

at org.python.core.PyCode.call(PyCode.java:18)

at org.python.core.Py.runCode(Py.java:1687)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:788)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:797)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:740)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:778)

at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:90)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: org.python.core.PyException: java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO [dbo].[mes_work_orders_DEM2]( Priority, WO, WC, Material, Description, Plant, StartTime_Planned, EndTime_Planned, Labour, Printed, Batch ) VALUES(?,?,?,?,?,?,?,?,?,?,?), [0, 105538664, 001, 35429-00, TOWERLATCH S.P., GBM1, 10/12/2022, 10/17/2022, 18.333, X, 0000112901], IGNITION_SQL_SERVER, , false, false)

... 26 common frames omitted

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [October 5, 2022, 8:25am UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/5 "2022-10-05T08:25:03Z")

</div>

I don't know why the query fails, but this:

> [@batman01793](#):
>
> `f.close	`

Is not correct.  
First, it should be `.close()`, and second you're using a context manager (`with open() as f:`), which takes care of closing the file.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 5, 2022, 11:56am UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/6 "2022-10-05T11:56:52Z")

</div>

A few things.

- I would move away from using `datetime` and just use the `system.date.*` functions, however, putting that aside, you should really move away from storing dates/times as strings. Store them as actual dates and only convert them to strings when you need them to be strings.
- Don't use `else: if` instead use `elif:`

Finally, what I think may be the actual problem.

On the first line of the file you define variables for the positions of the various columns in the file. e.g( `colPriority = cols.index('Priority')`) however, those variables are within the scope of that `if` statement. You should define them in a scope that is available to both the `if` and the `else`.

---

<div class="post-metadata">

**Author:** ![batman01793](https://avatars.discourse-cdn.com/v4/letter/b/858c86/32.png) [@batman01793](https://forum.inductiveautomation.com/u/batman01793)\
**Post date:** [October 5, 2022, 3:35pm UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/7 "2022-10-05T15:35:32Z")

</div>

Thanks all for the replies - I have tried a few of the options such as elif: and removing the f.close etc... and im still at a loss.

Perhaps I could reach out for some "how would you do it" advice

So I would like to  
1: monitor a FTP file location for a file to see when it has changed  
2: if it has changed Delete the contents of a SQL table and replace with the contents of the file  
3: if not do nothing (note I dont need to check the SQL table for the contents just want to ensure that the SQL table always reflects the contents of the file)

I think I have overcomplicated it - Truth be told I took an existing script that did the above and then transferred it to a new project to do the same - but I am doing something wrong....

Please see the original (working script) that I have in another project listed below  
I just simply pointed to a new memory tag that I created and pointed to new tables (these were copies of the original tables just given new names - so in my head I thought all would work fine lol)

```auto
import shutil, os, time
from datetime import datetime

fileINP = 'GBM1_01.txt'
baseDIR = '\\\\Uscl-as02\\dfs\\SAP\\SAPFTP-Master\\SAPFTP\\PowerBI\\ZMKBR02A\\'
#baseDIR = 'C:\\Temp\\' #Used for testing
fileDST = 'Processed\\'
tagLast = '[MES_SCADA]Parameters/LAST_IMPORT_ORDER'

def CheckFileLanded():
	firstRow = True
	sql = 'SELECT COUNT(*) FROM mes_work_orders_hist WHERE WO = ?'
	current = os.path.getmtime(baseDIR + fileINP) 
	i = 0
		
	if datetime.fromtimestamp(current).strftime('%Y-%m-%d %H:%M:%S') == system.tag.readBlocking(tagLast)[0].value:
		print('No New File')
		return

	system.tag.writeBlocking([tagLast], [datetime.fromtimestamp(current).strftime('%Y-%m-%d %H:%M:%S')])						
	system.util.getLogger('Import (WO)').info('Import Started at: {}'.format(system.date.format(system.date.now(),'yyyy-MM-dd HH:mm:ss')))	

	with open(baseDIR + fileINP,'r') as f:
		for line in f:
			i += 1
			cols = [x.strip() for x in line.replace('\n','').split('\t')]
			if firstRow:
				firstRow = False
				
				system.db.runUpdateQuery("DELETE FROM [dbo].[mes_work_orders] WHERE [Status] = 'Import'")
				
				colPriority	= cols.index('Priority')
				colMRP = cols.index('MRP')
				colWC = cols.index('Sched.')
				colWO = cols.index('Order Numb')
				colPlant	= cols.index('Plnt')
				colMat = cols.index('Material')
				colDesc = cols.index('Description')
				colLabour	= cols.index('Labor')
				colStart	= cols.index('Sched. Sta')
				colFinish	= cols.index('Sched. Fin')
				colPrinted	= cols.index('Printed')
				colBatch	= cols.index('Batch')
			
			else:
				if system.db.runScalarPrepQuery(sql, [cols[colWO]]) == 0:
					system.db.runPrepUpdate("""INSERT INTO [dbo].[mes_work_orders](
						Priority,
						WO,
						WC,
						Material,
						Description,
						Plant,
						StartTime_Planned,
						EndTime_Planned,
						Labour,
						Printed,
						Batch
						) VALUES(?,?,?,?,?,?,?,?,?,?,?)""",
						[
						cols[colPriority],
						cols[colWO],
						cols[colWC],
						cols[colMat],
						cols[colDesc],
						cols[colPlant],
						cols[colStart],
						cols[colFinish],
						cols[colLabour].replace(',',''),
						cols[colPrinted],
						cols[colBatch],
						])				
	f.close	
	
	system.util.getLogger('Import (WO)').info('Records ({}) imported at: {}'.format(i,system.date.format(system.date.now(),'yyyy-MM-dd HH:mm:ss')))
	shutil.copy(baseDIR + fileINP, baseDIR + fileDST + datetime.fromtimestamp(current).strftime('%Y%m%d%H%M%S_') + fileINP)

```

type or paste code here

```auto

```

---

<div class="post-metadata">

**Author:** ![batman01793](https://avatars.discourse-cdn.com/v4/letter/b/858c86/32.png) [@batman01793](https://forum.inductiveautomation.com/u/batman01793)\
**Post date:** [October 6, 2022, 1:33pm UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/9 "2022-10-06T13:33:14Z")

</div>

I have been working on this again last night and this morning & feel i am moving forward a little

I am now however seeing the following error

I notice that the file is showing "" rather than a name - not sure why this would be

anyone see anything obvious

```auto
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 1, in File "", line 40, in CheckFileLanded ValueError: list.index(x): x not in list

at org.python.core.PyException.doRaise(PyException.java:211)

at org.python.core.Py.makeException(Py.java:1638)

at org.python.core.Py.makeException(Py.java:1642)

at org.python.core.Py.makeException(Py.java:1646)

at org.python.core.Py.makeException(Py.java:1650)

at org.python.pycode._pyx149.CheckFileLanded$1(:76)

at org.python.pycode._pyx149.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:173)

at org.python.core.PyBaseCode.call(PyBaseCode.java:119)

at org.python.core.PyFunction. __call__ (PyFunction.java:406)

at org.python.pycode._pyx148.f$0(:1)

at org.python.pycode._pyx148.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:173)

at org.python.core.PyCode.call(PyCode.java:18)

at org.python.core.Py.runCode(Py.java:1687)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:788)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:797)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:740)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:778)

at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:90)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: org.python.core.PyException: ValueError: list.index(x): x not in list

... 26 common frames omitted

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 6, 2022, 1:41pm UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/10 "2022-10-06T13:41:19Z")

</div>

Have you modified the code from the original post? If so, show that script.

The error is saying that it couldn't find X in the list. Presumably this is in a comprehension of some sort, with out the script it's hard to say.

The two scripts you have posted have no `list.index(x)` in them.

Out of curiosity, is there some third party software that is creating these text files you're parsing, or are you creating them in another way?

---

<div class="post-metadata">

**Author:** ![batman01793](https://avatars.discourse-cdn.com/v4/letter/b/858c86/32.png) [@batman01793](https://forum.inductiveautomation.com/u/batman01793)\
**Post date:** [October 6, 2022, 2:29pm UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/11 "2022-10-06T14:29:16Z")

</div>

Hi I initially modified the script - then went back to the original as I was getting other errors - so wanted to go back to a script that works in another project & just modify the tables that it reviews and stores to.

The file itself is a Text file that is an extract from SAP that runs to an FTP location every two hours.

The script looks for the file date change then uploads the file to SQL.

I have this working on another project so wanted to copy over to a new project but with a different source file and different destination tables.  
I have tried by copying the original file and use that as the source to check that there is nothing wrong with the source, I have also checked the SQL table setup and all keys and indexes are duplicated so in theory the only difference is the table names have DEM added to them and the source file is DEM2\_01.TXT instead of GBM1\_01.TXT

Please see code being used below

```auto
import shutil, os, time
from datetime import datetime

fileINP = 'DEM2_01.txt'
baseDIR = '\\\\Uscl-as02\\dfs\\SAP\\SAPFTP-Master\\SAPFTP\\PowerBI\\ZMKBR02A\\'
#baseDIR = 'C:\\Temp\\' #Used for testing
fileDST = 'Processed_DEM2\\'
tagLast = '[MES_SCADA]Parameters/DED2_LAST_IMPORT_ORDER'

def CheckFileLanded():
	firstRow = True
	sql = 'SELECT COUNT(*) FROM mes_work_orders_DEM2_hist WHERE WO = ?'
	current = os.path.getmtime(baseDIR + fileINP) 
	i = 0
		
	if datetime.fromtimestamp(current).strftime('%Y-%m-%d %H:%M:%S') == system.tag.readBlocking(tagLast)[0].value:
		print('No New File')
		return

	system.tag.writeBlocking([tagLast], [datetime.fromtimestamp(current).strftime('%Y-%m-%d %H:%M:%S')])						
	system.util.getLogger('Import (WO)').info('Import Started at: {}'.format(system.date.format(system.date.now(),'yyyy-MM-dd HH:mm:ss')))	

	with open(baseDIR + fileINP,'r') as f:
		for line in f:
			i += 1
			cols = [x.strip() for x in line.replace('\n','').split('\t')]
			if firstRow:
				firstRow = False
				
				system.db.runUpdateQuery("DELETE FROM [dbo].[mes_work_orders_DEM2] WHERE [Status] = 'Import'")
				
				colPriority	= cols.index('Priority')
				colMRP = cols.index('MRP')
				colWC = cols.index('Sched.')
				colWO = cols.index('Order Numb')
				colPlant	= cols.index('Plnt')
				colMat = cols.index('Material')
				colDesc = cols.index('Description')
				colLabour	= cols.index('Labor')
				colStart	= cols.index('Sched. Sta')
				colFinish	= cols.index('Sched. Fin')
				colPrinted	= cols.index('Printed')
				colBatch	= cols.index('Batch')
			
			else:
				if system.db.runScalarPrepQuery(sql, [cols[colWO]]) == 0:
					system.db.runPrepUpdate("""INSERT INTO [dbo].[mes_work_orders_DEM2](
						Priority,
						WO,
						WC,
						Material,
						Description,
						Plant,
						StartTime_Planned,
						EndTime_Planned,
						Labour,
						Printed,
						Batch
						) VALUES(?,?,?,?,?,?,?,?,?,?,?)""",
						[
						cols[colPriority],
						cols[colWO],
						cols[colWC],
						cols[colMat],
						cols[colDesc],
						cols[colPlant],
						cols[colStart],
						cols[colFinish],
						cols[colLabour].replace(',',''),
						cols[colPrinted],
						cols[colBatch],
						])				
	f.close	
	
	system.util.getLogger('Import (WO)').info('Records ({}) imported at: {}'.format(i,system.date.format(system.date.now(),'yyyy-MM-dd HH:mm:ss')))
	shutil.copy(baseDIR + fileINP, baseDIR + fileDST + datetime.fromtimestamp(current).strftime('%Y%m%d%H%M%S_') + fileINP)

```

I can see the Tag is being updated with the latest file date and time stamp - I can trigger the event if I manually change the date in the tag. but then I get the fail message...

```auto

```

---

<div class="post-metadata">

**Author:** ![batman01793](https://avatars.discourse-cdn.com/v4/letter/b/858c86/32.png) [@batman01793](https://forum.inductiveautomation.com/u/batman01793)\
**Post date:** [October 6, 2022, 2:45pm UTC](https://forum.inductiveautomation.com/t/script-error-importing-file-to-sql-datanase/64828/12 "2022-10-06T14:45:15Z")

</div>

FINALLY FOUND OUT WHAT IT WAS!!!!  
So when I copied the tables over I missed out a default binding on one of the columns!  
such a school boy error...n but hey we all learn by doing!

Thank you all for your patience and responses!