How to split CSV file

Question

"0.0.0.0,""0.255.255.255"",""ZZ"""                
"1.0.0.0,""1.0.0.255"",""AU"""
"1.0.1.0,""1.0.3.255"",""CN"""
"1.0.4.0,""1.0.7.255"",""AU"""
"1.0.8.0,""1.0.15.255"",""CN"""
"1.0.16.0,""1.0.31.255"",""JP"""
"1.0.32.0,""1.0.63.255"",""CN"""
"1.0.64.0,""1.0.127.255"",""JP"""
"1.0.128.0,""1.0.255.255"",""TH"""
"1.1.0.0,""1.1.0.255"",""CN"""
"1.1.1.0,""1.1.1.255"",""AU"""
"1.1.2.0,""1.1.63.255"",""CN"""
"1.1.64.0,""1.1.127.255"",""JP"""
"1.1.128.0,""1.1.255.255"",""TH"""

İN EXCEL

0.0.0.0,"0.255.255.255","ZZ"
1.0.0.0,"1.0.0.255","AU"
1.0.1.0,"1.0.3.255","CN"
1.0.4.0,"1.0.7.255","AU"
1.0.8.0,"1.0.15.255","CN"
1.0.16.0,"1.0.31.255","JP"
1.0.32.0,"1.0.63.255","CN"
1.0.64.0,"1.0.127.255","JP"
1.0.128.0,"1.0.255.255","TH"
1.1.0.0,"1.1.0.255","CN"
1.1.1.0,"1.1.1.255","AU"
1.1.2.0,"1.1.63.255","CN"
1.1.64.0,"1.1.127.255","JP"
1.1.128.0,"1.1.255.255","TH"
1.2.0.0,"1.2.2.255","CN"
1.2.3.0,"1.2.3.255","AU"
1.2.4.0,"1.2.127.255","CN"
1.2.128.0,"1.2.255.255","TH"
1.3.0.0,"1.3.255.255","CN"
1.4.0.0,"1.4.0.255","AU"
1.4.1.0,"1.4.127.255","CN"
1.4.128.0,"1.4.255.255","TH"

How can split this CSV file.

For example 0.0.0.0 0.255.255.255 ZZ for first row and how can add datagridview with 3columns

Possible duplicate of [How to properly split a CSV using C# split() function?](https://stackoverflow.com/questions/17207269/how-to-properly-split-a-csv-using-c-sharp-split-function) — SᴇM, Jun 28 '18 at 05:49
By using a dedicated CSV reader, such as [CsvHelper](https://joshclose.github.io/CsvHelper/), rather than going through the pain of reinventing the wheel. — ProgrammingLlama, Jun 28 '18 at 06:01

score 0 · Answer 1 · answered Jun 28 '18 at 05:52

0

You can do it via the following way..

using System.IO;
static void Main(string[] args)
{
    using(var reader = new StreamReader(@"C:\test.csv"))
    {
        List<string> listA = new List<string>();
        List<string> listB = new List<string>();
        while (!reader.EndOfStream)
        {
            var line = reader.ReadLine();
            var values = line.Split(','); // or whatever yur get by reading that file

            listA.Add(values[0]);
            listB.Add(values[1]);
        }
    }
}

answered Jun 28 '18 at 05:52

Shrey Batra

11

3

You should explain your answer a little bit to make this a good answer – ochs.tobi Jun 28 '18 at 06:02
You may chop a record in the middle when a string value has a \r\n in it. – PepitoSh Jun 28 '18 at 06:24

Transcendent · Answer 2 · 2018-06-28T06:19:51.227

0

A CSV file is either a Tab delimited or a Comma delimited file. That said; you have to read the file line by line and then separate the values available in a line based on the delimiter character. The first line usually appears in a CSV file is usually the headers which you can use in order to produce a KeyValue pair to make your collection more efficient. For example:

 Dictionary<int, Dictionary<String, String>> values = new Dictionary<int, Dictionary<String,String>>();
 using(FileStream fileStream = new FileStream(@"D:\MyCSV.csv", FileMode.Open, FileAccess.Read, FileShare.Read)) {
     using(StreamReader streamReader = new StreamReader(fileStream)){
          //You can skip this line if there is no header
          // Then instead of Dictionary<String,String> you use List<String>
          var headers = streamReader.ReadLine().Split(',');
          String line = null;
          int lineNumber = 1;
          while(!streamReader.EndOfStream){
               line = streamReader.ReadLine().split(',');
               if(line.Length == headers.Length){
                   var temp = new Dictionary<String, String>();
                   for(int i = 0; i < headers.Length; i++){
                      // You can remove '"' character by line[i].Replace("\"", "") or through using the Substring method
                      temp.Add(headers[i], line[i]);
                   }
                   values.Add(lineNumber, temp);
               }
               lineNumber++;
          }              
     }

In case the data structure of your CSV is constant and it will not change in the future, you can develop a strongly typed data model and get rid of the Dictionary type. This approach will be more elegant and more efficient.

edited Jun 28 '18 at 06:19

answered Jun 28 '18 at 06:14

Transcendent

5,598
4
24
47

CSV by definition is comma separated values. The similar structure text files that use TAB as delimiter are *not* CSV. – PepitoSh Jun 28 '18 at 06:19
@PepitoSh: In data mining projects, sometimes a comma delimited CSV is troublesome, so we go for a Tab Delimited one. That is why I mentioned it. – Transcendent Jun 28 '18 at 06:21
You should not split the line you read by commas. You may split a string value that has comma in it. Also, you may read a partial record as \r\n may be part of a string value. Parsing CSV is a bit more complicated. – PepitoSh Jun 28 '18 at 06:22
@PepitoSh: `You may split a string value that has comma in it`, Why? it is the line that is delimited by ',' not the individual records! – Transcendent Jun 28 '18 at 06:24
No. In CSV columns are delimited with comma. See Specification: https://en.wikipedia.org/wiki/Comma-separated_values And I concur, tabs may be used as delimiters. :) – PepitoSh Jun 28 '18 at 06:26
@PepitoSh: Exactly, if you mean that separation by a comma may introduce problems, then yes you are very right! My answer is limited to what the OP asked. In that case we have two options, either by convention we wrap each value/column inside a tag or we do not use a comma within values. – Transcendent Jun 28 '18 at 06:29
Or we do not call it CSV. :-D – PepitoSh Jun 28 '18 at 06:30
@PepitoSh: Haha, the same concept that has been adapted in JSON files also applies here. You do not expect me to develop a high-end program and post it as an answer, do you ? :D – Transcendent Jun 28 '18 at 06:31
Sure I don't. Particularly that the OP is asking for a DataGridView implementation as well. With 3 columns. For now. :-) – PepitoSh Jun 28 '18 at 06:39

score 0 · Answer 3 · answered Jun 28 '18 at 14:49

First of all, your CSV lines are surrounded by quotes. Is it copy/paste mistake? If not, you will need to sanitize the file to a valid CSV file.

You can try Cinchoo ETL - an open source library to load the CSV file to datatable, then you can assign it to your DataGridView source.

I'll show you both approach, how to handle

Valid CSV: (test.csv)

0.0.0.0,"0.255.255.255","ZZ"
1.0.0.0,"1.0.0.255","AU"
1.0.1.0,"1.0.3.255","CN"
1.0.4.0,"1.0.7.255","AU"
1.0.8.0,"1.0.15.255","CN"
1.0.16.0,"1.0.31.255","JP"
1.0.32.0,"1.0.63.255","CN"
1.0.64.0,"1.0.127.255","JP"
1.0.128.0,"1.0.255.255","TH"
1.1.0.0,"1.1.0.255","CN"
1.1.1.0,"1.1.1.255","AU"
1.1.2.0,"1.1.63.255","CN"
1.1.64.0,"1.1.127.255","JP"
1.1.128.0,"1.1.255.255","TH"

Read CSV:

using (var p = new ChoCSVReader("test.csv"))
{
    var dt = p.AsDataTable();
    //Assign dt to DataGridView
}

Next approach

Invalid CSV: (test.csv)

"0.0.0.0,""0.255.255.255"",""ZZ"""
"1.0.0.0,""1.0.0.255"",""AU"""
"1.0.1.0,""1.0.3.255"",""CN"""
"1.0.4.0,""1.0.7.255"",""AU"""
"1.0.8.0,""1.0.15.255"",""CN"""
"1.0.16.0,""1.0.31.255"",""JP"""
"1.0.32.0,""1.0.63.255"",""CN"""
"1.0.64.0,""1.0.127.255"",""JP"""
"1.0.128.0,""1.0.255.255"",""TH"""
"1.1.0.0,""1.1.0.255"",""CN"""
"1.1.1.0,""1.1.1.255"",""AU"""
"1.1.2.0,""1.1.63.255"",""CN"""
"1.1.64.0,""1.1.127.255"",""JP"""
"1.1.128.0,""1.1.255.255"",""TH"""

Read CSV:

using (var p = new ChoCSVReader("Sample6.csv"))
{
    p.SanitizeLine += (o, e) =>
    {
        string line = e.Line as string;
        if (line != null)
        {
            line = line.Substring(1, line.Length - 2);
            line = line.Replace(@"""""", @"""");
        }

        e.Line - line;
    };

    var dt = p.AsDataTable();
    //Assign dt to DataGridView
}

Hope it helps.

How to split CSV file

3 Answers3