ccs ccs -4 years ago 80
C# Question

remove unnecessary white space, auto detect header, and sorted by name in datagrid from csv file. C#

I know my title is confuse and not clear, now i going to explain what i need.

In the end of the program, it will be able to input csv file, calculate and output the result.

For now i m doing it step by step.

1) Able to import csv to datagridview (done)

2) Remove unnecessary white space, sort it by name (in progress)

3) Calculation

To makes my question clear and easy to understand, here is the csv file sample.
enter image description here

As you can see that there are repeated 'lotID' is every section, and 2 type to lotID.

And here is what i have done so far. let's call this pic a.I successfully filter out lotID of the 1st type lotID.
enter image description here

this is pic B , as u can see the 'LotID' of second type(MSA) is appear again in each sectionenter image description here

As you can see in PIC A, the lotID of each section is not repeated, and it white space appear in each section.

This is the first thing i try want to fix.

Secondly, i want to filter out the 'LotID' header of second type lotid.

Here is the code.


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 test2
{
public partial class Form1 : Form
{
OpenFileDialog openFile = new OpenFileDialog();

public Form1()
{
InitializeComponent();
}


private void Button1_Click(object sender, EventArgs e)
{
if (openFile.ShowDialog() == DialogResult.OK)
{
List<string[]> rows = File.ReadLines(openFile.FileName).Select(x => x.Split(',')).ToList();
DataTable dt = new DataTable();
List<string> headerNames = rows[0].ToList();
foreach (var headers in rows[0])
{
dt.Columns.Add(headers);
}
foreach (var x in rows.Skip(1))
{
if (x.SequenceEqual(headerNames)) //linq to check if 2 lists are have the same elements (perfect for strings)
continue; //skip the row with repeated headers
dt.Rows.Add(x);
}

dataGridView1.DataSource = dt;
}
}

private void Form1_Load_1(object sender, EventArgs e)
{
openFile.Filter = "CSV|*.csv";
}
}
}


I hope that my question can helps the other if they having same problem like me too!

Appreciate.

Answer Source

For sorting by header & removing the blank rows, try this piece of code: (this requires you to know "Lot ID" will be the first column)

private void Button1_Click(object sender, EventArgs e)
{
    if (openFile.ShowDialog() == DialogResult.OK)
    {
        List<string[]> rows = File.ReadLines(openFile.FileName).Select(x => x.Split(',')).ToList();
        DataTable dt = new DataTable();
        List<string> headerNames = rows[0].ToList();
        foreach (var headers in rows[0])
        {
            dt.Columns.Add(headers);
        }
        foreach (var x in rows.Skip(1).OrderBy(r => r.First()))  //sort based on first column of each row
        {
            if (x.SequenceEqual(headerNames))   //linq to check if 2 lists are have the same elements (perfect for strings)
                continue;     //skip the row with repeated headers
            if (x.All(val => string.IsNullOrWhiteSpace(val))) //if all columns of the row are whitespace / empty, skip this row
                continue;
            dt.Rows.Add(x);
        }

        dataGridView1.DataSource = dt;
    }
}

As a kind of hackish way to remove a duplicated header line, you could try this:

if (x[0] == "Lot ID")
    continue;

instead of

if (x.SequenceEqual(headerNames))
    continue;

It's not very elegant, but it will work.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download