# File upload component / excel read

**URL:** <https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386>\
**Category:** General Discussion\
**Tags:** perspective, ignition81\
**Created:** [January 17, 2024, 2:54pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386 "2024-01-17T14:54:43Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [January 17, 2024, 2:54pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/1 "2024-01-17T14:54:43Z")

</div>

8.1.33

Trying to use File upload component to upload a excel file. Then I am trying to test out excel dataset read (I might when done put the read script inside the file upload component but currently just testing with script console while I figure out how I want to do script functions.)

So I have file upload that is saving file on gateway.  
confirmed file is actually on the gateway

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

then in script console running this code that I found in forum

```auto
from org.apache.poi.ss.usermodel import WorkbookFactory as WorkbookFactory, DateUtil as DateUtil, Row, CellType
from java.io import FileInputStream
from java.util import Date

def excelToDataSet(fileName, hasHeaders = False, sheetNum = 0, firstRow = None, lastRow = None, firstCol = None, lastCol = None):
	fileStream = FileInputStream(fileName)

	wb = WorkbookFactory.create(fileStream)
	
	sheet = wb.getSheetAt(sheetNum)

	if firstRow is None:
		firstRow = sheet.firstRowNum
	if lastRow is None:
		lastRow = sheet.lastRowNum

	data = []
	for i in range(firstRow , lastRow + 1):
		row = sheet.getRow(i)
		if i == firstRow:
			if firstCol is None:
				firstCol = row.firstCellNum

			if lastCol is None:
				lastCol = row.lastCellNum
			else:
				# if lastCol is specified add 1 to it.
				lastCol += 1
			if hasHeaders:
				headers = list(row)[firstCol:lastCol]
			else:
				headers = ['Col'+str(cNum) for cNum in range(firstCol, lastCol)] 
		rowOut = []
		for j in range(firstCol, lastCol):
			if not (i == firstRow and hasHeaders):
				cell = row.getCell(j,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
				
				cellTypes = (cell.cellType,cell.cachedFormulaResultType if cell.cellType == CellType.FORMULA else None)				
				if CellType.NUMERIC in cellTypes:
					if DateUtil.isCellDateFormatted(cell):
						value = cell.dateCellValue
					else:
						value = cell.numericCellValue
						if value == int(value):
							value = int(value)
				elif CellType.STRING in cellTypes:
					value = cell.stringCellValue
				elif CellType.BOOLEAN in cellTypes:
					value = cell.booleanCellValue
				elif CellType.BLANK in cellTypes:
					value = None
				else:
					value = None
				rowOut.append(value)
		if len(rowOut) > 0:
			data.append(rowOut)

	fileStream.close()
	
	return system.dataset.toDataSet(headers, data)
	
dataset= excelToDataSet('<U+202A>C:\IgnitionUploads\Loadsheet1.xlsx', hasHeaders = False, sheetNum = 5, firstRow = 3, lastRow = 5, firstCol = 1, lastCol = 4)

```

But getting this response

```auto
Java Traceback:
Traceback (most recent call last):
  File "<input>", line 62, in <module>
  File "<input>", line 6, in excelToDataSet
	at java.base/java.io.FileInputStream.open0(Native Method)

	at java.base/java.io.FileInputStream.open(Unknown Source)

	at java.base/java.io.FileInputStream.<init>(Unknown Source)

	at java.base/java.io.FileInputStream.<init>(Unknown Source)

	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Unknown Source)

	at java.base/java.lang.reflect.Constructor.newInstance(Unknown Source)

	at org.python.core.PyReflectedConstructor.constructProxy(PyReflectedConstructor.java:213)

java.io.FileNotFoundException: java.io.FileNotFoundException: ‪C:\IgnitionUploads\Loadsheet1.xlsx (The filename, directory name, or volume label syntax is incorrect)

```

any ideas why it is failing to get the file?

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [January 17, 2024, 3:08pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/2 "2024-01-17T15:08:59Z")

</div>

> [@rfolkes](#):
>
> any ideas why it is failing to get the file?

Do you not see the goofy character you have at the beginning of your file path?

> [@rfolkes](#):
>
> `FileNotFoundException: ‪C:\IgnitionUploads\Loadsheet1.xlsx`

---

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [January 17, 2024, 3:14pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/3 "2024-01-17T15:14:50Z")

</div>

I don't know where that is coming from

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

---

<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 17, 2024, 3:31pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/4 "2024-01-17T15:31:38Z")

</div>

In a file upload, don't use a file stream. Just wrap the event's file bytes in a ByteArrayInputStream, and feed that to POI.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 17, 2024, 3:54pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/5 "2024-01-17T15:54:41Z")

</div>

Look a few posts further down from where you found my original script.

> [@Copying an Excel file to a table's dataset](https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/18):
>
> Untested, but you should just be able to use ByteArrayInputStream instead. def excelBytesToDataSet(bytesIn, hasHeaders = False): import org.apache.poi.ss.usermodel.WorkbookFactory as WorkbookFactory import org.apache.poi.ss.usermodel.DateUtil as DateUtil from java.io import ByteArrayInputStream fileStream = ByteArrayInputStream(bytesIn) ...

---

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [January 17, 2024, 4:25pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/6 "2024-01-17T16:25:07Z")

</div>

ok Here is my script in onFileReceived of file upload

```auto
def runAction(self, event):
	event.file.copyTo('c:\IgnitionUploads\Loadsheet1.xlsx')
	from org.apache.poi.ss.usermodel import WorkbookFactory as WorkbookFactory, DateUtil as DateUtil, Row, CellType
	from java.io import ByteArrayInputStream
	from java.util import Date
	
	hasHeaders = False
	sheetNum = 0
	firstRow = 3
	lastRow = 5
	firstCol = 1
	lastCol = 3
	
		
	fileStream = ByteArrayInputStream(event.file.getBytes())

	wb = WorkbookFactory.create(fileStream)
	
	sheet = wb.getSheetAt(sheetNum)

	if firstRow is None:
		firstRow = sheet.firstRowNum
	if lastRow is None:
		lastRow = sheet.lastRowNum

	data = []
	for i in range(firstRow , lastRow + 1):
		row = sheet.getRow(i)
		if i == firstRow:
			if firstCol is None:
				firstCol = row.firstCellNum

			if lastCol is None:
				lastCol = row.lastCellNum
			else:
				# if lastCol is specified add 1 to it.
				lastCol += 1
			if hasHeaders:
				headers = list(row)[firstCol:lastCol]
			else:
				headers = ['Col'+str(cNum) for cNum in range(firstCol, lastCol)] 
		rowOut = []
		for j in range(firstCol, lastCol):
			if not (i == firstRow and hasHeaders):
				cell = row.getCell(j,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
				
				cellTypes = (cell.cellType,cell.cachedFormulaResultType if cell.cellType == CellType.FORMULA else None)				
				if CellType.NUMERIC in cellTypes:
					if DateUtil.isCellDateFormatted(cell):
						value = cell.dateCellValue
					else:
						value = cell.numericCellValue
						if value == int(value):
							value = int(value)
				elif CellType.STRING in cellTypes:
					value = cell.stringCellValue
				elif CellType.BOOLEAN in cellTypes:
					value = cell.booleanCellValue
				elif CellType.BLANK in cellTypes:
					value = None
				else:
					value = None
				rowOut.append(value)
		if len(rowOut) > 0:
			data.append(rowOut)

	fileStream.close()
	
	self.view.getChild("root").custom.ds1 = system.dataset.toDataSet(headers, data)

```

Here is the sheet I am uploading  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/8/88a2acff451f90951e221234769568a09bd7abbc.png)

results in

```auto
com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
  File "<function:runAction>", line 46, in runAction
AttributeError: 'NoneType' object has no attribute 'getCell'

	caused by org.python.core.PyException
Traceback (most recent call last):
  File "<function:runAction>", line 46, in runAction
AttributeError: 'NoneType' object has no attribute 'getCell'

Ignition v8.1.33 (b2023101913)
Java: Azul Systems, Inc. 17.0.8

```

line 46 is

```auto
				cell = row.getCell(j,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)

```

so looks like I am getting None data into row

---

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [January 17, 2024, 4:49pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/7 "2024-01-17T16:49:27Z")

</div>

ok fixed that by changing my references to base 0

```auto
||hasHeaders = False|
|---|---|
||sheetNum = 0|
||firstRow = 2|
||lastRow = 4|
||firstCol = 0|
||lastCol = 3|

```

---

<div class="post-metadata">

**Author:** ![Tamilvanan\_S](https://avatars.discourse-cdn.com/v4/letter/t/ecae2f/32.png) [@Tamilvanan\_S](https://forum.inductiveautomation.com/u/Tamilvanan_S)\
**Post date:** [January 31, 2024, 4:33pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/8 "2024-01-31T16:33:10Z")

</div>

Hi, I Have one issue. I want read multiple sheets. any solution?  
Thanks in Advance.

---

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [January 31, 2024, 5:18pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/9 "2024-01-31T17:18:52Z")

</div>

take the first part of the "runAction" function above up to sheet=wb.getSheetAt(sheetNum)

make the rest of it its own function and call it for each sheet you want to parse data from. You will get a new dataset from each call. so you can either combine them if it is all the same data or figure out where to store the datasets created.

---

<div class="post-metadata">

**Author:** ![Tamilvanan\_S](https://avatars.discourse-cdn.com/v4/letter/t/ecae2f/32.png) [@Tamilvanan\_S](https://forum.inductiveautomation.com/u/Tamilvanan_S)\
**Post date:** [February 1, 2024, 6:56am UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/10 "2024-02-01T06:56:58Z")

</div>

Thank you for the inputs.  
I want to Read last sheet number...  
Thanks in Advance.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [February 1, 2024, 12:29pm UTC](https://forum.inductiveautomation.com/t/file-upload-component-excel-read/84386/11 "2024-02-01T12:29:10Z")

</div>

> [@Tamilvanan\_S](#):
>
> I want to Read last sheet number...

```python
numSheets = wb.getNumberOfSheets()
sheet = wb.getSheetAt(numSheets-1) 

```

[Apache POI Workbook docs](https://poi.apache.org/apidocs/4.1/org/apache/poi/ss/usermodel/Workbook.html). I suggest some reading is in order.