# Problems converting from excel to dataset

**URL:** <https://forum.inductiveautomation.com/t/problems-converting-from-excel-to-dataset/53485>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [November 26, 2021, 9:38am UTC](https://forum.inductiveautomation.com/t/problems-converting-from-excel-to-dataset/53485 "2021-11-26T09:38:20Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![g.cascales](https://avatars.discourse-cdn.com/v4/letter/g/71c47a/32.png) [@g.cascales](https://forum.inductiveautomation.com/u/g.cascales)\
**Post date:** [November 26, 2021, 9:38am UTC](https://forum.inductiveautomation.com/t/problems-converting-from-excel-to-dataset/53485/1 "2021-11-26T09:38:20Z")

</div>

Dear all,

I am having problems using the function provided by @JordanCClark

> [@Copying an Excel file to a table's dataset](https://forum.inductiveautomation.com/t/copying-a-excel-file-to-a-tables-dataset/34942/13):
>
> Got my paperwork done early, so here's a sample to read from an Excel file EDIT: Reworked a bit to work with both .xls and .xlsx files EDIT: Modified to properly cast data types EDIT: Added handler for formula cell types EDIT: Added parameters to optionally specify sheet, row, and column numbers Reading back a file created from my other example: def excelToDataSet(fileName, hasHeaders = False, sheetNum = 0, firstRow = None, lastRow = None, firstCol = None, lastCol = None): import org.apac…

The function was working nicely, but I do not know how to solve this:

- I am trying to create a dataset from the sheet 1 of an excel document. What is more, I want to be the first row (headers in the converted dataset) the index number 9 in the image:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/2/428e079beedcee2df51fe8bbef52b606cedc1383.png)

Therefore, I just modified inside the function the index of the sheet and the firstRow index:

`
sheet = wb.getSheetAt(1)
firstRow = 8
`

PS: I tried changing lastRow to i.e. 12 and it works.  
PS2: It looks like the excel file has many blanks (please find attached an example of the document)  
PS3: I have tried with other different formatted "large" excel (770 rows and 12 columns) and it works.  
[Test.xlsx](https://forum.inductiveautomation.com/uploads/short-url/5yB02lVckLO6ASzH5MzE0HMAfvT.xlsx) (281.4 KB)

Thanks in advance guys,

---

<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:** [November 29, 2021, 2:13pm UTC](https://forum.inductiveautomation.com/t/problems-converting-from-excel-to-dataset/53485/2 "2021-11-29T14:13:17Z")

</div>

The rows have differing numbers of columns and a huge amount of blank rows underneath. Once a cell is touched (for example a value put in, then erased, the cell stays touched).  
I modified the script, adding options to select first/last rows and first/last columns

After the additions the data would import using:

```python
ds = util.excelToDataSet(fileName, 
                         hasHeaders = True,
                         sheetNum = 1, 
                         firstRow = 8,
                         lastRow = 756,
                         lastCol = 9)

```

---

<div class="post-metadata">

**Author:** ![g.cascales](https://avatars.discourse-cdn.com/v4/letter/g/71c47a/32.png) [@g.cascales](https://forum.inductiveautomation.com/u/g.cascales)\
**Post date:** [November 30, 2021, 6:36am UTC](https://forum.inductiveautomation.com/t/problems-converting-from-excel-to-dataset/53485/3 "2021-11-30T06:36:15Z")

</div>

Thank you!! Nice @JordanCClark

---

<div class="post-metadata">

**Author:** ![Priyanka.Khandge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/priyanka.khandge/32/35989_2.png) [@Priyanka.Khandge](https://forum.inductiveautomation.com/u/Priyanka.Khandge)\
**Post date:** [May 17, 2023, 12:55pm UTC](https://forum.inductiveautomation.com/t/problems-converting-from-excel-to-dataset/53485/4 "2023-05-17T12:55:39Z")

</div>

@g.cascales how to fix this format in Excel

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

I have tried to some format in Excel can you please help me how fix this for more information I have attached a link [https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828?u=priyankakhandge](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828)

Thanks,  
Priyanka

---

<div class="post-metadata">

**Author:** ![g.cascales](https://avatars.discourse-cdn.com/v4/letter/g/71c47a/32.png) [@g.cascales](https://forum.inductiveautomation.com/u/g.cascales)\
**Post date:** [May 18, 2023, 10:39am UTC](https://forum.inductiveautomation.com/t/problems-converting-from-excel-to-dataset/53485/5 "2023-05-18T10:39:37Z")

</div>

@Priyanka.Khandge do you still need help?

---

<div class="post-metadata">

**Author:** ![Priyanka.Khandge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/priyanka.khandge/32/35989_2.png) [@Priyanka.Khandge](https://forum.inductiveautomation.com/u/Priyanka.Khandge)\
**Post date:** [May 18, 2023, 10:47am UTC](https://forum.inductiveautomation.com/t/problems-converting-from-excel-to-dataset/53485/6 "2023-05-18T10:47:08Z")

</div>

@g.cascales Thanks for your response.

Yes, For info I have added a link [https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/7?u=priyankakhandge](https://forum.inductiveautomation.com/t/export-dataset-and-operator-name-to-excel-in-vision/73828/7)

Thanks,  
Priyanka