Reading CSV files using C

Question

I m writing a simple import application and need to read a CSV file  show result in a DataGrid and show corrupted lines of the CSV file in another grid  For example  show the lines that are shorter than 5 values in another grid  I m trying to do that like this   StreamReader sr   new StreamReader FilePath   importingData   new Account    string line  string   row   new string  5   while   line   sr ReadLine       null        row   line Split            importingData Add new Transaction               Date   DateTime Parse row 0            Reference   row 1           Description   row 2           Amount   decimal Parse row 3            Category    Category Enum Parse typeof Category   row 4               but it s very difficult to operate on arrays in this case  Is there a better way to split the values

User · Answer

I d highly suggest using CsvHelper   Here s a quick example    public class csvExampleClass       public string Id   get  set        public string Firstname   get  set        public string Lastname   get  set       var items   DeserializeCsvFile lt List lt csvExampleClass gt  gt   csvText     public static List lt T gt  DeserializeCsvFile lt T gt  string text        CsvReader csv   new CsvReader  new StringReader  text          csv Configuration Delimiter            csv Configuration HeaderValidated   null      csv Configuration MissingFieldFound   null      return  List lt T gt  csv GetRecords lt T gt         Full documentation can be found at  https   joshclose github io CsvHelper

User · Answer

Sometimes using libraries are cool when you do not want to reinvent the wheel  but in this case one can do the same job with fewer lines of code and easier to read compared to using libraries  Here is a different approach which I find very easy to use    In this example  I use StreamReader to read the file Regex to detect the delimiter from each line s   An array to collect the columns from index 0 to n     using  StreamReader reader   new StreamReader fileName                 string line            while   line   reader ReadLine       null                          Define pattern             Regex CSVParser   new Regex                                                           Separating columns to array             string   X   CSVParser Split line                   Do something with X

User · Answer

I use this here   http   www codeproject com KB database GenericParser aspx  Last time I was looking for something like this I found it as an answer to this question

User · Answer

Another one to this list  Cinchoo ETL - an open source library to read and write CSV files  For a sample CSV file below  Id  Name 1  Tom 2  Mark   Quickly you can load them using library as below  using  var reader   new ChoCSVReader  test csv   WithFirstLineHeader         foreach  dynamic item in reader             Console WriteLine item Id         Console WriteLine item Name            If you have POCO class matching the CSV file  public class Employee      public int Id   get  set       public string Name   get  set        You can use it to load the CSV file as below  using  var reader   new ChoCSVReader lt Employee gt   test csv   WithFirstLineHeader         foreach  var item in reader             Console WriteLine item Id         Console WriteLine item Name            Please check out articles at CodeProject on how to use it   Disclaimer  I m the author of this library

User · Answer

CSV can get complicated real fast  Use something robust and well-tested  FileHelpers  www filehelpers net   The FileHelpers are a free and easy to use  NET library to import export data from fixed length or delimited records in files  strings or streams

User · Answer

My experience is that there are many different csv formats  Specially how they handle escaping of quotes and delimiters within a field   These are the variants I have ran into    quotes are quoted and doubled  excel  i e  15  -  field1  15    field3 quotes are not changed unless the field is quoted for some other reason  i e  15  -  field1 15  fields3 quotes are escaped with    i e  15  -  field1  15    field3 quotes are not changed at all  this is not always possible to parse correctly  delimiter is quoted  excel   i e  a b -  field1  a b  field3 delimiter is escaped with    i e  a b -  field1 a  b field3   I have tried many of the existing csv parsers but there is not a single one that can handle the variants I have ran into  It is also difficult to find out from the documentation which escaping variants the parsers support   In my projects I now use either the VB TextFieldParser or a custom splitter

User · Answer

I recommend CsvHelper from Nuget  PS  Regarding other more upvoted answers  I m sorry but adding a reference to Microsoft VisualBasic is   Ugly Not cross-platform  because it s not available in  NETCore  NET5  and Mono never had very good support of Visual Basic  so it may be buggy

User · Answer

Don t reinvent the wheel  Take advantage of what s already in  NET BCL     add a reference to the Microsoft VisualBasic  yes  it says VisualBasic but it works in C  just as well - remember that at the end it is all just IL  use the Microsoft VisualBasic FileIO TextFieldParser class to parse CSV file   Here is the sample code   using  TextFieldParser parser   new TextFieldParser   c  temp test csv          parser TextFieldType   FieldType Delimited      parser SetDelimiters           while   parser EndOfData                   Processing row         string   fields   parser ReadFields            foreach  string field in fields                           TODO  Process field                     It works great for me in my C  projects    Here are some more links informations    MSDN  Read From Comma-Delimited Text Files in Visual Basic MSDN  TextFieldParser Class

User · Answer

First of all need to understand what is CSV and how to write it    Every next string    r n   is next  table  row   Table  cells is separated by some delimiter symbol  Most often used symbols is  t or   Every cell possibly can contain this delimiter symbol  cell must to start with quotes symbol and ends with this symbol in this case  Every cell possibly can contains  r n sybols   cell must to start with quotes symbol and ends with this symbol in this case    The easiest way for C  Visual Basic to work with CSV files is to use standard Microsoft VisualBasic library  You just need to add needed reference  and the following string to your class   using Microsoft VisualBasic FileIO    Yes  you can use it in C   don t worry  This library can read relatively big files and supports all of needed rules  so you will be able to work with all of CSV files   Some time ago I had wrote simple class for CSV read write based on this library  Using this simple class you will be able to work with CSV like with 2 dimensions array   You can find my class by the following link  https   github com ukushu DataExporter  Simple example of using   Csv csv   new Csv   t     delimiter symbol  csv FileOpen  c   file1 csv     var row1Cell6Value   csv Rows 0  5    csv AddRow  asdf   asdffffff   5    csv FileSave  c   file2 csv

User · Answer

private static DataTable ConvertCSVtoDataTable string strFilePath                        DataTable dt   new DataTable                using  StreamReader sr   new StreamReader strFilePath                                 string   headers   sr ReadLine   Split                       foreach  string header in headers                                        dt Columns Add header                                     while   sr EndOfStream                                        string   rows   sr ReadLine   Split                           DataRow dr   dt NewRow                        for  int i   0  i  lt  headers Length  i                                                  dr i    rows i                                             dt Rows Add dr                                                 return dt                     private static void WriteToDb DataTable dt                        string connectionString                    Data Source localhost                      Initial Catalog Northwind                      Integrated Security SSPI                 using  SqlConnection con   new SqlConnection connectionString                                         using  SqlCommand cmd   new SqlCommand  spInsertTest   con                                                 cmd CommandType   CommandType StoredProcedure                           cmd Parameters Add   policyID   SqlDbType Int  Value   12                          cmd Parameters Add   statecode   SqlDbType VarChar  Value    blagh2                           cmd Parameters Add   county   SqlDbType VarChar  Value    blagh3                            con Open                            cmd ExecuteNonQuery

User · Answer

To complete the previous answers  one may need a collection of objects from his CSV File  either parsed by the TextFieldParser or the string Split method  and then each line converted to an object via Reflection  You obviously first need to define a class that matches the lines of the CSV file   I used the simple CSV Serializer from Michael Kropat found here  Generic class to CSV  all properties  and reused his methods to get the fields and properties of the wished class   I deserialize my CSV file with the following method   public static IEnumerable lt T gt  ReadCsvFileTextFieldParser lt T gt  string fileFullPath  string delimiter        where T   new         if   File Exists fileFullPath                 return null             var list   new List lt T gt         var csvFields   GetAllFieldOfClass lt T gt         var fieldDict   new Dictionary lt int  MemberInfo gt          using  TextFieldParser parser   new TextFieldParser fileFullPath                 parser SetDelimiters delimiter            bool headerParsed   false           while   parser EndOfData                          Processing row             string   rowFields   parser ReadFields                if   headerParsed                                for  int i   0  i  lt  rowFields Length  i                                             First row shall be the header                      var csvField   csvFields Where f   gt  f Name    rowFields i   FirstOrDefault                        if  csvField    null                                                fieldDict Add i  csvField                                                           headerParsed   true                            else                               T newObj   new T                    for  int i   0  i  lt  rowFields Length  i                                          var csvFied   fieldDict i                       var record   rowFields i                        if  csvFied is FieldInfo                                                  FieldInfo csvFied  SetValue newObj  record                                             else if  csvFied is PropertyInfo                                                var pi    PropertyInfo csvFied                          pi SetValue newObj  Convert ChangeType record  pi PropertyType   null                                             else                                               throw new Exception  Unhandled case                                                             if  newObj    null                                        list Add newObj                                                       return list     public static IEnumerable lt MemberInfo gt  GetAllFieldOfClass lt T gt          return         from mi in typeof T  GetMembers BindingFlags Public   BindingFlags Instance   BindingFlags Static          where new     MemberTypes Field  MemberTypes Property   Contains mi MemberType          let orderAttr    ColumnOrderAttribute Attribute GetCustomAttribute mi  typeof ColumnOrderAttribute           orderby orderAttr    null   int MaxValue   orderAttr Order  mi Name         select mi

User · Answer

Here s a solution I coded up today for a situation where I needed to parse a CSV without relying on external libraries  I haven t tested performance for large files since it wasn t relevant to my particular use case but I d expect it to perform reasonably well for most situations          static List lt List lt string gt  gt  ParseCsv string csv                var parsedCsv   new List lt List lt string gt  gt                 var row   new List lt string gt                 string field    quot  quot               bool inQuotedField   false               for  int i   0  i  lt  csv Length  i                      char current   csv i                   char next   i    csv Length - 1         csv i   1                       if current character is not a quote or comma or carriage return or newline  or not a quote and currently in an a quoted field   just add the character to the current field text                 if   current      quot    amp  amp  current         amp  amp  current      r   amp  amp  current      n       current      quot    amp  amp  inQuotedField                         field    current                    else if  current           current      t                         continue     ignore whitespace outside a quoted field                   else if  current      quot                          if  inQuotedField  amp  amp  next      quot         quote is escaping a quote within a quoted field                         i       skip escaping quote                         field    current                        else if  inQuotedField       quote signifies the end of a quoted field                         row Add field                           if  next                                       i       skip the comma separator since we ve already found the end of the field                                                   field    quot  quot                           inQuotedField   false                        else      quote signifies the beginning of a quoted field                         inQuotedField   true                                           else if  current                                  row Add field                       field    quot  quot                     else if  current      n                         row Add field                       parsedCsv Add new List lt string gt  row                        field    quot  quot                       row Clear                                                 return parsedCsv

[c#] Reading CSV files using C#

Examples related to c#

Examples related to csv