Perspective Table to CSV download - Random Error

Ignition
, ,
1

I have a script that worked until today. I thought maybe it was because of the number of pages in the table but I populated the table with a different larger date range, giving me a CSV with 45K rows from a table with hundreds of pages.

The range with the issue is only 21 pages but the script fails with this error.

line 13, in runAction TypeError: Unable to convert row 1, column 4 to type class java.lang.Long

The 4th column in the table is always a string so not sure what would cause this. The data is all very similar, regardless of the date range.

def runAction(self, event):
    # root > table-flex > body > table
    table = self.parent.parent.getChild("Table")
    
    # create a list of columns that are selected by user
    colsToKeep = [col["field"] for col in table.props.columns if col["visible"]]
    
    data = []
    for el in table.props.data:
        rowData = [el[col] for col in colsToKeep if col in el]
        data.append(rowData)
    
    newCsv = system.dataset.toCSV(system.dataset.toDataSet(colsToKeep, data))
    
    # Get the date from DateTimeInput_Start component
    raw_date = self.parent.parent.getChild("FlexContainer_Date_Range").getChild("DateTimeInput_Start").props.value
    
    # Convert the raw_date to a formatted string
    formatted_date = system.date.format(system.date.parse(raw_date), "yyyy-MM-dd HH:mm:ss")
    
    # Replace characters in formatted_date to make it filename-friendly
    filename_date = formatted_date.replace(":", "-").replace(" ", "_")
    
    # Create the filename
    filename = "Azure_Export_" + filename_date + ".csv"
    
    # Trigger the download
    system.perspective.download(filename, newCsv)
2

Row and column numbering is zero-based, so look at the fifth column.

1 Like
3

Actually, this will not yield a consistent length. Should probably be:

rowData = [el.get(col) for col in colsToKeep]

That will put nulls in the list when a key is missing from props.data on a particular row.

4

Good to know, the error always says row 1 so I was thinking it was not zero-based. The fifth column is a value. Most always a float but a few are Integers or Boolean.

I tried filtering the range by a few different types in the second column and the script worked every time.

5

After making this change it now says the issue is on row two.


line 13, in runAction TypeError: Unable to convert row 2, column 4 to type class java.lang.Long
6

This is a little odd. It just worked but when I ran it again it now says row (3) four is the issue.

In the current table this first 5 rows all have a value of 0. Not sure if the CSV is created in the current order of the table.

image

7

Perhaps you should show the first several rows of your data.

Keep in mind that system.dataset.toDataSet() determines the column types of the output dataset from the first row of the input. If anything changes type after the first row, breakage is likely.

8

If you need precise control over column types (I usually do), use the DatasetBuilder instead.

9

I'm still testing but even if I leave the range the same and only update the table from the Azure DB it will randomly work. It looks like all the same values or rows are there each time but in a different order. The error message will change the row number it has the error on. So just the order of rows in the table seems to cause the error. But the table always seems ok.

10

This still sounds like python duck typing is getting in the way.

As an example, say you have a first row where a column value is 1.3, the script will then use float as the column type, if in a following row that column has a value of 0 you’ll get an error because it would be trying to insert an integer into a float column.

Look into the Dataset Builder as @pturmel suggests.

1 Like
11

I was able to update the make CSV function to work where the original did not. The script now checks for int, float, and long types and casts them to float. Seems to work so far.

def runAction(self, event):
    # root > table-flex > body > table
    table = self.parent.parent.getChild("Table")
    
    # Create a list of columns that are selected by user
    colsToKeep = [col["field"] for col in table.props.columns if col["visible"]]
    
    # Prepare data for dataset
    data = []
    for el in table.props.data:
        rowData = []
        for col in colsToKeep:
            value = el.get(col)
            if isinstance(value, (int, float, long)):  # Ensure all numeric types are consistent
                value = float(value)
            rowData.append(value)
        data.append(rowData)
    
    # Create the dataset manually
    newDataSet = system.dataset.toDataSet(colsToKeep, data)
    newCsv = system.dataset.toCSV(newDataSet)
    
    # Get the date from DateTimeInput_Start component
    raw_date = self.parent.parent.getChild("FlexContainer_Date_Range").getChild("DateTimeInput_Start").props.value
    
    # Convert the raw_date to a formatted string
    formatted_date = system.date.format(system.date.parse(raw_date), "yyyy-MM-dd HH:mm:ss")
    
    # Replace characters in formatted_date to make it filename-friendly
    filename_date = formatted_date.replace(":", "-").replace(" ", "_")
    
    # Create the filename
    filename = "Azure_Export_" + filename_date + ".csv"
    
    # Trigger the download
    system.perspective.download(filename, newCsv)