# Creating a duplicate dataset with different column datatypes

**URL:** <https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804>\
**Category:** Ignition\
**Created:** [July 24, 2023, 6:08pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804 "2023-07-24T18:08:40Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Juan\_Caquias](https://avatars.discourse-cdn.com/v4/letter/j/43a26b/32.png) [@Juan\_Caquias](https://forum.inductiveautomation.com/u/Juan_Caquias)\
**Post date:** [July 24, 2023, 6:08pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/1 "2023-07-24T18:08:40Z")

</div>

Hello all,

I have been scratching my head at this for quite a bit this morning, and even though it's a lower priority project, it just bothers me that it feels like I'm _that_ close, and can't figure it out.

Long story short, I have a table that pulls data from a SQL query that executes a stored procedure. This table has all of the data I need, but unfortunately, the stored procedure casts all the values as varchar data type. I am trying to allow a chart to plot values from this table, but I need to get those data into a numeric form before the chart can handle it properly.

I'd prefer not to mess with the database or stored procedures if possible. I am wondering if there is any way within the Designer to create that dataset and then somehow modify it to change the column data types to numeric ones. My first thought was to do something like creating the base dataset as a custom property of the root container, and then query that using CAST AS statements to convert the datatypes, but I'm not sure that's possible.

Any workarounds? I have seem some similar threads that show some python codes, but I'm not sure how to set that up to get it to output to a table component.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 24, 2023, 6:18pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/2 "2023-07-24T18:18:36Z")

</div>

> [@Juan\_Caquias](#):
>
> base dataset as a custom property of the root container, and then query that using CAST AS statements to convert the datatypes, but I'm not sure that's possible.

I don't believe that the designer natively has any "CAST AS" statements for a dataset.

Pretty sure the only way to accomplish this is to use the Dataset Builder looping through the data casting each value to the correct type.

---

<div class="post-metadata">

**Author:** ![Juan\_Caquias](https://avatars.discourse-cdn.com/v4/letter/j/43a26b/32.png) [@Juan\_Caquias](https://forum.inductiveautomation.com/u/Juan_Caquias)\
**Post date:** [July 24, 2023, 6:24pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/3 "2023-07-24T18:24:28Z")

</div>

Thanks - this Dataset Builder is something I've seen mentioned in other threads.

Is there documentation for this somewhere? I've seen quite a few blocks of python code, but I'm not sure where to even begin with how to incorporate this on my window. My first thought is that it is supposed to be set as a propertyChange script and respectively update the visible table with the filtered data.

---

<div class="post-metadata">

**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 24, 2023, 6:36pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/4 "2023-07-24T18:36:17Z")

</div>

> [@Ignition Extensions - Convenience utilities for advanced users](https://forum.inductiveautomation.com/t/ignition-extensions-convenience-utilities-for-advanced-users/64615):
>
> Introducing a hopefully community-engaged effort to build a useful repository of scripting and other Ignition utilities for everyone to benefit from. Download the latest release from the [Releases](https://github.com/IgnitionModuleDevelopmentCommunity/ignition-extensions/releases) page and take advantage of the following functions: Scripting Functions system.util system.util.getContext(): Context A convenience function to retrieve the local "context" object, useful as an entrypoint to other advanced scripting. system.util.deepCopy(object): Object Converts a given Pyth…

`system.dataset.map` from Ignition Extensions could do this pretty easily as a single operation.

It's also definitely possible using native dataset operations; you'll probably want to construct a typical list-of-lists PyDataset approach.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 24, 2023, 7:01pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/5 "2023-07-24T19:01:50Z")

</div>

No worries, I can help with that.

Create a custom property somewhere on the display, I would probably add it to the component. Bind this property to the data coming from the dataset.

Then in the property change event for that property place a script similar to this to update the data property on the component itself.

Documentation [DatasetBuilder](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.27/com/inductiveautomation/ignition/common/util/DatasetBuilder.html#%3Cinit%3E())

```auto
from com.inductiveautomation.ignition.common import DatasetBuilder
from java.lang import String,Integer

rawDs = system.dataset.toPyDataSet(event.source.rawData)

headers = rawDs.columnHeaders
colTypes = [String.Class,Integer.Class]

builder = DatasetBuilder.newBuilder()
builder.colNames(headers)
builder.colTypes(colTypes)

for row in ds:
    builder.addRow([colType(row[colName]) for colName,colType in zip(headers,colTypes)])

event.source.data = builder.build()

```

---

<div class="post-metadata">

**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\
**Post date:** [July 24, 2023, 7:38pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/6 "2023-07-24T19:38:12Z")

</div>

I had to do something similar a while back. This might be useful to you.

> [@\[SOLVED\] Is there a way to convert a dataset column type?](https://forum.inductiveautomation.com/t/solved-is-there-a-way-to-convert-a-dataset-column-type/49971):
>
> Is there a way to convert a dataset column type? [csv\_data] [script] I am trying to import a CSV file into a dataset tag but the numbers come in as strings. What would be the easiest way to accomplish this? Thanks for any guidance.

---

<div class="post-metadata">

**Author:** ![Juan\_Caquias](https://avatars.discourse-cdn.com/v4/letter/j/43a26b/32.png) [@Juan\_Caquias](https://forum.inductiveautomation.com/u/Juan_Caquias)\
**Post date:** [July 24, 2023, 8:55pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/7 "2023-07-24T20:55:50Z")

</div>

This looks promising but I'm still running into some issues. Line 1 returns an error. Do I need an extension to import DatasetBuilder?

```auto
Traceback (most recent call last):
  File "<event:propertyChange>", line 1, in <module>
ImportError: cannot import name DatasetBuilder

Ignition v8.1.25 (b2023021409)
Java: Azul Systems, Inc. 11.0.17

```

Also, in the for loop, should "ds" instead be "rawDs"?

---

<div class="post-metadata">

**Author:** ![Juan\_Caquias](https://avatars.discourse-cdn.com/v4/letter/j/43a26b/32.png) [@Juan\_Caquias](https://forum.inductiveautomation.com/u/Juan_Caquias)\
**Post date:** [July 24, 2023, 9:25pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/8 "2023-07-24T21:25:15Z")

</div>

Okay, I'm getting a little closer. @bmeyers, thanks for sharing that thread, as that helped me diagnose some stuff. Here's the current code:

```python
from com.inductiveautomation.ignition.common.util import DatasetBuilder
from java.lang import String,Integer

rawDs = system.dataset.toPyDataSet(event.source.DefaultDataset)

headers = rawDs.columnNames
colTypes = [Integer.Class,String.Class,String.Class,Integer.Class,String.Class,Datetime.Class,String.Class,String.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class,Float.Class]

builder = DatasetBuilder.newBuilder()
builder.colNames(headers)
builder.colTypes(colTypes)

for row in ds:
    builder.addRow([colType(row[colName]) for colName,colType in zip(headers,colTypes)])

event.source.data = builder.build()

```

I still get an error on line 7, saying that Integer doesn't have an attribute "Class". Any advice?

I figure I'll also have to import the relevant java.langs for float, and datetime, but one thing at a time.

---

<div class="post-metadata">

**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 24, 2023, 9:31pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/9 "2023-07-24T21:31:47Z")

</div>

Lowercase `c`; `Integer.class`, would be the _Java_ syntax, but within Jython you just provide the full static name: `[Integer, String, String, Integer]`, I'm pretty sure.

---

<div class="post-metadata">

**Author:** ![Juan\_Caquias](https://avatars.discourse-cdn.com/v4/letter/j/43a26b/32.png) [@Juan\_Caquias](https://forum.inductiveautomation.com/u/Juan_Caquias)\
**Post date:** [July 24, 2023, 9:47pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/10 "2023-07-24T21:47:04Z")

</div>

You are correct.

I was able to get the code to work with that edit, along with a couple of others.

Posting full code below for others who may run into the same issue.

I replaced Datetime (does not exist) with String for the time being, and must now diagnose another issue with where this data goes down the line. But the code successfully transforms the dataset to have the correct data types.

```python
from com.inductiveautomation.ignition.common.util import DatasetBuilder
from java.lang import String,Integer,Float

rawDs = system.dataset.toPyDataSet(event.source.DefaultDataset)

headers = rawDs.columnNames
colTypes = [Integer,String,String,Integer,String,String,String,String,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float,Float]

builder = DatasetBuilder.newBuilder()
builder.colNames(headers)
builder.colTypes(colTypes)

for row in rawDs:
    builder.addRow([colType(row[colName]) for colName,colType in zip(headers,colTypes)])

event.source.data = builder.build()

```

This is running as a propertyChange script on the component, with DefaultDataset being a custom property with the dataset that needs to be transformed, and Data being the transformed dataset.

---

<div class="post-metadata">

**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 24, 2023, 10:00pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/11 "2023-07-24T22:00:46Z")

</div>

A couple brief notes that may help you down the road:

1. You can use Python's operator overloading to make constructing the repetitive list a bit nicer/easier to maintain:

```auto
colTypes = [Integer, String, String, Integer] + ([String] * 4) + ([Float] * 30)

```

1. You can take advantage of the 'fluent' API on DatasetBuilder to avoid some repetition:

```auto
builder = DatasetBuilder.newBuilder().colNames(headers).colTypes(colTypes)

```

1. Accessing columns by name is "expensive" compared to accessing by column index, so you'll get a not-insignificant performance boost using `enumerate` over `zip`:

```auto
for row in rawDs:
    builder.addRow([colType(row[colIndex]) for colIndex, colType in enumerate(colTypes)])

```

---

<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 24, 2023, 10:13pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/12 "2023-07-24T22:13:07Z")

</div>

> [@Juan\_Caquias](#):
>
> This is running as a propertyChange script on the component, with DefaultDataset being a custom property with the dataset that needs to be transformed, and Data being the transformed dataset.

You might find it easier to put this expression binding on your `data` property, after installing my latest [Simulation Aids](https://forum.inductiveautomation.com/t/automation-professionals-simulation-aids-v2/74934/54) module (assuming that last bug is fixed):

```auto
unionAll(
	forEach(
		columnsOf({Root container.path.to.custom.prop}),
		asList(
			it()[0],
			if(
				idx() < 8,
				asList('I', 'str', 'str', 'I', 'str', 'str', 'str', 'str')[idx()],
				'F'
			)
		)
	),
	{Root container.path.to.custom.prop}
)

```

😁

{ The `unionAll` function is really just a glorified wrapper around the DatasetBuilder class. }

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 25, 2023, 3:47am UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/13 "2023-07-25T03:47:34Z")

</div>

> [@Juan\_Caquias](#):
>
> I replaced Datetime (does not exist) with String for the time being,

This would just be ~~`java.lang.Date`~~.

---

<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 25, 2023, 9:42am UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/14 "2023-07-25T09:42:06Z")

</div>

> [@lrose](#):
>
> This would just be java ~~.lang~~.Date.

`java.util.Date`

---

<div class="post-metadata">

**Author:** ![Juan\_Caquias](https://avatars.discourse-cdn.com/v4/letter/j/43a26b/32.png) [@Juan\_Caquias](https://forum.inductiveautomation.com/u/Juan_Caquias)\
**Post date:** [July 25, 2023, 4:32pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/15 "2023-07-25T16:32:24Z")

</div>

By that do you mean the following?

```python
from java.util import Date

...

colTypes = [...,Date,...]

```

If so, I might just be running into a problem with the code interpreting the string as a datetime, as I get an error. To be fair, whatever the stored procedure is doing, it spits out a string formatted as hh:mm:ss, so I don't blame it for not being able to get to a proper datetime from there.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 25, 2023, 4:46pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/16 "2023-07-25T16:46:40Z")

</div>

Wow, I would have someone take a very close look at that stored procedure. I don't know your data, but it would seem to me that loosing the context of the day that the time occurred on is less than desirable. This also begs the question of how the "Date" is actually being stored in the Database.

That being said, you should be able to use `system.date.parse()` to get a valid `java.util.Date` object.

```auto
system.date.parse('dateString','hh:mm:ss')

```

This will produce a date set to Jan 1 1970 with the time as supplied.

If you care about the date and you want it to be "today's" date then you could do something like:

```auto
hour,mins,sec = [int(part) for part in'dateString'.split(':')]
system.date.setTime(system.date.now(),hour,mins,sec)

```

---

<div class="post-metadata">

**Author:** ![Juan\_Caquias](https://avatars.discourse-cdn.com/v4/letter/j/43a26b/32.png) [@Juan\_Caquias](https://forum.inductiveautomation.com/u/Juan_Caquias)\
**Post date:** [July 25, 2023, 4:59pm UTC](https://forum.inductiveautomation.com/t/creating-a-duplicate-dataset-with-different-column-datatypes/76804/17 "2023-07-25T16:59:25Z")

</div>

And that's a fair point. I am meeting with someone later in the week to take a look, since SQL is unfamiliar to me and I have a few other related questions.

Regarding the dates, I know that the datetime is stored on the database in accessible manner. I think the bigger issue here is me using a stored procedure for an unintended purpose. I believe the stored procedure I am referencing is intended for preparing this specific set of data for printed reports - which I believe is why everything ends up as a varchar. It just so happens that this specific set of data is exactly what I needed for an ignition project, and I figured I could use the existing stored procedure instead of reinventing the wheel.

Ultimately, I need to get a little more familiar with SQL so in the future I can investigate these kinds of issues without as much fear of walls of code.