CODEWITHSUNDEEP
c#asp.net-mvccsvimport
Ruan Knoetze
Ruan Knoetze

Reputation: 23

Verify file format is csv on import by determining delimiter and checking all columns are included with .NET

I am busy doing a project where I have to add import and export functionality. I was able to quite easily make the export functionality work. Here is my question regarding Import:

I was able to make a template with 3 columns : stock_code;item_name;price All future imports will only have these 3 columns. Now here is my question:

  1. How can I determine the delimiter on import? I have done the following on the file input

    <input type="file" class="custom-file-input" id="File" accept=".csv/text/plain"

This sets the browse window to - custom files and not All files. The problem here is if they do set it to all files, I need to determine the delimiter so there is not an error.

2.In addition to determining delimiter I need to make sure that the file is only 3 columns

Useful Info: I do have CSVHelper nuget package and im working on asp.net mvc 4.6

Please help

Upvotes: 0

Views: 260

Answers (1)

MarkPflug
MarkPflug

Reputation: 29528

I have written code that implements a "delimiter detector for csv data", so I can describe the strategy that I used. First, I defined a set of "acceptable delimiters" ordered by priority: ',', '\t', ';', '|'. Those are the most common delimiters that I've seen in use.

Then, I read the first line of data out of the csv, this could be done with StreamReader.ReadLine(). I then iterate over every character in the line, and keep track of how many times I see each delimiter. The delimiter that was seen the most is the winner.

This works quite well, especially when the first line in the file is a header row that contains mostly alpha character. However, for some cultures it is common to find ',' used as a decimal point in numbers, and they tend to use ';' as the CSV delimiter. If the first row is all numeric values with decimal points (no headers) then this algorithm can mis-detect ',' as being the delimiter. I doubt this would likely ever happen in practice.

My implementation can be seen here. There is a bit extra complexity because I'm processing data out of an intermediate buffer; that could be simplified if you adapted it to your own needs.

Depending on what you intend to do with the CSV data you could use the library that this code lives in: Sylvan.Data.Csv. It is available as a nuget package.

using Sylvan.Data.Csv;
...

// by default, detects delimiter and assumes a header row is present
var csv = CsvDataReader.Create("MyData.csv");

if(csv.FieldCount != 3) {
    throw new Exception("Invalid file");
}

while(csv.Read()) 
{
  if(csv.RowFieldCount != 3) {
    // the row contains a different number of columns than expected
    throw new Exception("Invalid row at " + csv.RowNumber);
  }
  var stockCode = csv.GetString(0);
  var name = csv.GetString(1);
  // this next line would throw a FormatException if the the price column doesn't contain
  // a numeric value
  var price = csv.GetDecimal(2);

}

Upvotes: 1

Related Questions