# Convert columntype in table to integer **URL:** \ **Category:** Ignition\ **Created:** [September 26, 2014, 12:34pm UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103 "2014-09-26T12:34:41Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![stokstaart](https://avatars.discourse-cdn.com/v4/letter/s/3da27b/32.png) [@stokstaart](https://forum.inductiveautomation.com/u/stokstaart)\ **Post date:** [September 26, 2014, 12:34pm UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/1 "2014-09-26T12:34:41Z")
Dear all, I’ve been breaking my head over this one but i think there should be a simple solution i’m overlooking… On a page i have a table and a button, by pressing the button i call on a script which empties the table using `Headers= ["Header1: "," Header2"] blankdataset = [] table.data = system.dataset.toDataSet(Headers,blankdataset)` Then, using a for loop and “system.db.runQuery” i call on an SQL query to fetch some data which gets imported to the table using the ```auto table.data = system.dataset.addRow(table.data, Row) ``` The challenge i am facing is that doing this makes all the columntypes in “String” format while the data i am fetching is a mix of integer,strings and dates and i would like to be able to (for instance…) change rowbackground color based on one of the columns. Does anyone knows of a solution and/or script command to change the columntype? - Edit: forgot to mention i am using multipple for loops to get data from different tables in the database, this is the main reason i am creating a complete row and adding that to the table… ---
**Author:** ![JGJohnson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgjohnson/32/29582_2.png) [@JGJohnson](https://forum.inductiveautomation.com/u/JGJohnson)\ **Post date:** [September 26, 2014, 12:51pm UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/2 "2014-09-26T12:51:02Z")
There is a simple solution. ```auto table.data = system.db.runQuery(query) ``` In this case trying to append the dataset is unnecessary. Edit: Forgot about the for loop that you are using. In that case I think you should build a dataset in the loop and when that is done then set the table data. ```auto #psuedo code rows = [] headers = ["col_1","col_2"] for some loop: data = system.db.runQuery(new_query) for row in data: rows.append(row) table.data = system.dataset.toDataSet(headers, rows) ``` ---
**Author:** ![stokstaart](https://avatars.discourse-cdn.com/v4/letter/s/3da27b/32.png) [@stokstaart](https://forum.inductiveautomation.com/u/stokstaart)\ **Post date:** [September 26, 2014, 1:40pm UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/3 "2014-09-26T13:40:51Z")
Dear JGJohnson, thank you for your quick reply. Unfortunately my opening post was incomplete… The reason i am using the addrow is that i am getting data from multiple tables in the database, therefore i am using multiple for loops… and i have really no idea how to merge multiple datasets to one. Still hope you have an answer for me 😉 ---
**Author:** ![JGJohnson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgjohnson/32/29582_2.png) [@JGJohnson](https://forum.inductiveautomation.com/u/JGJohnson)\ **Post date:** [September 26, 2014, 1:48pm UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/4 "2014-09-26T13:48:20Z")
Do you absolutely have to do this in script? Seems to me that he simplest solution would be to build a view in the database that gathers data from all of the tables and then run a query against that view in Ignition. Alternatively if all of the queries are returning the same number of columns all with the same data-types, then you could forget about clearing the table data-set. Just set the table data to the result of the 1st query and then as new queries are run append the rows to the table data-set. There is no way to change the data-type of a column in an existing data-set as far as I know. ---
**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\ **Post date:** [January 15, 2018, 10:10pm UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/5 "2018-01-15T22:10:47Z")
Use system.dataset.fromCSV to specify data types and column headers for your blank dataset like this: ```auto blankDataset = """#NAMES Header1, Header2, #TYPES d,I #ROWS,0""" ``` See [Ignition Manual](https://docs.inductiveautomation.com:8443/display/DOC79/system.dataset.fromCSV) for data types, etc. ---
**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:** [January 16, 2018, 1:09am UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/6 "2018-01-16T01:09:45Z")
Consider using the platform’s [DatasetBuilder](http://files.inductiveautomation.com/sdk/javadoc/ignition79/793/com/inductiveautomation/ignition/common/util/DatasetBuilder.html) class. Something like: ```auto from java.lang import Integer, String from com.inductiveautomation.ignition.common.util import DatasetBuilder def someFunction(): b = DatasetBuilder.newBuilder().colNames("col1", "col2").colTypes(Integer, String) for x in range(5): b.addRow(x, "V%d" % x) return b.build() ``` ---
**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\ **Post date:** [March 13, 2018, 12:17am UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/7 "2018-03-13T00:17:04Z")
Thanks Phil _(was Pat)_. My posted solution worked okay for a really simple application where I needed to store data in correct types, but your solution is superior. For x in range fits in nicely to create a dataset with ten of this column, ten of that column, and ten of another bunch of columns. ---
**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:** [March 13, 2018, 12:28am UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/8 "2018-03-13T00:28:51Z")
Pat? I see no Pat here in this topic. (-: ---
**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\ **Post date:** [March 13, 2018, 3:23am UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/9 "2018-03-13T03:23:40Z")
Oh Pat, you are so silly! ---
**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\ **Post date:** [March 14, 2018, 11:02pm UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/10 "2018-03-14T23:02:07Z")
Oh, you’re going by Phil now! 😉 Somehow I had Pat in my mind for your avatar; sorry 'bout that. ---
**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:** [March 15, 2018, 12:27am UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/11 "2018-03-15T00:27:07Z")
Well, they do sound a lot alike… 😉 ---
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [June 4, 2020, 6:11pm UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/12 "2020-06-04T18:11:49Z")
@pturmel, How do you dynamically add columnNames and types. I mean, I am build a dataset from another dataset. So I could get the columns for the original dataset as .getColumnNames(). I can directly pass list ? Also, I am doing this in component scripting. Thanks ! ---
**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:** [June 4, 2020, 8:24pm UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/13 "2020-06-04T20:24:12Z")
> [@kaushik.shah](#): > > How do you dynamically add columnNames and types. I'd use a PyDataSet for that. It will preserve datatypes for you. ```python def addColumns(datasetIn, newColNames, newColData): colNames = list(datasetIn.getColumnNames()) pyData = system.dataset.toPyDataSet(dataset) newRows = [list(row) + list(addCols) for row, addCols in zip(pyData, zip(*newColData))] return system.dataset.toDataSet(colNames+newColNames, newRows) # Create test dataset colNames =['c1', 'c2', 'c3'] data = [ [1, 2.0, '3'], [4, 5.0, '6'] ] dataset = system.dataset.toDataSet(colNames, data) # New columnNames as List newColNames = ['c4', 'c5'] # New column data as list of lists newColData = [ ['X', 'Y'], [23, 42] ] dataOut = addColumns(dataset, newColNames, newColData) print dataOut.getColumnNames() print dataOut.getColumnTypes() ``` Output: ```auto [c1, c2, c3, c4, c5] [class java.lang.Integer, class java.lang.Double, class java.lang.String, class java.lang.String, class java.lang.Integer] ``` Resultant dataset: ```auto row | c1 c2 c3 c4 c5 --------------------- 0 | 1 2.0 3 X 23 1 | 4 5.0 6 Y 42 ``` ---
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [June 4, 2020, 8:46pm UTC](https://forum.inductiveautomation.com/t/convert-columntype-in-table-to-integer/9103/14 "2020-06-04T20:46:43Z")
Is there something you can’t do with datasets ? What I am trying to do is manipulated the data to add header and sub header. But since I have integers and double columns, I can’t really do it. So, the whole process for me goes like this - Get the data from table, Copy it to new dataset and change the columns to string Add row in the beginning as header, Add another row at 1 as Sub header, print that data ! Depending on the header requirement, set cell span ! Also, hide original Header ! Lol ! That’s the dumbest way to express the data.