Copying a Excel file to a tables Dataset

Okay, now paste it into a dataset. From perspective.

1 Like

Are you[quote=“OscarLucas, post:25, topic:34942, full:true”]
The most easy way!

[/quote]

Are you going to give an operator access to SQL?? Not the best idea. Plus this post was about Excel to an Ignition table, not necessarily SQL table.

@OscarLucas file->save as csv and we can avoid the rest of the adventure through GUI Land

CREATE TABLE Test (
     ID int
    , Data_Description varchar(50)
)
BULK INSERT Test
FROM '/path/to/file.csv'
WITH
(
    FIELDTERMINATOR = ','
    , ROWTERMINATOR = '\n'
)

Then to stay on topic system.db.runQuery('SELECT * FROM Test') gives us the dataset

Are you going to give an operator access to SQL?? Not the best idea. Plus this post was about Excel to an Ignition table, not necessarily SQL table.

tbf @jshaffer09 asked for “easy”, no need to overcomplicate things :grinning:

Hi Jordan
For cell with formula I try manipulate your code to get the result of the formula.

					if cell != 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':
							value = cell.getNumericCellValue()
					else:
						value = None

but the problem is I don’t know what is the return type of formula is. So the .getNumericCellValue() is not always work if formula return string. Do you know how can I get the type of return value?

getCachedFormulaResultType() is what you’re looking for.

Script updated.

1 Like

For project with every limited budget instead of using report module we can create report templates in excel and save them in server hard drive. When user ask for report we can just simply open the workbook and put the result of query into it and save it as bytes and send the file to client.

I talk to my account exec and buy just 1 report. Then I bolt on more as needed. Once 5 are reached, the report count goes unlimited.

1 Like

How much does it cost for 1 report?

$700 off top of my head, full module is $3.5K again IIRC

For ones like Jordan that has solid coding knowledge, the amount of engineering for both method is same. So it is better to save some money here. Specially when you want to compete to Chinese HMI panel.

I was getting the error, “TypeError: Unable to convert row x, column y to type class java.lang.Integer” on the line “return system.dataset.toDataSet(headers, data)”, which was traced to

value = cell.getNumericCellValue()
if value == int(value):
value = int(value)

The value it was referring to was a single float within a column of mostly integers

I fixed this error following the logic in "Cannot convert row x column y to type java.lang.Long" by adding

try:
value = float(value)
except:
pass

just before rowOut.append(value). I will update this if I run into problems downstream due to this change.

In my project I’m able to use openpyxl for working with excel files. I’m not sure if one of the previous project devs had to do anything weird to set it up but it’s really easy to use.
Ignition v7.9.18
openpyxl v1.7.0 (It’s old, but it works)

I just tried the openpyxl library and it works in Ignition 8.1.
Version 2.6.4 is the last one to support Python 2.7.
If anyone wants to try…
openpyxl-2.6.4.zip (1.1 MB)

Just copy everything in the .zip file into the C:\Program Files\Inductive Automation\Ignition\user-lib\pylib folder under Windows.

https://openpyxl.readthedocs.io/en/stable/

1 Like

Heh i had tried openpyxl too a while ago, but i was missing a file of jdcal, when i check your zip tho it seems its in there :o
Maybe i pulled it from git tho instead of that site and forgot to copy that file

Edit: jup seems i pulled it from git

Thank you for this very useful snippet of code.
Now, I am using the fileUploader component in Perspective, but it seem to return just the filename, rather than the full path when I use event.file.name to get said path on the OnFileReceived event.

Is it something that I am doing wrong?
Without the full path, I cannot use your code snippet.

Thanks in advance,

The full path is deliberately obscured because it’s an implementation detail and subject to change if necessary. You need to either read from the byte array directly or copy the file to a known location.

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 …

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

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.

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.