# Vision: Convert dataset column type

**URL:** <https://forum.inductiveautomation.com/t/vision-convert-dataset-column-type/26341>\
**Category:** Ignition\
**Created:** [July 8, 2019, 7:32am UTC](https://forum.inductiveautomation.com/t/vision-convert-dataset-column-type/26341 "2019-07-08T07:32:06Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [July 8, 2019, 7:32am UTC](https://forum.inductiveautomation.com/t/vision-convert-dataset-column-type/26341/1 "2019-07-08T07:32:06Z")

</div>

I have a dataset that I’m returning from an SQL query, however I need to post-process it to convert a few of the fields to java.awt.Color object types. They’re incoming as strings e.g. “java.awt.Color[r=25,g=23,b=123]”

Is there a simple way to do this?

What I’m doing at the moment is super clunky:

1. get the headers from the dataset
2. get the first row as a list
3. convert all of the colour columns in the list to a colour object using e.g.: system.gui.color(“java.awt.color([…])”). Now this list contains colour objects rather than colours as strings.
4. create a dataset from these lists
5. join the SQL dataset to this new dataset, thereby forcing the SQL dataset to use the new java.awt.Color type
6. delete the original column…

I tried using `system.dataset.clearDataSet()` in between steps 3 and 4, but this seemed to not retain the column type.

EDIT:  
This logic however doesn’t work sometimes, especially if the first row has some NoneType values in it, I think… I’m pulling my hair out trying to get this working 😕

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 8, 2019, 11:45am UTC](https://forum.inductiveautomation.com/t/vision-convert-dataset-column-type/26341/2 "2019-07-08T11:45:35Z")

</div>

Use Ignition’s [DatasetBuilder](http://files.inductiveautomation.com/sdk/javadoc/ignition79/795/com/inductiveautomation/ignition/common/util/DatasetBuilder.html). Something like this:

```auto
from com.inductiveautomation.ignition.common.util import DatasetBuilder
from java.lang import Integer, String, Object
from java.awt import Color

def forceTypes(sqlDS):
    builder = DatasetBuilder.newBuilder()
    builder.colNames(sqlDS.columnNames)
    originalTypes = list(sqlDS.columnTypes)
    i = #some column index for a color
    originalTypes[i] = Color
    builder.colTypes(originalTypes)
    for r in range(sqlDS.rowCount):
        builder.addRow([sqlDS.getValueAt(r, c) for c in range(sqlDS.columnCount)])
    return builder.build()

```

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [July 8, 2019, 11:48am UTC](https://forum.inductiveautomation.com/t/vision-convert-dataset-column-type/26341/3 "2019-07-08T11:48:14Z")

</div>

Thanks Phil, didn’t know this existed!