Probleme to Read csv file

I'am reading csv fille but the format of data that I'am getting is not correct
this my csv data:

This the script that I'am using:

import csv

def LireFichierConfiCamera():

	ls_CheminAcces  = "C:\Users\Administrateur\Documents\FichierConfig\\test.csv" 

	csvData = csv.reader(open(ls_CheminAcces))

	header =

	dataset = system.dataset.toDataSet(header ,list(csvData))

	return dataset

Result obtened on the table :

Something is wrong in script ?

csv = Comma seperated value
; is not a ,

1 Like

Just specify the delimiter

csvData = csv.reader(open(ls_CheminAcces),delimiter=';')


I think it would be smarter to change the csv file to be a real csv.
Or atleast test of both methodes so that it will work for real csv files too.
csv has sniffer to find the delimiter

thisFile = open(ls_CheminAcces)
thisDelimiter = csv.Sniffer().sniff(thisFile.readline()).delimiter #back to line 0
csvData = csv.reader(thisFile,delimiter=thisDelimiter)
header =
dataset = system.dataset.toDataSet(header ,list(csvData))
1 Like

If they are the one making the CSV in the first place then yes, but if he's making it presumably its provide information for someone outside the system who may be expecting the ;. And also if he's making it, he presumably is doing from a query or some data source he has direct access too - he should just be accessing that in that case instead of reading from the data source, making a csv, then reading from the csv the data back.

If he's not making the csv to begin with and is receiving it from an external source which is what it seems like, I would not modify it before reading it, you don't know if any of the data includes a , in it which may be why they're using ; as a delimiter in the first place. I feel like this is potentially just introducing more problems without adding any value.


Hello, basically I have an Excel file. This Excel.xlsx file which will be converted to CSV.csv using an executable (the executable I haven't done yet).

Once the Excel file is converted, I will call the function on Gateway Startup Script because CSV dataset will be written on a tag.

I use this script read csv and write data to a tag dataset

def LireFichierConfiCamera():

	ls_CheminAcces  = "C:\Users\test12.csv"
	lo_CsvData = csv.reader(open(ls_CheminAcces),delimiter=';')

	ls_listeHeader =

	lo_Dataset = system.dataset.toDataSet(ls_listeHeader ,list(lo_CsvData))
	system.tag.writeBlocking("[Commun]DataWebCam", [lo_Dataset])
	return lo_Dataset

I call LireFichierConfiCamera() funtion on Gateway on Sartup script

it work but I don't if it is the best solution

Probably not. I would have your script read the Excel file directly, instead of relying on a double conversion (to CSV, then to Ignition dataset). There are a number of examples on this forum of using Ignition's Apache POI libraries to read Excel files.


Yes @pturmel I have founded some exemple but I also founded problem.

For exemple, I have a script that can read excel file but it can't readother sheetNumber.

def excelToDataSet(fileName, hasHeaders = False, sheetNum = 0, firstRow = None, lastRow = None, firstCol = None, lastCol = None):
	import as WorkbookFactory
	import as DateUtil
	from import FileInputStream
	from java.util import Date
	   Function to create a dataset from an Excel spreadsheet. It will try to automatically detect the boundaries of the data,
	   but helper parameters are available:
	   		fileName   - The path to the Excel spreadsheet. (required)
	   		hasHeaders - If true, uses the first row of the spreadsheet as column names.
	   		sheetNum   - select the sheet to process. defaults to the first sheet.
	   		firstRow   - select first row to process. 
	   		lastRow    - select last row to process.
	   		firstCol   - select first column to process
	   		lastCol    - select last column toprocess
	fileStream = FileInputStream(fileName)

	wb = WorkbookFactory.create(fileStream)
	sheet = wb.getSheetAt(sheetNum)

	if firstRow is None:
		firstRow = sheet.getFirstRowNum()
	if lastRow is None:
		lastRow = sheet.getLastRowNum()

	data = []
	for i in range(firstRow , lastRow + 1):
		row = sheet.getRow(i)
		print str(i).zfill(3), list(row)
		if i == firstRow:
			if firstCol is None:
				firstCol = row.getFirstCellNum()

			if lastCol is None:
				lastCol  = row.getLastCellNum()
				lastCol += 1
			if hasHeaders:
				headers = list(row)[firstCol:lastCol]
				headers = ['Col'+str(cNum) for cNum in range(firstCol, lastCol)] 
		rowOut = []
		for j in range(firstCol, lastCol):
			if i == firstRow and hasHeaders:
				cell = row.getCell(j)
				#print "----- :" + str(cell) 
				cellType = cell.getCellType().toString()
				if cellType == 'NUMERIC':
					if DateUtil.isCellDateFormatted(cell):
						value =  cell.dateCellValue
						value = cell.getNumericCellValue()
						if value == int(value):
							value = int(value)
				elif cellType == 'STRING':
					value = cell.getStringCellValue()
					#print "----- :" + str(cellType) 
				elif cellType == 'BOOLEAN':
					value = cell.getBooleanCellValue()
				elif cellType == 'BLANK':
					value = None	
				elif cellType == 'FORMULA':
					if formulatype == 'NUMERIC':
						if DateUtil.isCellDateFormatted(cell):
							value =  cell.dateCellValue
							value = cell.getNumericCellValue()
							if value == int(value):
								value = int(value)
					elif formulatype == 'STRING':
						value = cell.getStringCellValue()
					elif formulatype == 'BOOLEAN':
						value = cell.getBooleanCellValue()
					elif formulatype == 'BLANK':
						value = None
					value = None	
		if len(rowOut) > 0:

	return system.dataset.toDataSet(headers, data)

fName = '"C:\Users\Administrateur\Documents\FichierConfig\\test.xlsx"'
ds1 = excelToDataSet(fName, True)		

On script bove it only work when sheetNum = 0
when I set sheetNum = 1 ou 2 it not work , the raison that why I want convert all excel sheet on .csv file.

Consider iterating through your sheets to pick out a particular named sheet like so:

	for sheet in wb:
		if sheet.sheetName == 'Some Sheet Name':
			# Do something

Thank you @pturmel it work. I can read name of sheet


Need to make same format. Meaning first 4 columns are separated by ';' and last one is 'space'. This also a mistake .put comma for every thing

He's French, and in France we use the comma as decimal separator: 3.14 is 3,14 in France.
That's why we use the semi colon as separator in CSV.
And that's also why I freaking hate working with CSV, because it's always a mess.
I recently had to make a daemon that had to extract data from CSVs to put it in a database, and it was a mix a different formats... some used commas, some used semi colons. Dates were also a mix of different formats, but the CSV is not to blame for that.