# Copying an Excel file to a table's dataset **URL:** \ **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:** 2
**Author:** ![nader.chinichian](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nader.chinichian/32/9261_2.png) [@nader.chinichian](https://forum.inductiveautomation.com/u/nader.chinichian)\ **Post date:** [January 26, 2021, 7:11am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/21 "2021-01-26T07:11:56Z")
I successfully test the code in perspective without any error. So it is only for vision. ---
**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 26, 2021, 10:13am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/23 "2021-01-26T10:13:40Z")
Huh. I get the same thing. Looks like we can’t serialize the dataset. I’ll take a closer look when I get some time to do so. ---
**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 26, 2021, 10:27am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/24 "2021-01-26T10:27:12Z")
Function updated above, hopefully casting values as something other than a cell. ---
**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\ **Post date:** [January 26, 2021, 11:01am UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/25 "2021-01-26T11:01:02Z")
The most easy way! ![](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/c/bcd6f16422d9f6f146f5928dc715111a8bf740a0.gif) ---
**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 26, 2021, 12:38pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/26 "2021-01-26T12:38:52Z")
Okay, now paste it into a dataset. From perspective. ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [January 26, 2021, 1:09pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/27 "2021-01-26T13:09:39Z")
Are you[quote=“OscarLucas, post:25, topic:34942, full:true”] The most easy way! ![](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/c/bcd6f16422d9f6f146f5928dc715111a8bf740a0.gif) [/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. ---
**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\ **Post date:** [January 27, 2021, 3:18pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/28 "2021-01-27T15:18:47Z")
@OscarLucas file-\>save as csv and we can avoid the rest of the adventure through GUI Land ```auto CREATE TABLE Test ( ID int , Data_Description varchar(50) ) ``` ```auto 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 😀 ---
**Author:** ![nader.chinichian](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nader.chinichian/32/9261_2.png) [@nader.chinichian](https://forum.inductiveautomation.com/u/nader.chinichian)\ **Post date:** [January 27, 2021, 7:58pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/29 "2021-01-27T19:58:02Z")
Hi Jordan For cell with formula I try manipulate your code to get the result of the formula. ```auto 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? ---
**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 28, 2021, 12:18pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/30 "2021-01-28T12:18:24Z")
`getCachedFormulaResultType()` is what you’re looking for. Script updated. ---
**Author:** ![nader.chinichian](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nader.chinichian/32/9261_2.png) [@nader.chinichian](https://forum.inductiveautomation.com/u/nader.chinichian)\ **Post date:** [February 1, 2021, 3:09pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/31 "2021-02-01T15:09:34Z")
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. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 1, 2021, 3:24pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/32 "2021-02-01T15:24:12Z")
> [@nader.chinichian](#): > > For project with every limited budget 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. ---
**Author:** ![nader.chinichian](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nader.chinichian/32/9261_2.png) [@nader.chinichian](https://forum.inductiveautomation.com/u/nader.chinichian)\ **Post date:** [February 1, 2021, 5:02pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/33 "2021-02-01T17:02:06Z")
How much does it cost for 1 report? ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [February 1, 2021, 5:05pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/34 "2021-02-01T17:05:42Z")
$700 off top of my head, full module is $3.5K again IIRC ---
**Author:** ![nader.chinichian](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nader.chinichian/32/9261_2.png) [@nader.chinichian](https://forum.inductiveautomation.com/u/nader.chinichian)\ **Post date:** [February 1, 2021, 5:09pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/35 "2021-02-01T17:09:08Z")
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. ---
**Author:** ![Ryan\_Shaw](https://avatars.discourse-cdn.com/v4/letter/r/dbc845/32.png) [@Ryan\_Shaw](https://forum.inductiveautomation.com/u/Ryan_Shaw)\ **Post date:** [January 13, 2022, 3:00pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/36 "2022-01-13T15:00:50Z")
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"](https://forum.inductiveautomation.com/t/cannot-convert-row-x-column-y-to-type-java-lang-long/34175) 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. ---
**Author:** ![Troy](https://avatars.discourse-cdn.com/v4/letter/t/85f322/32.png) [@Troy](https://forum.inductiveautomation.com/u/Troy)\ **Post date:** [January 13, 2022, 9:16pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/37 "2022-01-13T21:16:29Z")
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) ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [January 18, 2022, 1:12pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/38 "2022-01-18T13:12:33Z")
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](https://forum.inductiveautomation.com/uploads/short-url/jo7uXmP9CZYEjBWW3OVdiT9TPtv.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/](https://openpyxl.readthedocs.io/en/stable/) ---
**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\ **Post date:** [January 18, 2022, 2:14pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/39 "2022-01-18T14:14:14Z")
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 ---
**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 2, 2022, 11:23pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/40 "2022-07-02T23:23:42Z")
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, ---
**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:** [July 5, 2022, 3:30pm UTC](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942/41 "2022-07-05T15:30:43Z")
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. [Previous page](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942.md?page=1) [Next page](https://forum.inductiveautomation.com/t/copying-an-excel-file-to-a-tables-dataset/34942.md?page=3)