Microsoft excel 2010

Ignition
1

Hello !
Please help me on this matter
I want to open and excel file in ignition to show it as a dataset in my table xlsx format/ file and send it to my database SQL SERVER. How can i do it?
It is inapproriate to use CSV file because everytime i have a file with xlsx format i still have to convert it to a csv file.
Thank you

2

You might have to get a Module to handle .xlsx files. Ignition handles CSV files out of the box, but I don’t believe it handles .xlsx files.

So your choices are get a module like http://www.perfectabstractions.com/pa-office-document-module.html and then go through each for of the excel file, inserting it into the database one row at a time with a query you wrote.

Or turn your excel files into .csv files.

1 Like
3

I’ve used the python xlrd library to read data from an xlsx file.
Specifically for Ignition 7.9.11 this version: https://pypi.org/project/xlrd/0.7.9/ works.

We pull data from a spreadsheet and then grab all the data from a specific tab and load it into a client dataset.
I’ve got the code in a project script.

railCarListlocation = 'C:\HCP21\Loadout Railcar List.xls'
logger = system.util.getLogger('ESP')

#Loads rail cars from a xlsx spreadsheet into a dataset
#siloName = tab in spreadsheet
#tagPath = base tag path for the dataset
#The created dataset headers match the headers in the excel file
def loadRailCars(siloName,tagPath):
	import xlrd
	
	logger.info('Reading RailCars for silo: ' + siloName)

	#Clear the current dataset
	railCarData = system.tag.read(tagPath).value
	system.tag.write(tagPath,system.dataset.clearDataset(railCarData))
	
	#read the Excel spreadsheet file
	#wrapped in a try/except
	try:
		wb = xlrd.open_workbook(railCarListlocation)
		sheet = wb.sheet_by_name(siloName)
		header = [str(cell.value) for cell in sheet.row(0)]
		if sheet.nrows > 1:
			data = []
			for rowIdx in range(1,sheet.nrows):
				carData = [str(cell.value) for cell in sheet.row(rowIdx)]
				data.append(carData)
			railCarData = system.dataset.toDataSet(header,data)
			system.tag.write(tagPath,railCarData)
	except:
		logger.error('Error reading data')
		pass

Once you have it in a dataset then you can stream it back to a database as @bkarabinchak.psi mentioned.

2 Likes
4

thanks for helping
i will try it now

5

how can import xlrd file?

6

i shows No module named xlrd

7

At the top of his post, he gave a link to the xlrd library. You’ll need to copy this into the Ignition \user-lib\pylib directory I believe.

8

Capture
Capture925×468 21.8 KB

Should copy all these files?

9

I think copy the whole xlrd-1.2.0 folder in (the 2nd one, you don’t need the folder within the folder). You might also just need the xlrd folder - give them a shot!. I’ve only used an external library once before, and a while back… if you need help, there should be some other forum posts on the topic.

10

Xlrd-1.2.0 will not work, at least in my testing, with Jython you will need the 0.7.9 version in order for it to work properly.

The user manual tells you where to put the files: https://docs.inductiveautomation.com/display/DOC80/Libraries.

You will need to copy the directory that has all the .PY files in it into the location from the usermanual. In this case it is the xlrd directory.

1 Like
11

yup thanks
i have tried using the http://blog.perfectabstractions.com/2017/04/05/using-the-pa-office-document-module/
and it works
now i have to try the xlrd

12

Hi
How can I read and write Excel in perspective?