none
Performing mathematical operations on the data of CSV file RRS feed

  • Question

  • Hi,

    I have read a CSV file from the directory and now i need to perform certain math operations for the data of CSV.

    I need to calculate: Cp as Val2-Val1/(6 std*dev) for each tests 

    Here is the code so far


    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;

    namespace Reading_CSV_file
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
                btOK.Enabled = false;
            }

            private void button1_Click(object sender, EventArgs e)
            {
                button1.Enabled = false;
                OpenFileDialog openFileDialog1 = new OpenFileDialog();
                openFileDialog1.Filter = ("comma seperated value | *.CSV");
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    string strfilename = openFileDialog1.FileName;
                    if (File.Exists(strfilename))
                    {
                        textBox1.Text = strfilename;
                    }
                    else
                    {

                        strfilename = "";
                    }
                    if (!string.IsNullOrWhiteSpace(strfilename))
                    {
                        textBox1.Text = strfilename;
                    }
                    else
                    {
                        MessageBox.Show("Please select a file");
                    }
                }
                else
                {
                    MessageBox.Show("Please select a file");
                }
            }

            private void textBox1_TextChanged(object sender, EventArgs e)
            {

                if (textBox1.Text == "")
                    btOK.Enabled = false;
                else
                    btOK.Enabled = true;

            }

            private void Form1_Load(object sender, EventArgs e)
            {

            }

            public static DataTable OpenCSV(string filePath)
            {
                DataTable dt = new DataTable();
                FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

                StreamReader sr = new StreamReader(fs);
                string strLine = "";
                string[] aryLine = null;
                string[] tableHead = null;
                int columnCount = 0;s
                bool IsFirst = true;
                while ((strLine = sr.ReadLine()) != null)
                {
                    if (IsFirst == true)
                    {
                        tableHead = strLine.Split(',');
                        IsFirst = false;
                        columnCount = tableHead.Length;
                        for (int i = 0; i < columnCount; i++)
                        {
                            DataColumn dc = new DataColumn(tableHead[i]);
                            dt.Columns.Add(dc);
                        }
                    }
                    else
                    {
                        aryLine = strLine.Split(',');
                        DataRow dr = dt.NewRow();
                        for (int j = 0; j < columnCount; j++)
                        {
                            dr[j] = aryLine[j];
                        }
                        dt.Rows.Add(dr);
                    }
                }
                if (aryLine != null && aryLine.Length > 0)
                {
                    dt.DefaultView.Sort = tableHead[0] + " " + "asc";
                }
                sr.Close();
                fs.Close();
                return dt;
            }

            DataTable dt;

            private void btOK_Click(object sender, EventArgs e)
            {
                dt = OpenCSV(textBox1.Text);
                foreach (DataRow dr in dt.Rows)
                {
                    // Get the first column
                    checkedListBox1.Items.Add(dr["Tests"]);
                }
            }

            class Value
            {
                string testname;
                string value1;
                string value2;
                public Value(string t, string v1, string v2)
                {
                    testname = t;
                    value1 = v1;
                    value2 = v2;
                }

                public string Testname { get { return testname; } }
                public string Value1 { get { return value1; } }
                public string Value2 { get { return value2; } }
            }


            private void btGetValues_Click(object sender, EventArgs e)
            {
                //int checkedTest = checkedListBox1.SelectedIndex;
                //MessageBox.Show(dt.Rows[checkedTest][1] + ", " + dt.Rows[checkedTest][2]);
                List<Value> list = new List<Value>();

                foreach (var i in checkedListBox1.CheckedItems)
                {
                    int index = checkedListBox1.Items.IndexOf(i);
                    Value v = new Value(dt.Rows[index][0].ToString(), dt.Rows[index][1].ToString(), dt.Rows[index][2].ToString());
                    list.Add(v);
                }

                foreach (Value i in list)
                {
                    MessageBox.Show(i.Testname + i.Value1 + i.Value2);
                }
            }

           
        }
    }


    Akshay

    Tuesday, April 2, 2019 9:33 AM

Answers

  • Hi

    You could download it from here.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by anonymous_3210 Wednesday, April 10, 2019 11:23 AM
    Wednesday, April 10, 2019 9:32 AM
    Moderator

All replies

  • You could create a DataColumn with an Expression if you know what columns are coming in, column order does not matter.

    https://docs.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=netframework-4.7.2#operators



    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Tuesday, April 2, 2019 1:54 PM
    Moderator
  • Hi,

    I need to perform operations on the columns val1 and val2.

    Assuming i want to perform Val2 - Val1 for each tests and display in console as for now.

    How do i do it

    Thanks in advance


    Akshay

    Wednesday, April 3, 2019 5:34 AM

  • I have read a CSV file from the directory and now i need to perform certain math operations for the data of CSV.


    I need to calculate: Cp as Val2-Val1/(6 std*dev) for each tests 


    This looks like a continuation of another open thread by the OP:

    calculating CP,CPK 
    https://social.msdn.microsoft.com/Forums/vstudio/en-US/893bcb38-c438-4ac5-9ca1-1392abd8ed1d/calculating-cpcpk?forum=csharpgeneral

    - Wayne

    Wednesday, April 3, 2019 6:20 AM
  • I have used the following code

    But unable to make it work

     private void btGetValues_Click(object sender, EventArgs e)
            {
               
                List<Value> list = new List<Value>();

                foreach (var i in checkedListBox1.CheckedItems)
                {
                    int index = checkedListBox1.Items.IndexOf(i);
                    Value v = new Value(dt.Rows[index][0].ToString(), dt.Rows[index][1].ToString(), dt.Rows[index][2].ToString());
                    list.Add(v);
                }

                foreach (Value i in list)
                {
                    foreach (DataRow row in dt.Rows)
                    {


                        double sum = 0;
                        double sumsq = 0;
                        foreach (DataRow Row in dt.Rows)
                        {
                            int delta = int.Parse(row["val2"].ToString()) - int.Parse(row["val1"].ToString());
                            sum += delta;
                            sumsq += delta * delta;
                        }


                        double mean = sum / dt.Rows.Count;
                        double stdev = Math.Sqrt(sumsq / dt.Rows.Count - mean * mean);


                        int usl = (int)row["Val2"];
                        int lsl = (int)row["Val1"];
                        double Cp = (usl - lsl) / (6 * stdev);
                        double Cpk = Math.Min(
                            (usl - mean) / (3 * stdev),
                            (mean - lsl) / (3 * stdev)
                            );
                        Console.WriteLine(
                            "{0}: {1},{2} {3} {4}",
                            row["id"], usl, lsl, Cp, Cpk);

                    }


    Akshay

    Wednesday, April 3, 2019 6:52 AM
  • Hi

    Thank you for posting here.

    Based on your description, you want to perform mathematical operations on the data of CSV file.

    You could try the following code, the bold code is modified.

    private void btGetValues_Click(object sender, EventArgs e)
            {
                List<Value> list = new List<Value>();
    
                foreach (var i in checkedListBox1.CheckedItems)
                {
                    int index = checkedListBox1.Items.IndexOf(i);
                    Value v = new Value(dt.Rows[index][0].ToString(), dt.Rows[index][1].ToString(), dt.Rows[index][2].ToString());
                    list.Add(v);
                }
    
                foreach (Value i in list)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        if (row["Tests"].ToString() == i.Testname)
                        {
    
                            double sum = 0;
                            double sumsq = 0;
                            foreach (DataRow Row in dt.Rows)
                            {
                                int delta = int.Parse(row["val2"].ToString()) - int.Parse(row["val1"].ToString());
                                sum += delta;
                                sumsq += delta * delta;
                            }
    
    
                            double mean = sum / dt.Rows.Count;
                            double stdev = Math.Sqrt(sumsq / dt.Rows.Count - mean * mean);
    
    
                            int usl = Convert.ToInt32(row["Val2"]);
                            int lsl = Convert.ToInt32(row["Val1"]);
                            double Cp = (usl - lsl) / (6 * stdev);
                            double Cpk = Math.Min(
                                (usl - mean) / (3 * stdev),
                                (mean - lsl) / (3 * stdev)
                                );
                            Console.WriteLine(
                                "{0}: {1},{2} {3} {4}",
                                row["Tests"], usl, lsl, Cp, Cpk);
                        }
    
                    }
                }
            }

    Result:

    Best regards,

    Jack



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, April 3, 2019 8:05 AM
    Moderator
  • I tried the above code. But I'm unable to see anything in the console.

    I'm getting this on the console window:

    "The program '[14180] Reading CSV file.exe' has exited with code -1 (0xffffffff)."


    Akshay


    Wednesday, April 3, 2019 9:55 AM
  • Hi,

    I need to perform operations on the columns val1 and val2.

    Assuming i want to perform Val2 - Val1 for each tests and display in console as for now.

    How do i do it

    Thanks in advance


    Akshay

    Mocked up example

    var dt = new DataTable();
    dt.Columns.Add(new DataColumn() {ColumnName = "Tests", DataType = typeof(string)});
    dt.Columns.Add(new DataColumn() { ColumnName = "val1", DataType = typeof(int) });
    dt.Columns.Add(new DataColumn() { ColumnName = "val2", DataType = typeof(int) });
    dt.Columns.Add(new DataColumn() { ColumnName = "result", DataType = typeof(int),
        Expression = "val2 - val1"});
    
    dt.Rows.Add("test1", 1, 4);
    dt.Rows.Add("test2", 2, 5);
    dt.Rows.Add("test4", 3, 6);
    
    foreach (DataRow row in dt.Rows)
    {
        Console.WriteLine($"Test name {row.Field<string>("Tests")}");
        Console.WriteLine($"  {string.Join(",", row.ItemArray)}");
    }

    Results to the IDE console window

    Test name test1
      test1,1,4,3
    Test name test2
      test2,2,5,3
    Test name test4
      test4,3,6,3


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, April 3, 2019 11:16 AM
    Moderator
  • Hi,

    I'm unable to see the results in the console. Could you please tell me why


    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;

    namespace Reading_CSV_file
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
                btOK.Enabled = false;
            }

            private void button1_Click(object sender, EventArgs e)
            {
                btBROWSE.Enabled = false;
                OpenFileDialog openFileDialog1 = new OpenFileDialog();
                openFileDialog1.Filter = ("comma seperated value | *.CSV");
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    string strfilename = openFileDialog1.FileName;
                    if (File.Exists(strfilename))
                    {
                        textBox1.Text = strfilename;
                    }
                    else
                    {

                        strfilename = "";
                    }
                    if (!string.IsNullOrWhiteSpace(strfilename))
                    {
                        textBox1.Text = strfilename;
                    }
                    else
                    {
                        MessageBox.Show("Please select a file");
                    }
                }
                else
                {
                    MessageBox.Show("Please select a file");
                }
            }

            private void textBox1_TextChanged(object sender, EventArgs e)
            {

                if (textBox1.Text == "")
                    btOK.Enabled = false;
                else
                    btOK.Enabled = true;

            }

            private void Form1_Load(object sender, EventArgs e)
            {

            }

            public static DataTable OpenCSV(string filePath)
            {
                DataTable dt = new DataTable();
                FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

                StreamReader sr = new StreamReader(fs);
                string strLine = "";
                string[] aryLine = null;
                string[] tableHead = null;
                int columnCount = 0;
                bool IsFirst = true;
                while ((strLine = sr.ReadLine()) != null)
                {
                    if (IsFirst == true)
                    {
                        tableHead = strLine.Split(',');
                        IsFirst = false;
                        columnCount = tableHead.Length;
                        for (int i = 0; i < columnCount; i++)
                        {
                            DataColumn dc = new DataColumn(tableHead[i]);
                            dt.Columns.Add(dc);
                        }
                    }
                    else
                    {
                        aryLine = strLine.Split(',');
                        DataRow dr = dt.NewRow();
                        for (int j = 0; j < columnCount; j++)
                        {
                            dr[j] = aryLine[j];
                        }
                        dt.Rows.Add(dr);
                    }
                }
                if (aryLine != null && aryLine.Length > 0)
                {
                    dt.DefaultView.Sort = tableHead[0] + " " + "asc";
                }
                sr.Close();
                fs.Close();
                return dt;
            }

            DataTable dt;

            public int Val1 { get; private set; }
            public int Val2 { get; private set; }
            public int Cp { get; private set; }

            private void btOK_Click(object sender, EventArgs e)
            {
                dt = OpenCSV(textBox1.Text);
                foreach (DataRow dr in dt.Rows)
                {
                    // Get the first column
                    checkedListBox1.Items.Add(dr["Tests"]);
                }
            }

            class Value
            {
                string testname;
                string value1;
                string value2;


                private string v1;
                private string v2;
                private string v3;

                public Value(string v1, string v2, string v3)
                {
                    this.v1 = v1;
                    this.v2 = v2;
                    this.v3 = v3;
                }

                public Value(string t, string v1, string v2, string cp)
                {
                    testname = t;
                    value1 = v1;
                    value2 = v2;

                }

                public string Testname { get { return testname; } }
                public string Value1 { get { return value1; } }
                public string Value2 { get { return value2; } }

            }


            private void btGetValues_Click(object sender, EventArgs e)
            {
                {
                    List<Value> list = new List<Value>();

                    foreach (var i in checkedListBox1.CheckedItems)
                    {
                        int index = checkedListBox1.Items.IndexOf(i);
                        Value v = new Value(dt.Rows[index][0].ToString(), dt.Rows[index][1].ToString(), dt.Rows[index][2].ToString());
                        list.Add(v);
                    }

                    foreach (Value i in list)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            if (row["Tests"].ToString() == i.Testname)
                            {

                                double sum = 0;
                                double sumsq = 0;
                                foreach (DataRow Row in dt.Rows)
                                {
                                    int delta = int.Parse(row["val2"].ToString()) - int.Parse(row["val1"].ToString());
                                    sum += delta;
                                    sumsq += delta * delta;
                                }


                                double mean = sum / dt.Rows.Count;
                                double stdev = Math.Sqrt(sumsq / dt.Rows.Count - mean * mean);


                                int usl = Convert.ToInt32(row["Val2"]);
                                int lsl = Convert.ToInt32(row["Val1"]);
                                double Cp = (usl - lsl) / (6 * stdev);
                                double Cpk = Math.Min(
                                    (usl - mean) / (3 * stdev),
                                    (mean - lsl) / (3 * stdev)
                                    );
                                Console.WriteLine(
                                    "{0}: {1},{2} {3} {4}",
                                    row["Tests"], usl, lsl, Cp, Cpk);
                            }

                        }

                    
                    }
                }
            }
        }
    }


    Akshay

    Wednesday, April 3, 2019 11:47 AM
  • Hi,

    That worked! Thank you.

    But i'm getting the same value for Cp, Cpk as 8,-8 irrespective of the val1 and val2


    Akshay

    Thursday, April 4, 2019 6:43 AM
  • Hi,

    Can i do the same operation with the code that you have given?

    using Microsoft.VisualBasic.FileIO;
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.OleDb;
    using System.Drawing;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;

    namespace _1.CSV
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            DataTable dt = new DataTable();
            private void button1_Click(object sender, EventArgs e)
            {

                OpenFileDialog openFileDialog1 = new OpenFileDialog();
                openFileDialog1.Filter = ("comma seperated value | *.CSV");
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    string strfilename = openFileDialog1.FileName;
                    if (File.Exists(strfilename))
                    {
                        textBox1.Text = strfilename;
                    }
                    else
                    {

                        strfilename = "";
                    }
                    if (!string.IsNullOrWhiteSpace(strfilename))
                    {
                        textBox1.Text = strfilename;
                    }
                    else
                    {
                        MessageBox.Show("Please select a file");
                    }
                }
                else
                {
                    MessageBox.Show("Please select a file");
                }
               
            }

            private void button2_Click(object sender, EventArgs e)
            {
                dt= GetDataTabletFromCSVFile(textBox1.Text);

                foreach (DataRow row in dt.Rows)
                {
                    if (Convert.ToDateTime(row["Date"])<= Convert.ToDateTime(dateTimePicker2.Text)&& Convert.ToDateTime(row["Date"]) >= Convert.ToDateTime(dateTimePicker1.Text))
                    {
                        Console.WriteLine(row["Test1"].ToString() + "    " + row["USL"].ToString() + "      " + row["LSL"].ToString());
                        
                    }
                }
            }
            private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
            {
                DataTable csvData = new DataTable();
                try
                {
                    using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                    {
                        csvReader.SetDelimiters(new string[] { "," });
                        csvReader.HasFieldsEnclosedInQuotes = true;
                        string[] colFields = csvReader.ReadFields();
                        foreach (string column in colFields)
                        {
                            if (column == "Date" || column == "Test1" || column == "USL" || column == "LSL")
                            {
                                DataColumn datecolumn = new DataColumn(column);
                                datecolumn.AllowDBNull = true;
                                csvData.Columns.Add(datecolumn);
                            }
                        }
                        while (!csvReader.EndOfData)
                        {
                            string[] fieldData = csvReader.ReadFields();
                            for (int i = 0; i < fieldData.Length; i++)
                            {
                                if (fieldData[i] == "")
                                {
                                    fieldData[i] = null;
                                }
                            }
                            csvData.Rows.Add(fieldData);
                        }
                    }

                }
                catch (Exception)
                {
                    throw;
                }
                return csvData;

            }
        }
    }

    where mean = sum of specific tests between the dates / N 


    Akshay


    Tuesday, April 9, 2019 9:19 AM
  • Hi

    You could download it from here.

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 9, 2019 9:32 AM
    Moderator
  • Hi,

    This was the older CSV Format. 

    I needed for the below format. Which you had sent me the code between two specific dates.

    where mean is the sum of Test1/N between those dates


    Akshay

    Tuesday, April 9, 2019 9:57 AM
  • Hi,

    I need to perform those math operations for the tests between those specific dates on Test1,USL,LSL


    Akshay

    Wednesday, April 10, 2019 8:50 AM
  • Hi

    You could download it from here.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by anonymous_3210 Wednesday, April 10, 2019 11:23 AM
    Wednesday, April 10, 2019 9:32 AM
    Moderator
  • Hi,

    Thank you so much .

    That's exactly what I wanted to know


    Akshay

    Wednesday, April 10, 2019 11:24 AM
  • You have the code to read the CSV file.  You have been given the code to compute Cp and Cpk from your data set.  You have everything you need.  WE ARE NOT GOING TO WRITE THE APP FOR YOU.  If you need someone to write it for you, you will have to contract with and pay a programmer.

    And your data set above is WRONG.  USL must always be greater than LSL.


    Tim Roberts | Driver MVP Emeritus | Providenza &amp; Boekelheide, Inc.

    Wednesday, April 10, 2019 8:59 PM