Scripting importing multiple csv and inserting into sql Database (Issue only first row inserting)

Ignition
1

I’m having issues with with my script with only inserting the first row of each csv into the database, as I need all rows of each csv to be inserted.

# Importing the appropriate java libraries.
from java.lang import System
from java.io import File
import os, shutil
import time
import csv

# This sets up an extension filter that can check the file extension. Txt is the default.
class ExtensionFilter(FilenameFilter):
   def __init__(self, extension=".csv"):
      self.extension=extension.lower()
 
   def accept(self, directory, name):
      # make sure that the filename ends in the right extension
      return name.lower().endswith(self.extension)
 
# Used to look up the files in the users home directory.
homePath = '\\\\scadacentral01\\Data\\d\\datalog\\LeakTestLog\\'
homeDir = File(homePath)
archivePath = '\\\\scadacentral01\\Data\\d\\datalog\\OUTARCHIVE\\'
 
# Prints out all .csv files. Txt is provided if nothing is specified.
for filename in homeDir.list(ExtensionFilter()):
	path = homePath + filename
	# Create a reader object that will iterate over the lines of a CSV.
	# We're using Python's built-in open() function to open the file.
	csvData = csv.reader(open(path,'rU')) 
	# Create a List of strings to use as a header for the dataset. Note that the number
	# of headers must match the number of columns in the CSV, otherwise an error will occur.
	# The simplest approach would be to use next() to read the first line in the file, and
	# store that at the header.
	header = csvData.next()
	# Create a dataset with the header and the rest of our CSV.
	dataset = system.dataset.toDataSet(header ,list(csvData))
	
	# Convert to PyDataset
	Data = system.dataset.toPyDataSet(dataset)
	
	# Start
	txID = system.db.beginTransaction(timeout=5000)
	
	# Loop through the rows in the dataset, and insert them into the database.
	for rows in Data:
		column1 = rows['Date']
		column2 = rows['Time']
		column3 = rows['Site']
		column4 = rows['Room']
		column5 = rows['Tester']
		column6 = rows['mmH2O']
		column7 = rows['Duration']
		column8 = rows['Step']
	
    	sql = """
		INSERT INTO leak_test_data
			(Data_Date, Data_Time, Data_Site, Data_Room, Data_Tester, Data_Press, Data_Dur, Data_Step)
		VALUES
			(?, ?, ?, ?, ?, ?, ?, ?)
    	"""
    	
    	system.db.runPrepUpdate(sql, [column1, column2, column3, column4, column5, column6, column7, column8], tx=txID)
		
	# Commit and close
	system.db.commitTransaction(txID)
	system.db.closeTransaction(txID)	
	time.sleep(1) # Sleep for 1 seconds
for filename in homeDir.list(ExtensionFilter()):
	shutil.move(homePath + filename, archivePath + filename)
2

I fixed and you the issue, I had a typo. updated code below with some other filter features if anyone is interested.

# Importing the appropriate java libraries.
from java.lang import System
from java.io import File
from java.io import FilenameFilter
import os, shutil
import time
import csv

# This sets up an extension filter that can check the file extension. Txt is the default.
class ExtensionFilter(FilenameFilter):
   def __init__(self, extension=".csv"):
      self.extension=extension.lower()
 
   def accept(self, directory, name):
      # make sure that the filename ends in the right extension
      return name.lower().endswith(self.extension)
 
# Used to look up the files in the users home directory.
homePath = '\\\\scadacentral01\\Data\\d\\datalog\\LeakTestLog\\'
homeDir = File(homePath)
archivePath = '\\\\scadacentral01\\Data\\d\\datalog\\OUTARCHIVE\\'
 
# Prints out all .csv files. Txt is provided if nothing is specified.
for filename in homeDir.list(ExtensionFilter()):
	path = open(homePath + filename,'rU')
	# Create a reader object that will iterate over the lines of a CSV.
	# We're using Python's built-in open() function to open the file.
	csvData = csv.reader(path) 
	# Create a List of strings to use as a header for the dataset. Note that the number
	# of headers must match the number of columns in the CSV, otherwise an error will occur.
	# The simplest approach would be to use next() to read the first line in the file, and
	# store that at the header.
	header = csvData.next()
	# Create a dataset with the header and the rest of our CSV.
	dataset = system.dataset.toDataSet(header ,list(csvData))
	
	# Convert to PyDataset
	Data = system.dataset.toPyDataSet(dataset)
	
	# Start
	txID = system.db.beginTransaction(timeout=5000)
	
	# Loop through the rows in the dataset, and insert them into the database.
	for row in Data:
		column1 = row['Date']
		column2 = row['Time']
		column3 = row['Site']
		column4 = row['Room']
		column5 = row['Tester']
		column6 = row['mmH2O']
		column7 = row['Duration']
		column8 = row['Step']
		if int(column8) >= 1:
			sql = "INSERT INTO leak_test_data (Data_Date, Data_Time, Data_Site, Data_Room, Data_Tester, Data_Press, Data_Dur, Data_Step) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
			arguments = [column1, column2, column3, column4, column5, column6, column7, column8]
			system.db.runPrepUpdate(sql, arguments, tx=txID)
			
	# Commit and close
	system.db.commitTransaction(txID)
	system.db.closeTransaction(txID)
	path.close()	
	time.sleep(1) # Sleep for 3 seconds
for filename in homeDir.list(ExtensionFilter()):
	time.sleep(1)
	shutil.move(homePath + filename, archivePath + filename)
1 Like
3

A post was split to a new topic: Issue with script to read CSV into table