locked
read csv change engineering format to dobule RRS feed

  • Question

  • Hi list,

    I am trying to read data from a csv file using the following code. I would like to edit them as reading and loading data into datatable. For example if in a cell data is 2E3, I would like to first change it into 2000 then store it. Any idea?

    DataTable mydt = new DataTable();
                StreamReader aReader = new StreamReader(fn);
                string sHeaderLine = aReader.ReadLine();
                string[] sComma = sHeaderLine.Split(',');

    while (aReader.EndOfStream == false)
                {
                    string myLine = aReader.ReadLine();
                    sComma = myLine.Split(',');
                    DataRow aNewRow = mydt.NewRow();
                    mydt.Rows.Add(aNewRow);
                    for (int i = 0; i < sComma.Length; i++)

                     //to add a condition here to change values from engineering format to double, or leave them unchanged as they are

                         oNewRow[i] = sComma[i];
                }


    Monday, July 16, 2012 8:48 PM

Answers

  • You can use:

      oNewRow[i] = Convert.ToDouble(sComma[i]);

    That being said, you may want to consider using something like TextFieldParser to parse your CSV file directly instead of trying to do the string splitting yourself.  This is far safer, as it handles all of the CSV options (like quotes) properly.


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by azolfaghari Wednesday, July 18, 2012 3:43 PM
    Monday, July 16, 2012 8:50 PM
  • You can use code like this

                string mynumber = "1.02E24";
                Boolean converted;
                double results;
                converted = Double.TryParse(mynumber, out results);

                mynumber = "1.02";
                converted = Double.TryParse(mynumber, out results);

    You only need to convert columns where you need precise accuracy or columns that qare in scientific notation.


    jdweng

    • Marked as answer by azolfaghari Wednesday, July 18, 2012 3:43 PM
    Tuesday, July 17, 2012 11:08 AM
  • I am just after a condition, how can I distinguish "test" from "1.02E24"? There must be something?

    You could just use double.TryParse, as suggested by Joel.  If it returns true, the value was originally a number, and can be converted into a double.

    // Instead of this:
    // oNewRow[i] = Convert.ToDouble(sComma[i]);
    
    // Use:
    double temp;
    if (double.TryParse(sComma[i], out temp))
       oNewRow[i] = temp;
    else
       oNewRow[i] = sComma[i]; // Otherwise, use string


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by azolfaghari Wednesday, July 18, 2012 3:42 PM
    Tuesday, July 17, 2012 4:01 PM
  • I am just after a condition, how can I distinguish "test" from "1.02E24"? There must be something?

    Hi Azolfaghari,

    Welcome to the MSDN Forum.

    Based on my understanding, you don't need a condition. Because you are reading a CSV file, so the data in that file should be formatted, I mean the data should be written in order. As Joel said, you can just parse the specific column, rather than check it is "engineering format" or not, if you failed to parse it, it is not your fault, the data is wrong. 

    If you want to say both the following two lines data are vaild:

    123,abc,1.02E24,25,abc

    123,abc,test,25,abc

    You can use regular expression to check it:

    And it can be something like this: 

    ^\d+(?:\.\d+)?E\d+

    But I don't think this make sense, because you stored both numbers and string in the same column.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by azolfaghari Wednesday, July 18, 2012 3:42 PM
    Wednesday, July 18, 2012 3:33 AM

All replies

  • You can use:

      oNewRow[i] = Convert.ToDouble(sComma[i]);

    That being said, you may want to consider using something like TextFieldParser to parse your CSV file directly instead of trying to do the string splitting yourself.  This is far safer, as it handles all of the CSV options (like quotes) properly.


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by azolfaghari Wednesday, July 18, 2012 3:43 PM
    Monday, July 16, 2012 8:50 PM
  • I knew that, I am after a condition to see when it is in engineering format, then convert it into double. Because if it is string, we do not need to convert it.
    Monday, July 16, 2012 10:43 PM
  • You can use code like this

                string mynumber = "1.02E24";
                Boolean converted;
                double results;
                converted = Double.TryParse(mynumber, out results);

                mynumber = "1.02";
                converted = Double.TryParse(mynumber, out results);

    You only need to convert columns where you need precise accuracy or columns that qare in scientific notation.


    jdweng

    • Marked as answer by azolfaghari Wednesday, July 18, 2012 3:43 PM
    Tuesday, July 17, 2012 11:08 AM
  • I am just after a condition, how can I distinguish "test" from "1.02E24"? There must be something?

    Tuesday, July 17, 2012 2:20 PM
  • I am just after a condition, how can I distinguish "test" from "1.02E24"? There must be something?

    You could just use double.TryParse, as suggested by Joel.  If it returns true, the value was originally a number, and can be converted into a double.

    // Instead of this:
    // oNewRow[i] = Convert.ToDouble(sComma[i]);
    
    // Use:
    double temp;
    if (double.TryParse(sComma[i], out temp))
       oNewRow[i] = temp;
    else
       oNewRow[i] = sComma[i]; // Otherwise, use string


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by azolfaghari Wednesday, July 18, 2012 3:42 PM
    Tuesday, July 17, 2012 4:01 PM
  • I am just after a condition, how can I distinguish "test" from "1.02E24"? There must be something?

    Hi Azolfaghari,

    Welcome to the MSDN Forum.

    Based on my understanding, you don't need a condition. Because you are reading a CSV file, so the data in that file should be formatted, I mean the data should be written in order. As Joel said, you can just parse the specific column, rather than check it is "engineering format" or not, if you failed to parse it, it is not your fault, the data is wrong. 

    If you want to say both the following two lines data are vaild:

    123,abc,1.02E24,25,abc

    123,abc,test,25,abc

    You can use regular expression to check it:

    And it can be something like this: 

    ^\d+(?:\.\d+)?E\d+

    But I don't think this make sense, because you stored both numbers and string in the same column.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by azolfaghari Wednesday, July 18, 2012 3:42 PM
    Wednesday, July 18, 2012 3:33 AM