# Convert dataset column values from string to integer

**URL:** <https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886>\
**Category:** Ignition\
**Created:** [December 8, 2015, 2:08am UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886 "2015-12-08T02:08:12Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![framebuilder](https://avatars.discourse-cdn.com/v4/letter/f/96bed5/32.png) [@framebuilder](https://forum.inductiveautomation.com/u/framebuilder)\
**Post date:** [December 8, 2015, 2:08am UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/1 "2015-12-08T02:08:12Z")

</div>

Hi everyone,  
I have a dataset with two columns. Column1 has string values that I need to convert into integers in order to use both columns on a chart. Is this possible?

I was thinking about creating a Custom Property (dataset) on the Root Container that is bound to the dataset and use a propertyChange event to fire a script to loop through the dataset, cast all the values in Column1 to an integer and then push the new dataset to another Custom Property. I am having a hard time with the script for this and seem to be overcomplicating it.

It would be nice if there was an Expression language to cast an entire column’s value of a dataset to integer, etc. Looks like Expression can only handle one.

---

<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:** [December 8, 2015, 2:57am UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/2 "2015-12-08T02:57:32Z")

</div>

[quote=“framebuilder”]It would be nice if there was an Expression language to cast an entire column’s value of a dataset to integer, etc. Looks like Expression can only handle one.[/quote]You’re in luck!  
The [view() expression function](https://inductiveautomation.com/forum/viewtopic.php?f=50&t=14532) can do this with one line of pseudo-SQL:`view("SELECT columnname1, int(columnname2) as newcolumnname",
  {Root Container.mySourceDataset})`… with appropriate substitutions for your case. 🙂

---

<div class="post-metadata">

**Author:** ![framebuilder](https://avatars.discourse-cdn.com/v4/letter/f/96bed5/32.png) [@framebuilder](https://forum.inductiveautomation.com/u/framebuilder)\
**Post date:** [December 8, 2015, 3:49am UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/3 "2015-12-08T03:49:35Z")

</div>

Phil,  
I tried installing the module but the Designer hangs at “Initializing Scripting”. If I uninstall, the Designer launches fine. This is on 7.7.6 with version 1.6.1 (b1510110139) Simulation Aids. A Gateway restart has not helped.

---

<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:** [December 8, 2015, 11:50am UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/4 "2015-12-08T11:50:03Z")

</div>

I may be oversimplifying it, but does Column1 on the original dataset have to be a string?

---

<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:** [December 8, 2015, 1:05pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/5 "2015-12-08T13:05:31Z")

</div>

[quote=“framebuilder”]Phil,  
I tried installing the module but the Designer hangs at “Initializing Scripting”. If I uninstall, the Designer launches fine. This is on 7.7.6 with version 1.6.1 (b1510110139) Simulation Aids. A Gateway restart has not helped.[/quote]Thanks for the heads-up. I hadn’t had a chance to play with 7.7.6 yet. Will fix.

---

<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:** [December 8, 2015, 1:32pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/6 "2015-12-08T13:32:37Z")

</div>

[quote=“pturmel”][quote=“framebuilder”]Phil,  
I tried installing the module but the Designer hangs at “Initializing Scripting”. If I uninstall, the Designer launches fine. This is on 7.7.6 with version 1.6.1 (b1510110139) Simulation Aids. A Gateway restart has not helped.[/quote]Thanks for the heads-up. I hadn’t had a chance to play with 7.7.6 yet. Will fix.[/quote]  
Ah, just noticed your build number. You should upgrade to the latest, b1511032223. I’ll go fix the link in the post.

---

<div class="post-metadata">

**Author:** ![framebuilder](https://avatars.discourse-cdn.com/v4/letter/f/96bed5/32.png) [@framebuilder](https://forum.inductiveautomation.com/u/framebuilder)\
**Post date:** [December 8, 2015, 2:47pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/7 "2015-12-08T14:47:26Z")

</div>

Jordan,  
I was wondering if someone would catch that. Unfortunately, it has to be a string because it comes from another datasource that we can’t modify.

Here’s what I came up with so far by playing with a Table as the output (I’ll move to a chart later). It seems to be working for the one string column and converting to int, but I can’t figure out how to build the next column which is already an int.

[code]data = event.source.parent.CycleTimes  
table = event.source.parent.getComponent(“Table”)

rows = []  
headers = [“OP100 CT”]  
for row in range(data.rowCount):  
oneRow = data.getValueAt(row, “OP100 Cycle Time2”)  
rows.append([int(oneRow)])

data = system.dataset.toDataSet(headers,rows)  
table.data = data  
[/code]

I’m still getting used to playing with datasets and going through the docs so I’m sure it’s something simple that I’m missing.

---

<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:** [December 8, 2015, 3:22pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/8 "2015-12-08T15:22:55Z")

</div>

Actually, you have a great start! Building the columns is done at the same time. It could look something like this:

[code]data = event.source.parent.CycleTimes  
table = event.source.parent.getComponent(“Table”)

rows = []  
headers = [“OP100 CT”, “column2Name”]  
for row in range(data.rowCount):  
oneRow = [int(data.getValueAt(row, “OP100 Cycle Time2”), data.getValueAt(row, “theOtherColumn”)]  
rows.append(oneRow)

data = system.dataset.toDataSet(headers,rows)  
table.data = data[/code]

---

<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:** [December 8, 2015, 3:27pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/9 "2015-12-08T15:27:22Z")

</div>

Also, I like Phil’s solution a lot! Just wanna be clear on that one, as it’s a good all-in-one tool.

On the other hand, doing it the “long way” is a good way to build your coding skills.

Just shows there are a lot of skinned cats… 😆

---

<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:** [December 8, 2015, 3:59pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/10 "2015-12-08T15:59:44Z")

</div>

[quote=“JordanCClark”]Also, I like Phil’s solution a lot! Just wanna be clear on that one, as it’s a good all-in-one tool.

On the other hand, doing it the “long way” is a good way to build your coding skills.

Just shows there are a lot of skinned cats… 😆[/quote]The simulation aids module can help you learn these algorithms, too – if you can express the problem in pseudo-SQL to get a result from view(), you can turn on its ‘debug’ mode to see the python it created to solve your problem. Which you can then cut, paste, study, and customize further.

---

<div class="post-metadata">

**Author:** ![framebuilder](https://avatars.discourse-cdn.com/v4/letter/f/96bed5/32.png) [@framebuilder](https://forum.inductiveautomation.com/u/framebuilder)\
**Post date:** [December 8, 2015, 4:14pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/11 "2015-12-08T16:14:01Z")

</div>

Nice. Here’s the code that got it working.

```auto
data = event.source.parent.CycleTimes
table = event.source.parent.getComponent("Table")

rows = []
headers = ["OP100 CT", "Line PC"]
for row in range(data.rowCount):
   oneRow = [int(data.getValueAt(row, "OP100 Cycle Time2")), data.getValueAt(row, "Line Production Count")]
   rows.append(oneRow)

data = system.dataset.toDataSet(headers,rows)
table.data = data
```

Phil,  
I’m going to try out the Simulation Aids module as well, but I have a question. You mention it works with Expressions–does that mean when I go to bind a chart’s data, I can use the Expression button and I will see a new option on the right side that has view() and some others? I’m getting excited.

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [December 8, 2015, 4:39pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/12 "2015-12-08T16:39:43Z")

</div>

Here’s how to implement the solution using a [MutablePyDataSet](http://doc.perfectabstractions.com/modules/powerscripting/mutablepydataset.html):

`table = event.source.parent.getComponent('Table')
data = pa.dataset.toData(table.data)
for row in data:
	row["Col 1"] = int(row["Col 1"])
table.data = data.toDataSet()` MutablePyDataSet is a swiss army knife for Datasets.

To show off the various things you can do with a MutablePyDataSet, here’s another way it could be done:`table = event.source.parent.getComponent('Table')
data = pa.dataset.toData(table.data)
data.setColumn("OP100 Cycle Time2",map(int,data.columns["OP100 Cycle Time2"]))
table.data = data.toDataSet()`1. The “pa.dataset.toData” function converts the dataset to a MutablePyDataSet.

1. data.columns[“OP100 Cycle Time2”] gets the entire column as a list.

2. map(int,data.columns[“OP100 Cycle Time2”]) creates a new list from the “OP100 Cycle Time2” column list but with each value converted to an int.

3. data.setColumn(“OP100 Cycle Time2”,map(str,data.columns[“OP100 Cycle Time2”])) sets a new column at position “OP100 Cycle Time2”, using the result of map(int,data.columns[“OP100 Cycle Time2”]) as the new column.

MutablePyDataSet is part of the PA Power Scripting Module.  
[nickmudge.info/post/pa-power-scripting-module](http://nickmudge.info/post/pa-power-scripting-module)  
[marketplace.inductiveautomation. … oduleId=87](http://marketplace.inductiveautomation.com/moduledetail?13&moduleId=87)  
[nickmudge.info/post/datasets-in-Ignition](http://nickmudge.info/post/datasets-in-Ignition)

Best,

---

<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:** [December 8, 2015, 7:04pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/13 "2015-12-08T19:04:04Z")

</div>

[quote=“framebuilder”]I’m going to try out the Simulation Aids module as well, but I have a question. You mention it works with Expressions–does that mean when I go to bind a chart’s data, I can use the Expression button and I will see a new option on the right side that has view() and some others? I’m getting excited.[/quote]Yes. In the f(x) menu. 😃  
The view() function and the objectScript() function are the most versatile for general use. The rest of the module really is tailored to simulation of not-yet-available processes.  
Expression functions have the big advantage that they run in the designer without having to ask for preview mode, making some design tasks much quicker and/or more intuitive. The demo project has several live examples.

---

<div class="post-metadata">

**Author:** ![ahardin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahardin/32/66398_2.png) [@ahardin](https://forum.inductiveautomation.com/u/ahardin)\
**Post date:** [May 2, 2018, 5:04pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/14 "2018-05-02T17:04:34Z")

</div>

Hi Phil,  
I have the view() function working with a PowerTable (thank you btw), but when I try grab a piece of information in a Text Field Component all I get is Dataset [1R x 1C]. Is there a way return the value rather than the dataset info?

Thanks,  
Aaron

---

<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:** [May 2, 2018, 5:23pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/15 "2018-05-02T17:23:47Z")

</div>

Like a scalar query? Try adding [0,0] at the end of the expression.

---

<div class="post-metadata">

**Author:** ![ahardin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahardin/32/66398_2.png) [@ahardin](https://forum.inductiveautomation.com/u/ahardin)\
**Post date:** [May 2, 2018, 6:39pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/16 "2018-05-02T18:39:53Z")

</div>

I have a dataset tag that pulls data from our ERP system. Working with PowerTables getting specific information seems pretty easy, however, grabbing specific information into other components doesn’t work the way that I would like. I’m new to Python so my going is a little slow.

I tried the following:  
view({Root Container.txtStatusPath.text}, {Performance/Performance\_Detail})[0,0]  
view({Root Container.txtStatusPath.text}, {Performance/Performance\_Detail}[0,0])  
view({Root Container.txtStatusPath.text}, {Performance/Performance\_Detail[0,0]})  
None of these worked.

I’m trying to learn about working with datasets as much as I can now.

Thanks,  
Aaron

---

<div class="post-metadata">

**Author:** ![ahardin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahardin/32/66398_2.png) [@ahardin](https://forum.inductiveautomation.com/u/ahardin)\
**Post date:** [May 2, 2018, 7:02pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/17 "2018-05-02T19:02:56Z")

</div>

I see what you mean by the [0,0] now. Still not answered, but it helps. Thanks!

---

<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:** [May 2, 2018, 8:06pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/18 "2018-05-02T20:06:53Z")

</div>

> [@ahardin](#):
>
> I'm new to Python so my going is a little slow.

Careful! Ignition's expression language isn't python. view() is part of the expression language--it just _ **uses** _ python for its internals for for the expressions nested within the pseudo-SQL.

Anyways, this one should have worked, since view() returns a dataset:

```python
view({Root Container.txtStatusPath.text}, {Performance/Performance_Detail})[0,0]

```

assuming {Root Container.txtStatusPath.text} contains your pseudo-SQL.

---

<div class="post-metadata">

**Author:** ![ahardin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahardin/32/66398_2.png) [@ahardin](https://forum.inductiveautomation.com/u/ahardin)\
**Post date:** [May 3, 2018, 11:46am UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/19 "2018-05-03T11:46:52Z")

</div>

Hi Phil,  
Sorry I misspoke, I should have said new to Ignition (but also Python and the Expression language).

Thanks for the info! Trying to learn quickly and not ask the same question twice.

---

<div class="post-metadata">

**Author:** ![tamerplc](https://avatars.discourse-cdn.com/v4/letter/t/d78d45/32.png) [@tamerplc](https://forum.inductiveautomation.com/u/tamerplc)\
**Post date:** [March 2, 2019, 12:48pm UTC](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886/20 "2019-03-02T12:48:34Z")

</div>

@framebuilder  
for below line in your script , i need to convert string to Datetime what’s the code should be used .  
Thanks

```auto
oneRow = [int(data.getValueAt(row, "OP100 Cycle Time2")), data.getValueAt(row, "Line Production Count")]

```

[Next page](https://forum.inductiveautomation.com/t/convert-dataset-column-values-from-string-to-integer/10886.md?page=2)