Changing the datatype of strings
-
I have a large datatable which I have filled with parsed text data using C#, this table has one column containing alphabetic codes, all the other columns are numbers from 0 through to 10,000. As a novice i was wondering how i convert the numeric columns to a different datatype such as "int" so i can then perform calculations, such as SUM on each column.However i need to leave the one colomn with the alphabetics as strings. The datatable in question has approx 300 columns. Some guidance on how to approach this would be greatly appreciated. Thanks in advance:confused:
-
I have a large datatable which I have filled with parsed text data using C#, this table has one column containing alphabetic codes, all the other columns are numbers from 0 through to 10,000. As a novice i was wondering how i convert the numeric columns to a different datatype such as "int" so i can then perform calculations, such as SUM on each column.However i need to leave the one colomn with the alphabetics as strings. The datatable in question has approx 300 columns. Some guidance on how to approach this would be greatly appreciated. Thanks in advance:confused:
If your column is numbers, you should make it a numeric type. To convert a string to a number in C#, use int.TryParse. int i; if (int.TryParse("3244", out i)) { // TryParse returns true if the string could be converted. }
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
If your column is numbers, you should make it a numeric type. To convert a string to a number in C#, use int.TryParse. int i; if (int.TryParse("3244", out i)) { // TryParse returns true if the string could be converted. }
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
Thanks christian Excuse my lack of experience with this Christian but how do I apply this to all the values in my datatable. I also forgot to say that all of the columns have a header row that has to remain as a string, so i guess what i have to do is cycle through all the individual cells in the datatable and convert the ones that can be converted with TryParse, is this correct ? and if so how would i approach this. thanks again:confused:
-
Thanks christian Excuse my lack of experience with this Christian but how do I apply this to all the values in my datatable. I also forgot to say that all of the columns have a header row that has to remain as a string, so i guess what i have to do is cycle through all the individual cells in the datatable and convert the ones that can be converted with TryParse, is this correct ? and if so how would i approach this. thanks again:confused:
minnie mouse, To convert every string in your database to an int you must iterate thru every row and change the value in each column to a int. For example if you have grabbed all data from your database and have filled your dataset with this data. Use the code below check for syntax errors. But this should get you going. DataTable table = MyDataSet.Tables[0]; foreach(DataRow row in table.Rows) { foreach(DataColumn column in table.Columns) { int newIntValue = int.Parse(row[column].toString()); row[column] = newIntValue; } } In your database you must be sure to change the dataType of the column so that when you update your database with the dataset via dataAdapter you get no dataType errors.
-
Thanks christian Excuse my lack of experience with this Christian but how do I apply this to all the values in my datatable. I also forgot to say that all of the columns have a header row that has to remain as a string, so i guess what i have to do is cycle through all the individual cells in the datatable and convert the ones that can be converted with TryParse, is this correct ? and if so how would i approach this. thanks again:confused:
minnie mouse wrote:
Excuse my lack of experience with this Christian but how do I apply this to all the values in my datatable.
Your core problem is that your database design is wrong. As it stands, you need to create a new column in your datatable, and copy the values across using SQL. Then delete the old table. I'm not sure that you can just change the type of a column.
minnie mouse wrote:
I also forgot to say that all of the columns have a header row that has to remain as a string,
Is this a database we're talking about ? A header is obvioulsy a string. It's the name of the column.
minnie mouse wrote:
so i guess what i have to do is cycle through all the individual cells in the datatable and convert the ones that can be converted with TryParse, is this correct ?
To do this in memory, and keep your database in this less than useful format, yes. I think someone else gave you some sample code.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
I have a large datatable which I have filled with parsed text data using C#, this table has one column containing alphabetic codes, all the other columns are numbers from 0 through to 10,000. As a novice i was wondering how i convert the numeric columns to a different datatype such as "int" so i can then perform calculations, such as SUM on each column.However i need to leave the one colomn with the alphabetics as strings. The datatable in question has approx 300 columns. Some guidance on how to approach this would be greatly appreciated. Thanks in advance:confused:
-
minnie mouse wrote:
Excuse my lack of experience with this Christian but how do I apply this to all the values in my datatable.
Your core problem is that your database design is wrong. As it stands, you need to create a new column in your datatable, and copy the values across using SQL. Then delete the old table. I'm not sure that you can just change the type of a column.
minnie mouse wrote:
I also forgot to say that all of the columns have a header row that has to remain as a string,
Is this a database we're talking about ? A header is obvioulsy a string. It's the name of the column.
minnie mouse wrote:
so i guess what i have to do is cycle through all the individual cells in the datatable and convert the ones that can be converted with TryParse, is this correct ?
To do this in memory, and keep your database in this less than useful format, yes. I think someone else gave you some sample code.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
Christian, Thanks for your help with this, all the info I have read so far doesnt seem to apply to the situation I have. Which is the need to write a data import programme, therefore the approach most of the educational material i have seen of firstly design the database, then etc , etc cant be applied as i'm starting with nothing but a large amount of csv data. Ive got to the point where i can succesfully store all this as string data in a SQL database tables. I now need to perform some calculations on the majority of the columns.These columns contain numbers but have names as the first row. I wasnt sure if it was easier to conert to numeric datatype first or try to act on the stored strings in the table with maybe a stored procedure? Please excuse my lack of understanding of this, i am looking for experienced guidance.
-
Christian, Thanks for your help with this, all the info I have read so far doesnt seem to apply to the situation I have. Which is the need to write a data import programme, therefore the approach most of the educational material i have seen of firstly design the database, then etc , etc cant be applied as i'm starting with nothing but a large amount of csv data. Ive got to the point where i can succesfully store all this as string data in a SQL database tables. I now need to perform some calculations on the majority of the columns.These columns contain numbers but have names as the first row. I wasnt sure if it was easier to conert to numeric datatype first or try to act on the stored strings in the table with maybe a stored procedure? Please excuse my lack of understanding of this, i am looking for experienced guidance.
If you're starting with a CSV, then rewrite your import code to create numeric columns for numeric data. Otherwise, you'll always be held back by poor database design.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
If you're starting with a CSV, then rewrite your import code to create numeric columns for numeric data. Otherwise, you'll always be held back by poor database design.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
Thanks christian I will now do that. I did wonder at the time if I was going the right way. Stay tuned for future developments ! Curvoisier concerto is a personal favourite of mine.;)
-
minnie mouse, To convert every string in your database to an int you must iterate thru every row and change the value in each column to a int. For example if you have grabbed all data from your database and have filled your dataset with this data. Use the code below check for syntax errors. But this should get you going. DataTable table = MyDataSet.Tables[0]; foreach(DataRow row in table.Rows) { foreach(DataColumn column in table.Columns) { int newIntValue = int.Parse(row[column].toString()); row[column] = newIntValue; } } In your database you must be sure to change the dataType of the column so that when you update your database with the dataset via dataAdapter you get no dataType errors.
Thanks for this help, please see Christians comments Much clearer now.:)