Problems converting from excel to dataset

Ignition
,
1

Dear all,

I am having problems using the function provided by @JordanCClark

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
image925×353 105 KB

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 (281.4 KB)

Thanks in advance guys,

2

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:

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

Thank you!! Nice @JordanCClark

4

@g.cascales how to fix this format in Excel

image
image704×272 55.1 KB

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

Thanks,
Priyanka

5

@Priyanka.Khandge do you still need help?

6

@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

Thanks,
Priyanka