# Microsoft excel 2010

**URL:** <https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259>\
**Category:** Ignition\
**Created:** [February 14, 2020, 7:56am UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259 "2020-02-14T07:56:51Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![epifanio.tigno](https://avatars.discourse-cdn.com/v4/letter/e/57b2e6/32.png) [@epifanio.tigno](https://forum.inductiveautomation.com/u/epifanio.tigno)\
**Post date:** [February 14, 2020, 7:56am UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/1 "2020-02-14T07:56:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [February 14, 2020, 1:23pm UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/2 "2020-02-14T13:23:15Z")

</div>

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](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.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [February 14, 2020, 1:30pm UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/3 "2020-02-14T13:30:23Z")

</div>

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/](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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![epifanio.tigno](https://avatars.discourse-cdn.com/v4/letter/e/57b2e6/32.png) [@epifanio.tigno](https://forum.inductiveautomation.com/u/epifanio.tigno)\
**Post date:** [February 17, 2020, 1:12am UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/4 "2020-02-17T01:12:02Z")

</div>

thanks for helping  
i will try it now

---

<div class="post-metadata">

**Author:** ![epifanio.tigno](https://avatars.discourse-cdn.com/v4/letter/e/57b2e6/32.png) [@epifanio.tigno](https://forum.inductiveautomation.com/u/epifanio.tigno)\
**Post date:** [February 17, 2020, 1:37am UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/5 "2020-02-17T01:37:06Z")

</div>

how can import xlrd file?

---

<div class="post-metadata">

**Author:** ![epifanio.tigno](https://avatars.discourse-cdn.com/v4/letter/e/57b2e6/32.png) [@epifanio.tigno](https://forum.inductiveautomation.com/u/epifanio.tigno)\
**Post date:** [February 17, 2020, 8:00am UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/6 "2020-02-17T08:00:46Z")

</div>

i shows No module named xlrd

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [February 17, 2020, 8:05am UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/7 "2020-02-17T08:05:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![epifanio.tigno](https://avatars.discourse-cdn.com/v4/letter/e/57b2e6/32.png) [@epifanio.tigno](https://forum.inductiveautomation.com/u/epifanio.tigno)\
**Post date:** [February 17, 2020, 8:14am UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/8 "2020-02-17T08:14:07Z")

</div>

![Capture](https://us1.discourse-cdn.com/inductiveautomation/original/2X/1/1dbb339a4f6efa5cc0cbaebb6fbf382aebfa3186.png)  
Should copy all these files?

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [February 17, 2020, 8:15am UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/9 "2020-02-17T08:15:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [February 17, 2020, 1:27pm UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/10 "2020-02-17T13:27:31Z")

</div>

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](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.

---

<div class="post-metadata">

**Author:** ![epifanio.tigno](https://avatars.discourse-cdn.com/v4/letter/e/57b2e6/32.png) [@epifanio.tigno](https://forum.inductiveautomation.com/u/epifanio.tigno)\
**Post date:** [February 18, 2020, 1:01am UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/11 "2020-02-18T01:01:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nader.chinichian](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nader.chinichian/32/9261_2.png) [@nader.chinichian](https://forum.inductiveautomation.com/u/nader.chinichian)\
**Post date:** [March 7, 2020, 8:57am UTC](https://forum.inductiveautomation.com/t/microsoft-excel-2010/32259/12 "2020-03-07T08:57:01Z")

</div>

Hi  
How can I read and write Excel in perspective?