# Import excel to power table in vision

**URL:** <https://forum.inductiveautomation.com/t/import-excel-to-power-table-in-vision/71590>\
**Category:** Ignition\
**Tags:** ignition81, database, vision, scripting\
**Created:** [March 25, 2023, 1:56pm UTC](https://forum.inductiveautomation.com/t/import-excel-to-power-table-in-vision/71590 "2023-03-25T13:56:42Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Priyanka.Khandge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/priyanka.khandge/32/35989_2.png) [@Priyanka.Khandge](https://forum.inductiveautomation.com/u/Priyanka.Khandge)\
**Post date:** [March 25, 2023, 1:56pm UTC](https://forum.inductiveautomation.com/t/import-excel-to-power-table-in-vision/71590/1 "2023-03-25T13:56:42Z")

</div>

Hi Guys,

Any idea how to import an excel file to the power table in vision?

```auto
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:
				# if lastCol is specified add 1 to it.
				lastCol += 1
			if hasHeaders:
				headers = list(row)[firstCol:lastCol]
			else:
				headers = ['Col'+str(i) for i in range(firstCol, lastCol)] 
		rowOut = []
		for j in range(firstCol, lastCol):
			if i == firstRow and hasHeaders:
				pass
			else:
				cell = row.getCell(j)
				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()
				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)

# Read .xlsx file
fName = 'C:\\IgnitionProjectBackup\\Record.xlsx'
ds1 = excelToDataSet(fName, True)		
util.printDataSet(ds1)
event.source.parent.getComponent('ptbl_Data').data = ds1

```

Found this error:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/4/f4f5d92d2ae1d5d296600c071a476c71b927767b.png)

Ignition - 8.1.17

Thanks,  
Priyanka

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [March 25, 2023, 2:34pm UTC](https://forum.inductiveautomation.com/t/import-excel-to-power-table-in-vision/71590/2 "2023-03-25T14:34:31Z")

</div>

Try this one, it's essentially the same with a few minor changes, specifically to handle empty cells, which appears is what you have based on the error you got.

```auto
def excelToDataSet(fileName, hasHeaders = False, forceString = 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
	from os.path import exists
	
	"""
	Description:
		Function to create a dataset from an Excel spreadsheet. This is typically used in Vision and the file path is
		directly referenced to its location in the filesystem.
		
	Arguments:
		fileName: The path to the Excel spreadsheet. (required)
		hasHeaders: If true, uses the first row of the spreadsheet as column names.
		forceString: If true, forces all cell values to be strings. This can be useful if cell values in columns are not consistent. 
			To create a dataset, the data type is determined by the first row. If the data types are different, you can get errors. 
			If you force all cells to strings, then this is not an issue.
		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
	
	History:
		No. Date Author Comment
		1.0 2021-01-22 Jordan Clark Initial - https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/13
		1.1 2023-02-17 James Landwerlen Updated to handle blank cells
													and to force strings
	"""
	
	if exists(fileName):
		
		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)
			
			if i == firstRow:
				if firstCol is None:
					firstCol = row.getFirstCellNum()
	
				if lastCol is None:
					lastCol = row.getLastCellNum()
				else:
					# if lastCol is specified add 1 to it.
					lastCol += 1
				if hasHeaders:
					headers = list(row)[firstCol:lastCol]
				else:
					headers = ['Col'+str(i) for i in range(firstCol, lastCol)] 
			
			rowOut = []
			for j in range(firstCol, lastCol):
				if i == firstRow and hasHeaders:
					pass
				else:
					cell = row.getCell(j)
					if cell is not None:
						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()
						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	
					else:
						value = None
					if forceString:
						rowOut.append(str(value))
					else:
						rowOut.append(value)
						
			if len(rowOut) > 0:
				data.append(rowOut)
	
		fileStream.close()
	
		return system.dataset.toDataSet(headers, data)

```

---

<div class="post-metadata">

**Author:** ![Priyanka.Khandge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/priyanka.khandge/32/35989_2.png) [@Priyanka.Khandge](https://forum.inductiveautomation.com/u/Priyanka.Khandge)\
**Post date:** [March 25, 2023, 2:40pm UTC](https://forum.inductiveautomation.com/t/import-excel-to-power-table-in-vision/71590/3 "2023-03-25T14:40:15Z")

</div>

@jlandwerlen Thank you so much.

Worked perfectly!!

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [March 25, 2023, 2:41pm UTC](https://forum.inductiveautomation.com/t/import-excel-to-power-table-in-vision/71590/4 "2023-03-25T14:41:08Z")

</div>

You're welcome, more @JordanCClark than me though.

---

<div class="post-metadata">

**Author:** ![Priyanka.Khandge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/priyanka.khandge/32/35989_2.png) [@Priyanka.Khandge](https://forum.inductiveautomation.com/u/Priyanka.Khandge)\
**Post date:** [March 25, 2023, 2:45pm UTC](https://forum.inductiveautomation.com/t/import-excel-to-power-table-in-vision/71590/5 "2023-03-25T14:45:28Z")

</div>

@jlandwerlen I'm facing another issue.

Can you please help?

Please see this post:

> [@Update row in template repeater using button (outside from template) click](https://forum.inductiveautomation.com/t/update-row-in-template-repeater-using-button-outside-from-template-click/71582/11):
>
> @justinedwards.jle sorry for the inconvenience. Only the dropdown value is not updated. remaining all component values updated successfully. I select the dropdown value but do not set the new value in the database. updated old value.