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)