# Import excel data into table

**URL:** <https://forum.inductiveautomation.com/t/import-excel-data-into-table/70225>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [February 23, 2023, 2:52pm UTC](https://forum.inductiveautomation.com/t/import-excel-data-into-table/70225 "2023-02-23T14:52:32Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![aman\_sharma](https://avatars.discourse-cdn.com/v4/letter/a/e480ec/32.png) [@aman\_sharma](https://forum.inductiveautomation.com/u/aman_sharma)\
**Post date:** [February 23, 2023, 2:52pm UTC](https://forum.inductiveautomation.com/t/import-excel-data-into-table/70225/1 "2023-02-23T14:52:32Z")

</div>

i want to import my excel file into perspective ignition.  
i am using this script:

```
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 = '<U+202A><U+202A>D:/SaleData.xlsx'
ds1 = excelToDataSet(fName, True)		
util.printDataSet(ds1)
self.getSibling("Table").props.data = ds1

```

but i got an error:  
File "function:runAction", line 96, in runAction  
File "function:runAction", line 21, in excelToDataSet  
java.io.FileNotFoundException: java.io.FileNotFoundException: ‪‪D:\SaleData.xlsx (The filename, directory name, or volume label syntax is incorrect)

please guide me

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 23, 2023, 3:17pm UTC](https://forum.inductiveautomation.com/t/import-excel-data-into-table/70225/2 "2023-02-23T15:17:42Z")

</div>

You marked this as Perspective. Perspective cannot directly access files on the client machine. You must use a File Upload Component.

---

<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:** [February 23, 2023, 3:20pm UTC](https://forum.inductiveautomation.com/t/import-excel-data-into-table/70225/3 "2023-02-23T15:20:15Z")

</div>

Look closer in the thread you took the code from, it discusses `ByteArrayInputStream `

> [@Copying an Excel file to a table's dataset](https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/16):
>
> Here you go. slight_smile