# Copying an Excel file to a table's dataset

**URL:** <https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942>\
**Category:** Ignition\
**Created:** [May 21, 2020, 3:38pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942 "2020-05-21T15:38:18Z")\
**Posts on this page:** 20\
**Page:** 3

<div class="post-metadata">

**Author:** ![simmone.mago](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/simmone.mago/32/36296_2.png) [@simmone.mago](https://forum.inductiveautomation.com/u/simmone.mago)\
**Post date:** [July 5, 2022, 5:36pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/42 "2022-07-05T17:36:49Z")

</div>

Yeah,  
Later I realized that all the examples I’ve found copy the file to another location and then it got me thinking that the code is running from the gateway rather than my computer, so it would be the gateway trying to locate the file, even if I provided the path to it. It would never work. It has to be available in a location that the gateway can reach, right?

Thanks for answering …

---

<div class="post-metadata">

**Author:** ![Andreas\_Klein](https://avatars.discourse-cdn.com/v4/letter/a/fbc32d/32.png) [@Andreas\_Klein](https://forum.inductiveautomation.com/u/Andreas_Klein)\
**Post date:** [July 22, 2022, 12:42pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/43 "2022-07-22T12:42:05Z")

</div>

Dear Jordran,

first of all I like your great work. I am a chemist using Ignition as bloody beginner for data evaluation in our lab.  
I’m using your code to import huge excel-files (13 MB) .  
When I call your method, which is working quite well, my internal RAM is increasing dramatically from 1.3GB to 4.3GB. Normally I thought there is some Java Garbage Collector releasing RAM afterwards but that seams not to be the case. Do you have any idea to overcome this issue?  
Thank you in advance.

Greatings,  
Andi

---

<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:** [July 22, 2022, 2:29pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/44 "2022-07-22T14:29:20Z")

</div>

Hi Andi,

First, welcome!

If you have min/max values for your heap size, you can see changes like that.

Garbage collection is based on a percentage of the heap size. You can see the used memory in the Java VM .  
In the gateway:

- Status → Performance

In the client:

- Help → Diagnostics -or- Ctrl+Shift+F7

After the GC happens, if the free memory ratio is above 70% (I think that’s the default), it should shrink the heap size. @pturmel might know better than I, but I’m not sure I’ve ever seen the heap shrink.

---

<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:** [July 22, 2022, 2:47pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/45 "2022-07-22T14:47:55Z")

</div>

The heap itself doesn’t shrink. Maybe there’s some condition that will trigger it, but I’ve never seen it. When the JVM wants to allocate memory from the heap, and it’s near full, and not yet at the maximum allowed, it will claim more memory from the OS, and never give it back. Once at that max, allocations when near full will trigger **aggressive** GC.

Because a gateway is long-lived, it should be configured to claim its entire allowed memory at startup (min == max) to avoid starvation when competing with other services and the OS’s own disk cache.

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [April 24, 2023, 6:15am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/46 "2023-04-24T06:15:23Z")

</div>

Love this function, thanks for sharing!

Anyone else having issues where the workbook seems to be marked as read-only after running the function though??

**EDIT 1** : Fixed the read-only thing by adding the following just before the fileStream.close() section:

```auto
		wb.close()	## 24/04/2023 - rlee - added, otherwise seems that wb is in readonly mode.
		fileStream.close()

```

Also added those two lines in the **except** clause of a try/except that covers most of the heavy duty logic to ensure things close nicely when it goes wrong.

**EDIT 2:** Encountered a bug when checking for cell type. Apparently a cell can be None if the cell is blank? Not really sure why it isn't handled nicely, as i see there is something to deal with cellType == 'BLANK' later..

Added the following change to get it to work nicely:

```auto
if i == firstRow and hasHeaders:
						pass
					else:
						cell = row.getCell(j)
						if cell == None:	# 24/04/2023 - rlee - added.. not sure why cell is returning None... 
							value = None	# 24/04/2023 - rlee - added.. not sure why cell is returning None... 
						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	
                            ##...... ETC.....

```

**EDIT 3:** read-only issue seems to NOT be resolved still.. reading up about java iostream module and appache aoi functions doesn't seem to indicate why this may be.. Everyone says that closing the filestream should do the trick,..

---

<div class="post-metadata">

**Author:** ![rberghammer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rberghammer/32/7150_2.png) [@rberghammer](https://forum.inductiveautomation.com/u/rberghammer)\
**Post date:** [May 1, 2023, 6:11pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/47 "2023-05-01T18:11:28Z")

</div>

@JordanCClark, I also want to thank you for sharing this!

For future visitors to this thread, I think I found a bug in this line:

```auto
headers = ['Col'+str(i) for i in range(firstCol, lastCol)]

```

The variable "i" should be changed to something else. For example:

```auto
headers = ['Col'+str(x) for x in range(firstCol, lastCol)]

```

"i" is used later in the code to check if the row is a header, but the previous for loop changes the value. I don't think this causes any big issues, but I noticed it when printing the value of "i" while testing.

---

<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:** [May 1, 2023, 6:23pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/48 "2023-05-01T18:23:03Z")

</div>

Good catch!. Updated.

---

<div class="post-metadata">

**Author:** ![tupanaster](https://avatars.discourse-cdn.com/v4/letter/t/5daacb/32.png) [@tupanaster](https://forum.inductiveautomation.com/u/tupanaster)\
**Post date:** [May 4, 2023, 2:08pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/49 "2023-05-04T14:08:40Z")

</div>

I was testing this approach today. I was able to write values to some cells but when I wanted to do something else it "bugs out". I think the reason for it is that it doesn't close the file afterwards. If you try to delete or make a change (an save) from Windows/Excel, it says that Ignition is using the file.

Also, I was unable to get calculated values from Excel formulas. Maybe newer versions of openpyxl can solve those issues.

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [May 5, 2023, 3:55am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/50 "2023-05-05T03:55:00Z")

</div>

> [@tupanaster](#):
>
> If you try to delete or make a change (an save) from Windows/Excel, it says that Ignition is using the file.

Yea i still couldn't get that part to work.. I somehow got it going where it wouldn't hold it as read only, as i said in an earlier edit, but it didn't seem to stick after more testing.. ☹

> [@richardNZ15](#):
>
> **EDIT 3:** read-only issue seems to NOT be resolved still.. reading up about java iostream module and appache aoi functions doesn't seem to indicate why this may be.. Everyone says that closing the filestream should do the trick,..

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [May 5, 2023, 4:03am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/51 "2023-05-05T04:03:17Z")

</div>

One approach to solving this problem could be to create an independent instance of the file by copying it into some temporary directory, perform the file stream from there, and then, simply deleted it once the dataset import is complete. This could all be done via scripting. I sometimes use the ignition cache directory for temporary files.

---

<div class="post-metadata">

**Author:** ![tupanaster](https://avatars.discourse-cdn.com/v4/letter/t/5daacb/32.png) [@tupanaster](https://forum.inductiveautomation.com/u/tupanaster)\
**Post date:** [May 5, 2023, 8:23am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/52 "2023-05-05T08:23:15Z")

</div>

Maybe it's possible to do it that way but I dropped openpyxl 2.6.4 because I wasn't able to get Excel formulas evaluated.

So I started testing Apache POI libraries. I thought I had to buy a module for it but I saw this post:

> [@Importing xlrd library for reading Excel files](https://forum.inductiveautomation.com/t/importing-xlrd-library-for-reading-excel-files/53332/3):
>
> Consider not using xlrd at all. Ignition 8.1 includes the Apache POI libraries, which are native java tools for MS office documents. This topic covers the basics:

I'm using Ignition 8.1 so if it's already there and I don't need to get a new module for it (although it's not expensive), then I better use it. Plus, I should be able to evaluate Excel formulas based on my inputs. So' I'm trying that out today.

So far it has been easier to play around with Apache POI libraries than with openpyxl 2.6.4. @JordanCClark makes it some much easier with the functions he shared.

---

<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:** [May 6, 2023, 5:09am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/53 "2023-05-06T05:09:36Z")

</div>

What about the function from Paul's ignition extensions module?

> [@Ignition Extensions - Convenience utilities for advanced users](https://forum.inductiveautomation.com/t/ignition-extensions-convenience-utilities-for-advanced-users/64615):
>
> Introducing a hopefully community-engaged effort to build a useful repository of scripting and other Ignition utilities for everyone to benefit from. Download the latest release from the [Releases](https://github.com/IgnitionModuleDevelopmentCommunity/ignition-extensions/releases) page and take advantage of the following functions: Scripting Functions system.util system.util.getContext(): Context A convenience function to retrieve the local "context" object, useful as an entrypoint to other advanced scripting. system.util.deepCopy(object): Object Converts a given Pyth…

---

<div class="post-metadata">

**Author:** ![tupanaster](https://avatars.discourse-cdn.com/v4/letter/t/5daacb/32.png) [@tupanaster](https://forum.inductiveautomation.com/u/tupanaster)\
**Post date:** [May 9, 2023, 8:22am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/54 "2023-05-09T08:22:38Z")

</div>

> [@nminchin](#):
>
> What about the function from Paul's ignition extensions module?

I remembered reading somewhere that Paul didn't finish his module

> [@Copying a Excel file to a tables Dataset](https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/12):
>
> For another option re: reading Excel files, I started and never finished an Ignition module that brings in the [fastexcel](https://github.com/dhatim/fastexcel) library: [https://github.com/paul-griffith/kotlin-module](https://github.com/paul-griffith/kotlin-module)

But now I can see the date differences between posts 😆

It would be a good idea to test it because Apache POI has a small issue; I couldn't recognize CONCAT as CONCATENATE. Not a deal breaker but just something to look out for.

@JordanCClark  
Is there an idea behind making all numbers int? Although many things can be integers, I would argue that most numbers are floats/doubles.

```auto
if value == int(value):
    value = int(value)

```

---

<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:** [May 9, 2023, 8:30am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/55 "2023-05-09T08:30:08Z")

</div>

Well I can confirm that Paul's Extensions module _does_ use `org.apache.poi` as the library. It looks like you might just need to update to the latest library  
[https://poi.apache.org/apidocs/dev/org/apache/poi/ss/formula/functions/TextFunction.html#CONCATENATE](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/formula/functions/TextFunction.html#CONCATENATE)

---

<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:** [May 9, 2023, 8:38am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/56 "2023-05-09T08:38:31Z")

</div>

It's only a test for integer. It's also only a proof of concept, although an admittedly really good one. 😏

> [@JordanCClark](#):
>
> Got my paperwork done early, so _ **here's a sample** _ to read from an Excel file

Feel free to modify it to your needs.

---

<div class="post-metadata">

**Author:** ![tupanaster](https://avatars.discourse-cdn.com/v4/letter/t/5daacb/32.png) [@tupanaster](https://forum.inductiveautomation.com/u/tupanaster)\
**Post date:** [May 9, 2023, 8:50am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/57 "2023-05-09T08:50:00Z")

</div>

I'm using the built-in libraries in Ignition 8.1.  
If there is a way to update just Apache POI I'll skip it for now to leave everything as default.

---

<div class="post-metadata">

**Author:** ![Mihir\_Bhandarkar](https://avatars.discourse-cdn.com/v4/letter/m/6a8cbe/32.png) [@Mihir\_Bhandarkar](https://forum.inductiveautomation.com/u/Mihir_Bhandarkar)\
**Post date:** [June 9, 2023, 6:19pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/58 "2023-06-09T18:19:19Z")

</div>

Hey, you might have already answered this but does this work in perspective without installing any other libraries.

---

<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:** [June 9, 2023, 8:30pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/59 "2023-06-09T20:30:05Z")

</div>

From earlier in this thread.My response is in the next post

> [@Copying a Excel file to a tables Dataset](https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/17):
>
> Hi Jordan Thanks for the code. Could you please update the code so it get file bytes instead of file path. I need this because in perspective we only can upload file to server and deal with file stream.

---

<div class="post-metadata">

**Author:** ![Keyur\_Parikh](https://avatars.discourse-cdn.com/v4/letter/k/c2a13f/32.png) [@Keyur\_Parikh](https://forum.inductiveautomation.com/u/Keyur_Parikh)\
**Post date:** [July 5, 2023, 7:52pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/60 "2023-07-05T19:52:06Z")

</div>

I am seeing the following error running this script on Vision. I have installed the POI JAR files in Ignition Common, Client, Gateway and Designer Folders.  
Ignition version: 7.9.21

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

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

---

<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:** [July 5, 2023, 11:00pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/62 "2023-07-05T23:00:20Z")

</div>

For 7.9 I used xlrd.

> [@Copying an Excel file to a table's dataset](https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/5):
>
> xlrd works with Jython 2.7. Unzip this into the user-lib/pylib/site-packages folder. [xlrd.zip](https://forum.inductiveautomation.com/uploads/short-url/rZTrGR58x9kIfTHEBhHHkzyJsLw.zip) (283.0 KB) [Documentation](https://xlrd.readthedocs.io/en/latest/)

[Previous page](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942.md?page=2)

[Next page](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942.md?page=4)