none
Find text in column of a .csv file. Add/modify text if does not exist. RRS feed

  • Question

  • Hi all,

    I am using C#.

    I have a .csv file formatted as below.

    COMPONENT, FREQUENCY

    C456,             45

    V345,            34

    etc.

    I want to search, for example, for C456.

    If C456 does NOT exist, then add it into COMPONENT column and add 1 into the FREQUENCY column.

    If C456 DOES exist, simply increment the number in the FREQUENCY column.

    Regards

    Francesco

    Friday, November 29, 2019 1:12 PM

Answers

  • Hi Francesco2017,

    Thank you for posting here.

    For your question, you want to add or modify the rows in the csv file depending on whether the text exists before.

    Here is a code example.

       class Program
        {
            static DataTable dt = GetDataTable();
            static void Main(string[] args)
            {
                string[] datas = new string[] { "c456","b123","a456","C123","V345"};
    
                foreach (DataColumn item in dt.Columns)
                {
                    item.ReadOnly = false;
                }
                for (int i = 0; i < datas.Length; i++)
                {
                    IsExists(datas[i]);
                }
                InsertToCsv(dt);
            }
            static void IsExists(string data)
            {
                int count = 0;
                foreach (DataRow row in dt.Rows)
                {
                    var item = row["COMPONENT"].ToString();
                    if (item.ToLower().Equals(data.ToLower()))
                    {
                        int nums = int.Parse(row["FREQUENCY"].ToString()) + 1;
                        row["FREQUENCY"] = nums;
                        count++;
                    }
                }
                if (count==0)
                {
                    dt.Rows.Add(data, 1);
                }
            }
            static DataTable GetDataTable()
            {
                using (var reader = new StreamReader(@"d:\test\test.csv"))
                using (var csv = new CsvReader(reader))
                {
                    using (var dr = new CsvDataReader(csv))
                    {
                        var dt = new DataTable();
                        dt.Load(dr);
                        return dt;
                    }
                }
            }
            static void InsertToCsv(DataTable dt)
            {
                using (var textWriter = File.CreateText(@"D:\test\test.csv"))
                using (var csv = new CsvWriter(textWriter))
                {
                    // Write columns
                    foreach (DataColumn column in dt.Columns)
                    {
                        csv.WriteField(column.ColumnName);
                    }
                    csv.NextRecord();
    
                    // Write row values
                    foreach (DataRow row in dt.Rows)
                    {
                        for (var i = 0; i < dt.Columns.Count; i++)
                        {
                            csv.WriteField(row[i]);
                        }
                        csv.NextRecord();
                    }
                }
            }
        }

    Before:

    After:

    Hope this could be helpful.

    Best Regards,

    Timon


    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, December 3, 2019 7:14 AM

All replies

  • What have you done to solve the problem yourself? You can get much better answers if you can ask specific questions.

    You need to load the data into a collection and use that to search and update then save it back when done.

    You can open the file as a text file and split each line using String.Split and put each line into a collection. Be sure to not put the first row into the collection. A Dictionary<TKey,TValue> would be a good collection to use; make the component the key and the frequency the value. When you write the data out be sure to write the first line (the header) first.



    Sam Hobbs
    SimpleSamples.Info


    Saturday, November 30, 2019 6:24 PM
  • Hi Francesco2017,

    Thank you for posting here.

    For your question, you want to add or modify the rows in the csv file depending on whether the text exists before.

    Here is a code example.

       class Program
        {
            static DataTable dt = GetDataTable();
            static void Main(string[] args)
            {
                string[] datas = new string[] { "c456","b123","a456","C123","V345"};
    
                foreach (DataColumn item in dt.Columns)
                {
                    item.ReadOnly = false;
                }
                for (int i = 0; i < datas.Length; i++)
                {
                    IsExists(datas[i]);
                }
                InsertToCsv(dt);
            }
            static void IsExists(string data)
            {
                int count = 0;
                foreach (DataRow row in dt.Rows)
                {
                    var item = row["COMPONENT"].ToString();
                    if (item.ToLower().Equals(data.ToLower()))
                    {
                        int nums = int.Parse(row["FREQUENCY"].ToString()) + 1;
                        row["FREQUENCY"] = nums;
                        count++;
                    }
                }
                if (count==0)
                {
                    dt.Rows.Add(data, 1);
                }
            }
            static DataTable GetDataTable()
            {
                using (var reader = new StreamReader(@"d:\test\test.csv"))
                using (var csv = new CsvReader(reader))
                {
                    using (var dr = new CsvDataReader(csv))
                    {
                        var dt = new DataTable();
                        dt.Load(dr);
                        return dt;
                    }
                }
            }
            static void InsertToCsv(DataTable dt)
            {
                using (var textWriter = File.CreateText(@"D:\test\test.csv"))
                using (var csv = new CsvWriter(textWriter))
                {
                    // Write columns
                    foreach (DataColumn column in dt.Columns)
                    {
                        csv.WriteField(column.ColumnName);
                    }
                    csv.NextRecord();
    
                    // Write row values
                    foreach (DataRow row in dt.Rows)
                    {
                        for (var i = 0; i < dt.Columns.Count; i++)
                        {
                            csv.WriteField(row[i]);
                        }
                        csv.NextRecord();
                    }
                }
            }
        }

    Before:

    After:

    Hope this could be helpful.

    Best Regards,

    Timon


    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, December 3, 2019 7:14 AM