# File Not Found using system.file.openFile

**URL:** <https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939>\
**Category:** Ignition\
**Tags:** vision\
**Created:** [January 15, 2025, 6:42pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939 "2025-01-15T18:42:17Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jason\_Platt](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@Jason\_Platt](https://forum.inductiveautomation.com/u/Jason_Platt)\
**Post date:** [January 15, 2025, 6:42pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/1 "2025-01-15T18:42:17Z")

</div>

Got a weird one. When I try to run this script to convert an excel file to dataset, I get this error.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/b/fb668ed85b447ae6c2f5eb7b84735418d75dae81.png)  
however, if I hardcode that exact path it works just fine.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/7/e79afd624f6fcb240399a58100367915161e7f4e.png)

and if I do this in the script counsel it works as intended

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/b/9b7afbbc8d39e7f79d37a97beb28f9506e81c9b8.png)

this one has me stumped

---

<div class="post-metadata">

**Author:** ![Jason\_Platt](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@Jason\_Platt](https://forum.inductiveautomation.com/u/Jason_Platt)\
**Post date:** [January 15, 2025, 6:42pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/2 "2025-01-15T18:42:45Z")

</div>

```python
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
path = system.file.openFile("xlxs")
fName = "'"+path+"'"

#fName = 'C:\Users\jplatt\Documents\data1.xlsx'
ds1 = excelToDataSet(fName, True)
event.source.parent.getComponent('Table 1').data = ds1

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [January 15, 2025, 7:01pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/3 "2025-01-15T19:01:16Z")

</div>

Is this Vision or Perspective? (Add the tag below your question title.)

Be aware that Perspective scripts run on the gateway whereas the Script Console runs on the Designer client PC so the C: drive will be different in each case (unless they're the same machine).

Your error screengrab says the FileNotFoundException occurred on line 99 but your code screengrab line 99 doesn't reference the filename. Did you edit the code between screengrabs? If so, please correct your post.

---

<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:** [January 15, 2025, 7:02pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/4 "2025-01-15T19:02:25Z")

</div>

Why are you adding single quotes around the path to build the filename? What you get in `path` is already a string.

---

<div class="post-metadata">

**Author:** ![Jason\_Platt](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@Jason\_Platt](https://forum.inductiveautomation.com/u/Jason_Platt)\
**Post date:** [January 15, 2025, 7:07pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/5 "2025-01-15T19:07:16Z")

</div>

code is update

It vision. Makes sense on the script console being on the local. So even though I can navigate to the file in the when I use the system.file.openFile the client might not be able to retrieve it?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [January 15, 2025, 7:11pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/6 "2025-01-15T19:11:39Z")

</div>

Does this work?

```python
path = system.file.openFile("xlxs")
fName = path.replace(r'\', '/')

```

---

<div class="post-metadata">

**Author:** ![Jason\_Platt](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@Jason\_Platt](https://forum.inductiveautomation.com/u/Jason_Platt)\
**Post date:** [January 15, 2025, 7:14pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/7 "2025-01-15T19:14:13Z")

</div>

> [@paul-griffith](#):
>
> ```python
> path = system.file.openFile("xlxs")
> fName = path.replace(r'\', '/')
> 
> ```

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/3/63108643568729b2dede9a47ca43e3e68d1a7ea4.png)

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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [January 15, 2025, 7:22pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/8 "2025-01-15T19:22:31Z")

</div>

Hmm, the quotes being funky suggest something's off with the escaping.

Maybe just:

```python
path = system.file.openFile("xlxs")
fName = path.replace('\\', '/')

```

---

<div class="post-metadata">

**Author:** ![Jason\_Platt](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@Jason\_Platt](https://forum.inductiveautomation.com/u/Jason_Platt)\
**Post date:** [January 15, 2025, 7:41pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/9 "2025-01-15T19:41:25Z")

</div>

> [@paul-griffith](#):
>
> ```python
> path = system.file.openFile("xlxs")
> fName = path.replace('\\', '/')
> 
> ```

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

---

<div class="post-metadata">

**Author:** ![Jason\_Platt](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@Jason\_Platt](https://forum.inductiveautomation.com/u/Jason_Platt)\
**Post date:** [January 15, 2025, 7:48pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/10 "2025-01-15T19:48:31Z")

</div>

> [@paul-griffith](#):
>
> ```python
> path = system.file.openFile("xlxs")
> fName = path.replace('\\', '/')
> 
> ```

this was an issue with the excel file

---

<div class="post-metadata">

**Author:** ![Jason\_Platt](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@Jason\_Platt](https://forum.inductiveautomation.com/u/Jason_Platt)\
**Post date:** [January 15, 2025, 7:53pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/11 "2025-01-15T19:53:55Z")

</div>

all in all Paul's idea worked. Issue was the datatypes were not compatible. If I change all the rows/columns to Text then it works. Onto the next issue, this excel file is also an export from Ignition. So ill need to figure out how to format each column on the export script. Any tips are greatly appreciated

---

<div class="post-metadata">

**Author:** ![S\_Qaiser](https://avatars.discourse-cdn.com/v4/letter/s/b9e5f3/32.png) [@S\_Qaiser](https://forum.inductiveautomation.com/u/S_Qaiser)\
**Post date:** [January 15, 2025, 8:15pm UTC](https://forum.inductiveautomation.com/t/file-not-found-using-system-file-openfile/100939/12 "2025-01-15T20:15:00Z")

</div>

try this code to avoid none datatype error

```python
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)
				
				if cell is None:
					value = ''
				else:
					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
path = system.file.openFile("xlsx")
fName = path.replace('\\', '/')

#fName = 'C:\Users\jplatt\Documents\data1.xlsx'
ds1 = excelToDataSet(fName, True)
print ds1

```