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

**URL:** <https://forum.inductiveautomation.com/t/scripting-importing-multiple-csv-and-inserting-into-sql-database-issue-only-first-row-inserting/49277>\
**Category:** Ignition\
**Created:** [July 27, 2021, 10:32pm UTC](https://forum.inductiveautomation.com/t/scripting-importing-multiple-csv-and-inserting-into-sql-database-issue-only-first-row-inserting/49277 "2021-07-27T22:32:27Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![gjaeschke](https://avatars.discourse-cdn.com/v4/letter/g/e47774/32.png) [@gjaeschke](https://forum.inductiveautomation.com/u/gjaeschke)\
**Post date:** [July 27, 2021, 10:32pm UTC](https://forum.inductiveautomation.com/t/scripting-importing-multiple-csv-and-inserting-into-sql-database-issue-only-first-row-inserting/49277/1 "2021-07-27T22:32:27Z")

</div>

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.

```auto
# 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) 

```

---

<div class="post-metadata">

**Author:** ![gjaeschke](https://avatars.discourse-cdn.com/v4/letter/g/e47774/32.png) [@gjaeschke](https://forum.inductiveautomation.com/u/gjaeschke)\
**Post date:** [July 28, 2021, 10:17pm UTC](https://forum.inductiveautomation.com/t/scripting-importing-multiple-csv-and-inserting-into-sql-database-issue-only-first-row-inserting/49277/2 "2021-07-28T22:17:00Z")

</div>

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

```auto
# 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) 

```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 11, 2024, 5:21pm UTC](https://forum.inductiveautomation.com/t/scripting-importing-multiple-csv-and-inserting-into-sql-database-issue-only-first-row-inserting/49277/3 "2024-10-11T17:21:44Z")

</div>

Split this topic on October 11, 2024, 5:21pm UTC

A post was split to a new topic: [Issue with script to read CSV into table](https://forum.inductiveautomation.com/t/issue-with-script-to-read-csv-into-table/97160)