none
Reading data from CSV RRS feed

  • Question

  • Hi,

    I have the following format of CSV file.

    I need to fetch date, and then read the Test1, USL and LSL column

    Can anyone please tell me

    Thanks in advance


    Akshay


    Friday, April 5, 2019 8:58 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.


    Tuesday, April 9, 2019 6:30 AM
    Moderator

All replies

  • An usual method to parse csv files is with TextFieldParser
    Friday, April 5, 2019 11:27 AM
  • How can i put those test1 values in an array after parsing

    Akshay

    Friday, April 5, 2019 11:44 AM
  • How can i put those test1 values in an array after parsing

    For example, with a test file 

    "04-Apr-19", 7.25, 7.25, 8
    "",  7.33, 7.25, 8
    "",  7.31, 7.25, 8

    Something like this :

    using (TextFieldParser tfp = new TextFieldParser("e:\\test.csv"))
    {
        List<double> listCol1 = new List<double>();
        tfp.HasFieldsEnclosedInQuotes = true;
        tfp.Delimiters = new string[] { "," };
        while (true)
        {
            string[] line = tfp.ReadFields();
            if (line == null)
                break;
            listCol1.Add(Convert.ToDouble(line[1], CultureInfo.InvariantCulture));                      
        }
        var array = listCol1.ToArray();
        for (int i = 0; i < listCol1.Count; i++)
        {
            Console.WriteLine("array({0}) = {1}", i, array[i]);
        }                   
    }

    The result :

    array(0) = 7,25
    array(1) = 7,33
    array(2) = 7,31

    • Proposed as answer by phil chelis Sunday, April 7, 2019 9:16 AM
    Friday, April 5, 2019 12:17 PM
  • Another solution is to query the entire CSV file into a dataTable. Then you can query the cells which ever you are interested:

       public DataTable GetDataTabletFromCSVFile(string csv_file_path, string charSep)
            {
                DataTable csvData = new DataTable();
                try
                {
                    using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                    {
                        csvReader.SetDelimiters(new string[] {charSep.ToString()   });
                        csvReader.HasFieldsEnclosedInQuotes = true;
                        string[] colFields = csvReader.ReadFields();
                        int numColumns = 0;
                        foreach (string column in colFields)
                        {
                            DataColumn datcolumn = new DataColumn(column);
                            datcolumn.AllowDBNull = true;
                            csvData.Columns.Add(datcolumn);
                            numColumns = numColumns + 1;
                        }
                        while (!csvReader.EndOfData)
                        {
                            string[] fieldData = csvReader.ReadFields();
                            //Making empty value as null
                            for (int i = 0; i < fieldData.Length; i++)
                            {
                                if (fieldData[i] == "")
                                {
                                    fieldData[i] = null;
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                }
                return csvData;
           }
        }

    Friday, April 5, 2019 1:24 PM
  • I would look into using FileHelpers. Very easy to use.

    FileHelpers

    You basically need to define the shape of your data - the fields in your individual line in the CSV - by means of a public class and point the FileHelpers engine at a file.


    william xifaras

    Friday, April 5, 2019 3:42 PM
  • Another solution is to query the entire CSV file into a dataTable. 

    FWIW, the OP was already given code to do this in an earlier thread days/weeks
    prior to starting this thread:

    Reading CSV file
    https://social.msdn.microsoft.com/Forums/vstudio/en-US/a32546f5-46f1-444c-a067-9532893d8b71/reading-csv-file?forum=winforms#6cfa7168-681b-4ffa-9c54-7e9eeb98ce3c

    That thread started in the Visual C# forum but was moved by a moderator to the
    Windows Forms General forum.

    - Wayne

    Friday, April 5, 2019 7:47 PM
  • Hi,

    Basically i need to read all those columns and perform some math operations on USL and LSL columns



    Akshay

    Sunday, April 7, 2019 8:06 AM

  • Basically i need to read all those columns and perform some math operations on USL and LSL columns


    Isn't this the same question that you're now pursuing in another thread as well?

    how to process items in checkedlistbox 
    https://social.msdn.microsoft.com/Forums/vstudio/en-US/2768df94-9059-40cb-a885-c617dcdd9634/how-to-process-items-in-checkedlistbox?forum=winforms

    You shouldn't be asking the exact same question in multiple threads. It wastes
    the time and energy of others who create replies and examples, unaware that
    someone else has already done the same in another thread.

    - Wayne

    Sunday, April 7, 2019 11:06 PM
  • Hi

    Thank you for posting here.

    Based on your description, you want to read the Test1, USL and LSL column with date.

    You could try the following code.

    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)
            {
                string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        string sheetName = "Sheet1";
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                        }
                    }
    
                }
                foreach (DataRow row in dt.Rows)
                {
                    if (Convert.ToDateTime(row["Date"]).ToShortDateString() == textBox2.Text)
                    {
                        Console.WriteLine(row["Test1"].ToString() + "    " + row["USL"].ToString() + "      " + row["LSL"].ToString());
                        break;
                    }
                }
            }
    

    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.

    Monday, April 8, 2019 3:12 AM
    Moderator
  • Hello,

    I'm getting the following error while reading the data.

    'External table is not in the expected format.'



    Akshay

    Monday, April 8, 2019 5:40 AM
  • Hi

    Thanks for the feedback.

    I want to confirm where you got the error. I have tested it, it works well.

    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.

    Monday, April 8, 2019 5:43 AM
    Moderator
  • Hello,

    I got the error after i press the Read CSV button


    Akshay


    Monday, April 8, 2019 5:51 AM
  • Hi 

    I could not reproduce your problem. You could download it from here to check it.

    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.

    Monday, April 8, 2019 6:12 AM
    Moderator
  • Hi,

    There is no code in your file.

    I guess i'm having trouble with my Excel 2010.

    Here is the link of my code

    https://1drv.ms/u/s!Av5Z02PBCxYccJwfSQJQmVUKhjw

    Thank you


    Akshay

    Monday, April 8, 2019 6:37 AM
  • Hi

    I have tested your code, I still could not reproduce your problem.

    I suggest that you could try to create a new csv file to test it.

    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.

    Monday, April 8, 2019 6:45 AM
    Moderator
  • Hello,

    I tried but its still the same. I guess the issue is with the OleDB.

    Is there any other normal way to parse it without OleDB


    Akshay

    Monday, April 8, 2019 6:52 AM
  • After reading those data I would like to do this math operation on USL and LSL

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

                                double sum = 0;
                                double sumsq = 0;
                                foreach (DataRow Row in dt.Rows)
                                {
                                    int delta = int.Parse(row["USL"].ToString()) - int.Parse(row["LSL"].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["USL"]);
                                int lsl = Convert.ToInt32(row["LSL"]);
                                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"].ToString(), usl, lsl, Cp, Cpk);


    Akshay

    Monday, April 8, 2019 7:07 AM
  • Hi

    Based on my research, you could use TextFieldParser class.

    Code:

      private void button2_Click(object sender, EventArgs e)
            {
                dt= GetDataTabletFromCSVFile(textBox1.Text);
    
                foreach (DataRow row in dt.Rows)
                {
                    if (Convert.ToDateTime(row["Date"]).ToShortDateString() == textBox2.Text)
                    {
                        Console.WriteLine(row["Test1"].ToString() + "    " + row["USL"].ToString() + "      " + row["LSL"].ToString());
                        break;
                    }
                }
            }
            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;
            }

    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.

    Monday, April 8, 2019 7:51 AM
    Moderator
  • Based on my research, you could use TextFieldParser class.

    This has been answered 3 days ago...
    Monday, April 8, 2019 8:54 AM
  • Hi,

    Could you please send me this code?


    Akshay

    Monday, April 8, 2019 9:21 AM
  • Hi

    You could download the project from the following link.

    https://onedrive.live.com/?authkey=%21AJY7lS8cxnVRX1I&cid=967DA31B4EC774CB&id=967DA31B4EC774CB%21150&parId=root&action=locate

    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.

    Monday, April 8, 2019 9:37 AM
    Moderator
  • Hi,

    It doesn't have the full code maybe . can you please check


    Akshay

    Monday, April 8, 2019 9:43 AM
  • Hi

    I have updated my link. You could refer to the new link.

    https://onedrive.live.com/?authkey=%21AMG7kc8eX9x8ZY8&cid=967DA31B4EC774CB&id=967DA31B4EC774CB%21252&parId=root&action=locate

    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.

    Monday, April 8, 2019 10:16 AM
    Moderator
  • Thank you,

    Its working


    Akshay

    Monday, April 8, 2019 10:40 AM
  • Hi,

    We cannot read multiple columns from this right?

    Assuming we have the same date for first two columns


    Akshay

    Monday, April 8, 2019 11:44 AM
  • Hi

    If you want to read multiple columns, you could try to delete break in the code.

    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 1:26 AM
    Moderator
  • Hi,

    Is it possible to get the values of Test1 between two specific dates i enter?

    Thanks in advance


    Akshay

    Tuesday, April 9, 2019 5:03 AM
  • Hi

    >>Is it possible to get the values of Test1 between two specific dates i enter?

    I want to know if you want to get one column of data or multiple columns of data.

    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 5:13 AM
    Moderator
  • Hi,

    I need to get multiple columns of Test1 data between two entered date range

    Thanks


    Akshay

    Tuesday, April 9, 2019 5:53 AM
  • Hi

    You could add two dateTimePicker controls and try the following change.

    Change:

    if (Convert.ToDateTime(row["Date"]).ToShortDateString() == textBox2.Text)
                    {
                        Console.WriteLine(row["Test1"].ToString() + "    " + row["USL"].ToString() + "      " + row["LSL"].ToString());
                        break;
                    }

    Into

    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());
                        
                    }

    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 6:11 AM
    Moderator
  • Hi,

    Could you please post the code 


    Akshay

    Tuesday, April 9, 2019 6:25 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.


    Tuesday, April 9, 2019 6:30 AM
    Moderator
  • Thank you so much.

    That works perfect


    Akshay

    Tuesday, April 9, 2019 6:40 AM
  • Hi

    It seems that your problem has been solved. If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    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 6:41 AM
    Moderator