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 = csvData.next()
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 ,
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
thisFile.seek(0) #back to line 0
csvData = csv.reader(thisFile,delimiter=thisDelimiter)
header = csvData.next()
dataset = system.dataset.toDataSet(header ,list(csvData))
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_CsvData.next()
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 org.apache.poi.ss.usermodel.WorkbookFactory as WorkbookFactory
import org.apache.poi.ss.usermodel.DateUtil as DateUtil
from java.io 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:
params:
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()
else:
lastCol += 1
if hasHeaders:
headers = list(row)[firstCol:lastCol]
else:
headers = ['Col'+str(cNum) for cNum in range(firstCol, lastCol)]
rowOut = []
for j in range(firstCol, lastCol):
if i == firstRow and hasHeaders:
pass
else:
cell = row.getCell(j)
#print "----- :" + str(cell)
cellType = cell.getCellType().toString()
if cellType == 'NUMERIC':
if DateUtil.isCellDateFormatted(cell):
value = cell.dateCellValue
else:
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':
formulatype=str(cell.getCachedFormulaResultType())
if formulatype == 'NUMERIC':
if DateUtil.isCellDateFormatted(cell):
value = cell.dateCellValue
else:
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
else:
value = None
rowOut.append(value)
if len(rowOut) > 0:
data.append(rowOut)
fileStream.close()
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
Hi,
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.