CSV Import Error - Rows Missing

Ignition
1

Hi -
I am having issues importing a data from a CSV to a database. I have had the below code work in other projects without issues, but for some reason not all CSV rows are being found when the script is ran. The CSV is updated daily and the goal is to run the delete/import daily as well. The only way I have found to solve the issue is to open the CSV(excel), save it(w/o any modification), and then it imports without issue. I believe it has something to do with the CSV file or how it is created, but am unable to find any differences when comparing it other CSV files we use. Has anyone seen an issue like this before?

ERROR:
Traceback (most recent call last):
File “event:actionPerformed”, line 11, in reupload
IOError: CSV invalid: rows missing. Found 1722, expected 3333.

Ignition v8.0.2 (b2019060511)
Java: Azul Systems, Inc. 11.0.3

if system.gui.confirm("Update NCCA Data?", "Update", 0):
	window = system.nav.openWindow('Popups/NCCA/Loading')
	system.nav.centerWindow(window)
	#Delete NCCA data
	system.db.runUpdateQuery("DELETE FROM B40_NCCA", "NX_Ignition")

def reupload():
		#import .csv data
		file_path = 'N:\ALLSHARE\Ignition\NCCA\ALL CAR REPORT - IGNITION.csv'
		data_string = system.file.readFileAsString(file_path)
		data = system.dataset.fromCSV(data_string)
		data2 = system.dataset.toPyDataSet(data)
		for index in range(len(data2)):
			rowData = list(data2[index])
			system.db.runPrepUpdate("INSERT INTO B40_NCCA (CarNumber, CarTitle, CarInvestigator, CarRespBusinessModule, CarPartNumber, CarIssueInd, CarCreateDate, CarDaysOpen, CarState, CarSevRating, CarDaysSinceUpdate, D3TargetDate, D4TargetDate, D5TargetDate, D6ImplementationTargetDate, D2Date, D3Date, D4Date, D5Date, D6Date, D7Date, CarLastUpdateDate, CarDataSource, CarSupplierNumber, CarSupplierName, CarOriginator, CarVerifier, CarChampion, CarSecondInvestigator, CarType) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", rowData, "NX_Ignition")
		#update time label
		date = system.date.now()
		system.tag.write('[default]NCCA/updateDateTime', date)
		system.nav.closeWindow('Popups/NCCA/Loading')
		system.gui.messageBox("CSV file successfully uploaded.")
		table = event.source.parent.getComponent('NCCA Table')
		system.db.refresh(table, "data")

system.util.invokeAsynchronous(reupload)
2

How is this CSV file being created/updated in the first place?
The error being thrown is because the reported row count in the (non-standard, Ignition-specific) header information is different from the actual number of rows in the file; that is, the file has a header that says the file should be 3333 rows, but the reader is getting a null line after 1722 rows. The most likely causes are A: the file hasn’t been saved completely at the time the reupload function is being run, or B: there’s a mixed up line ending (\n instead of \r\n) after 1722 rows that’s causing the reader to error out.

3

The CSV is being created(daily) from a Business Objects Query…I believe.
I would think the file is completely saved as it has been created hours before my attempt to import the data.

I opened the file in a text editor and everything appears to be correct. I don’t notice anything unusual around line 1722. Each line ends with a CRLF…

Should there be a commas shown in the header row when viewed in an editor? Would any characters within the text strings cause an issue?

#NAMES,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
CarNumber,CarTitle,CarInvestigator,CarRespBusinessModule,CarPartNumber,CarIssueInd,CarCreateDate,CarDaysOpen,CarState,CarSevRating,CarDaysSinceUpdate,D3TargetDate,D4TargetDate,D5TargetDate,D6ImplementationTargetDate,D2CarProblemIdentificationDate,D3Date,D4Date,D5Date,D6Date,D7Date,CarDaysSinceUpdate,CarDataSource,CarSupplierNumber,CarSupplierName,CarOrginator,CarVerifier,CarChampion,CarSecondInvestigator,CarType
#TYPES,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
i,str,str,str,str,str,date,i,str,str,i,date,date,date,date,date,date,date,date,date,date,date,str,str,str,str,str,str,str,str
#ROWS,3333,,,,,,,,,,,,,,,,,,,,,,,,,,,,