How to read from a text file which does not have consisted data?

Reading a semi-unstructured CSV can be tricky and you must have a lot of faith in the CSV file always being correct. Example, if one of the dates for a category is misspelled like… “Panint_s=28/05/2020” … in the posted data… then, in the code below, this misspelling is not going to throw any kind of error or message, it will simply never be used. So, coder beware. Below is one such “hacky” approach.

One complication you may have is how the current code is using string arrays (string[]). This is going to make things more complicated. It is useful when the code gets to the point of SomeCategory=SomeDate. Below, a string array is used for these splits to get the date for the category. Elsewhere in the code, it uses a List<string> to separate the lines in the data. This will help when we want to get the dates for a “particular” category. More below.

So, given a string of data as you show, such that the string is a “single” row in the grid, then I would “split” that string on commas ”,”. Then convert the string array into a List<string>. It may look something like…

List<string> SplitRow = curRow.Split(',').ToList();

Given the posted single line of data, this List<string> allData may look something like….

As can be seen we will use this List<string> to get the data for the first three cells in the grid AND when we get to the portion of the code where we want to get the “latest” date for a particular category, we can “filter” this List<string> to contain ONLY the categories we want. If allData is the List<string> above, then to filter the list to contain only the category we want, the code may look something like…

String targetCategory = “Stripping”;
List<string> categoryData = allData.Where(x => x.Contains(targetCategory)).ToList();

This will produce a List<string> categoryData with only the “Stripping” categories. Like below…

With this list we can loop through all the strings, parse the dates and return the latest date from all the given dates. If we create a method that returns this date string, then we can use it to assign each category column in the grid for each row.

Therefore a method, that takes the allData list and a string category to filter by… may come in handy. This method may look something like below…

private string GetLatestDateForCategory(List<string> allData, string targetCategory) {
  List<string> categoryData = allData.Where(x => x.Contains(targetCategory)).ToList();
  DateTime latestDate = DateTime.MinValue;
  string[] splitArray;
  foreach (string catLine in categoryData) {
    splitArray = catLine.Split('=');
    if (splitArray.Length >= 2) {
      if (DateTime.TryParseExact(splitArray[1], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime date)) {
        if (date > latestDate) {
          latestDate = date;
        }
      } 
    }
  }
  if (latestDate != DateTime.MinValue) {
    return latestDate.ToShortDateString();
  }
  return "";
}

A walk through this method would start with “filtering” all the data to get a list of the targetCategory string value. Then a DateTime variable is created to compare the dates and use as the latest date. We will set the date to a “min” value and we will use this “min” date to indicate that the category has NO dates in the data. Next is the string array splitArray which is used to split each string on the “=” character.

A loop through all the filtered strings in the list is started. After splitting the string on the “=” character, a check is made to make sure there is a date in the string. If there is a date string, then that date is parsed to a DateTime object for easier comparisons. If the date parses successfully, then the code will check the parsed date to the latest date. If the parsed date is later than the current latestDate, then it is updated to reflect this newer latest date.

After all the dates have been checked, the code will check the latestDate. If the latestDate is still the “Min” value, then that would mean no dates were found for that category and an empty string is returned, otherwise the code will return the latest date string.

If the code loops through each string in the data where there are numerous rows, then the method above will come in handy when we want to get the latest date from a particular category.

So, putting this together may look something like below…

private void Import() {
  List<string> allLines = File.ReadAllLines(@"D:\Test\CSV\567.txt").ToList();
  DataRow dr;
  List<string> SplitRow;
  int value;
  foreach (string curRow in allLines) {
    dr = table.NewRow();
    SplitRow = curRow.Split(',').ToList();
    if (SplitRow.Count >= 3) {
      value = 0;
      if (int.TryParse(SplitRow[0], out value)) {
        dr["SalesNr"] = value;
      }
      if (int.TryParse(SplitRow[2], out value)) {
        dr["Type"] = value;
      }
      dr["PName"] = SplitRow[1];
      dr["Stripping"] = GetLatestDateForCategory(SplitRow, "Stripping");
      dr["Cleaning"] = GetLatestDateForCategory(SplitRow, "Cleaning");
      dr["Paint"] = GetLatestDateForCategory(SplitRow, "Paint");
      dr["Cabinet"] = GetLatestDateForCategory(SplitRow, "Cabinet");
      dr["Table"] = GetLatestDateForCategory(SplitRow, "Table");
      dr["Stand"] = GetLatestDateForCategory(SplitRow, "Stand");
      dr["Display"] = GetLatestDateForCategory(SplitRow, "Display");
      dr["User Controls"] = GetLatestDateForCategory(SplitRow, "UControls");
      table.Rows.Add(dr);
    }
  }
}

A walk through this method would start with getting a List<string> from the CSV file. In the posted data this would be a single string in the list. Next a DataRow dr is defined, we will use this row to add the values we get from the data. Another List<string> SplitRow is used for “each” string in the list. And finally, an int value which is used to parse the int values SalesNr and Type.

A loop through each string from all the data is started, a new DataRow is created from the existing table, the current string is then split on a comma “;”. A check is made to make sure there are at least three (3) values for the SalesNr, PName and Type. The SalesNr and Type values are parsed, then the PName string value is set into the new row. Then, each “category” will use the method above to set the date string for each category. Finally, the row is added to the table. Note: not all categories are filled in the code.

I hope this makes sense. Its hacky but it worked in my tests. Good Luck.

This is the way I would do it.

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

namespace WindowsFormsApplication71
{
    public partial class Form1 : Form
    {
        const string FILENAME = @"c:\temp\test.csv";
        public Form1()
        {
            InitializeComponent();

            StreamReader reader = new StreamReader(FILENAME);
            string line = "";
            int row = 0;
            DataTable dt = new DataTable();
            while ((line = reader.ReadLine()) != null)
            {
                line = line.Trim();
                if (line.Length > 0)
                {
                    string[] columns = line.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToArray();
                    List<object> newRow = new List<object>(); ;
                    if (++row == 1)
                    {
                        dt.Columns.Add("SalesNr", typeof(string));
                        dt.Columns.Add("PName", typeof(string));
                        dt.Columns.Add("Type", typeof(int));
                    }
                    for (int col = 0; col < columns.Length; col++)
                    {
                        switch (col)
                        {
                            case 0:
                                newRow.Add(columns[0]);
                                break;
                            case 1:
                                newRow.Add(columns[1]);
                                break;
                            case 2:
                                newRow.Add(columns[2]);
                                break;
                            default:
                                string[] keyValue = columns[col].Split(new char[] { '=' });
                                if (row == 1)
                                {
                                    dt.Columns.Add(keyValue[0], typeof(DateTime));
                                }
                                if (keyValue[1].Trim().Length > 0)
                                {
                                    newRow.Add(DateTime.ParseExact(keyValue[1], "dd/MM/yyyy", CultureInfo.InvariantCulture));
                                }
                                else
                                {
                                    newRow.Add(null);
                                }
                                break;
                        }
                    }
                    dt.Rows.Add(newRow.ToArray());
                }
            }
            dataGridView1.DataSource = dt;
        }
    }
}