<p>What's the best way to import a CSV file into a strongly-typed data structure?</p>

[{'answer_id': 1905, 'author': 'helloandre', 'author_id': 50, 'author_profile': 'https://Stackoverflow.com/users/50', 'pm_score': 2, 'selected': False, 'text': '<p>A good simple way to do it is to open the file, and read each line into an array, linked list, data-structure-of-your-choice. Be careful about handling the first line though.</p>\n\n<p>This may be over your head, but there seems to be a direct way to access them as well using a <a href="http://web.archive.org/web/20040202071915/http://weblogs.asp.net:80/fmarguerie/archive/2003/10/01/29964.aspx" rel="nofollow noreferrer">connection string</a>.</p>\n\n<p>Why not try using Python instead of C# or VB? It has a nice CSV module to import that does all the heavy lifting for you.</p>\n'}, {'answer_id': 1912, 'author': 'Mike Stone', 'author_id': 122, 'author_profile': 'https://Stackoverflow.com/users/122', 'pm_score': 0, 'selected': False, 'text': '<p>If you can guarantee that there are no commas in the data, then the simplest way would probably be to use <a href="http://msdn.microsoft.com/en-us/library/system.string.split(VS.71).aspx" rel="nofollow noreferrer">String.split</a>.</p>\n\n<p>For example:</p>\n\n<pre><code>String[] values = myString.Split(\',\');\nmyObject.StringField = values[0];\nmyObject.IntField = Int32.Parse(values[1]);\n</code></pre>\n\n<p>There may be libraries you could use to help, but that\'s probably as simple as you can get.  Just make sure you can\'t have commas in the data, otherwise you will need to parse it better.</p>\n'}, {'answer_id': 1928, 'author': 'Yaakov Ellis', 'author_id': 51, 'author_profile': 'https://Stackoverflow.com/users/51', 'pm_score': 3, 'selected': False, 'text': '<p>There are two articles on CodeProject that provide code for a solution, one that uses <a href="http://www.codeproject.com/KB/database/CsvReader.aspx" rel="nofollow noreferrer">StreamReader</a> and one that <a href="http://www.codeproject.com/KB/database/FinalCSVReader.aspx" rel="nofollow noreferrer">imports CSV data</a> using the <a href="http://support.microsoft.com/kb/187670" rel="nofollow noreferrer">Microsoft Text Driver</a>.</p>\n'}, {'answer_id': 5790, 'author': 'Brian Leahy', 'author_id': 580, 'author_profile': 'https://Stackoverflow.com/users/580', 'pm_score': 3, 'selected': False, 'text': '<p>I was bored so i modified some stuff i wrote.  It try\'s to encapsulate the parsing in an OO manner whle cutting down on the amount of iterations through the file, it only iterates once at the top foreach.</p>\n\n<pre><code>using System;\n\nusing System.Collections.Generic;\n\nusing System.Linq;\n\nusing System.Text;\n\nusing System.IO;\n\nnamespace ConsoleApplication1\n{\n    class Program\n    {\n\n        static void Main(string[] args)\n        {\n\n            // usage:\n\n            // note this wont run as getting streams is not Implemented\n\n            // but will get you started\n\n            CSVFileParser fileParser = new CSVFileParser();\n\n            // TO Do:  configure fileparser\n\n            PersonParser personParser = new PersonParser(fileParser);\n\n            List&lt;Person&gt; persons = new List&lt;Person&gt;();\n            // if the file is large and there is a good way to limit\n            // without having to reparse the whole file you can use a \n            // linq query if you desire\n            foreach (Person person in personParser.GetPersons())\n            {\n                persons.Add(person);\n            }\n\n            // now we have a list of Person objects\n        }\n    }\n\n    public abstract  class CSVParser \n    {\n\n        protected String[] deliniators = { "," };\n\n        protected internal IEnumerable&lt;String[]&gt; GetRecords()\n        {\n\n            Stream stream = GetStream();\n            StreamReader reader = new StreamReader(stream);\n\n            String[] aRecord;\n            while (!reader.EndOfStream)\n            {\n                  aRecord = reader.ReadLine().Split(deliniators,\n                   StringSplitOptions.None);\n\n                yield return aRecord;\n            }\n\n        }\n\n        protected abstract Stream GetStream(); \n\n    }\n\n    public class CSVFileParser : CSVParser\n    {\n        // to do: add logic to get a stream from a file\n\n        protected override Stream GetStream()\n        {\n            throw new NotImplementedException();\n        } \n    }\n\n    public class CSVWebParser : CSVParser\n    {\n        // to do: add logic to get a stream from a web request\n\n        protected override Stream GetStream()\n        {\n            throw new NotImplementedException();\n        }\n    }\n\n    public class Person\n    {\n        public String Name { get; set; }\n        public String Address { get; set; }\n        public DateTime DOB { get; set; }\n    }\n\n    public class PersonParser \n    {\n\n        public PersonParser(CSVParser parser)\n        {\n            this.Parser = parser;\n        }\n\n        public CSVParser Parser { get; set; }\n\n        public  IEnumerable&lt;Person&gt; GetPersons()\n        {\n            foreach (String[] record in this.Parser.GetRecords())\n            {\n                yield return new Person()\n                {\n                    Name = record[0],\n                    Address = record[1],\n                    DOB = DateTime.Parse(record[2]),\n                };\n            }\n        }\n    }\n}\n</code></pre>\n'}, {'answer_id': 6111, 'author': 'ICR', 'author_id': 214, 'author_profile': 'https://Stackoverflow.com/users/214', 'pm_score': 3, 'selected': False, 'text': '<p>Brian gives a nice solution for converting it to a strongly typed collection.</p>\n\n<p>Most of the CSV parsing methods given don\'t take into account escaping fields or some of the other subtleties of CSV files (like trimming fields). Here is the code I personally use. It\'s a bit rough around the edges and has pretty much no error reporting.</p>\n\n<pre><code>public static IList&lt;IList&lt;string&gt;&gt; Parse(string content)\n{\n    IList&lt;IList&lt;string&gt;&gt; records = new List&lt;IList&lt;string&gt;&gt;();\n\n    StringReader stringReader = new StringReader(content);\n\n    bool inQoutedString = false;\n    IList&lt;string&gt; record = new List&lt;string&gt;();\n    StringBuilder fieldBuilder = new StringBuilder();\n    while (stringReader.Peek() != -1)\n    {\n        char readChar = (char)stringReader.Read();\n\n        if (readChar == \'\\n\' || (readChar == \'\\r\' &amp;&amp; stringReader.Peek() == \'\\n\'))\n        {\n            // If it\'s a \\r\\n combo consume the \\n part and throw it away.\n            if (readChar == \'\\r\')\n            {\n                stringReader.Read();\n            }\n\n            if (inQoutedString)\n            {\n                if (readChar == \'\\r\')\n                {\n                    fieldBuilder.Append(\'\\r\');\n                }\n                fieldBuilder.Append(\'\\n\');\n            }\n            else\n            {\n                record.Add(fieldBuilder.ToString().TrimEnd());\n                fieldBuilder = new StringBuilder();\n\n                records.Add(record);\n                record = new List&lt;string&gt;();\n\n                inQoutedString = false;\n            }\n        }\n        else if (fieldBuilder.Length == 0 &amp;&amp; !inQoutedString)\n        {\n            if (char.IsWhiteSpace(readChar))\n            {\n                // Ignore leading whitespace\n            }\n            else if (readChar == \'"\')\n            {\n                inQoutedString = true;\n            }\n            else if (readChar == \',\')\n            {\n                record.Add(fieldBuilder.ToString().TrimEnd());\n                fieldBuilder = new StringBuilder();\n            }\n            else\n            {\n                fieldBuilder.Append(readChar);\n            }\n        }\n        else if (readChar == \',\')\n        {\n            if (inQoutedString)\n            {\n                fieldBuilder.Append(\',\');\n            }\n            else\n            {\n                record.Add(fieldBuilder.ToString().TrimEnd());\n                fieldBuilder = new StringBuilder();\n            }\n        }\n        else if (readChar == \'"\')\n        {\n            if (inQoutedString)\n            {\n                if (stringReader.Peek() == \'"\')\n                {\n                    stringReader.Read();\n                    fieldBuilder.Append(\'"\');\n                }\n                else\n                {\n                    inQoutedString = false;\n                }\n            }\n            else\n            {\n                fieldBuilder.Append(readChar);\n            }\n        }\n        else\n        {\n            fieldBuilder.Append(readChar);\n        }\n    }\n    record.Add(fieldBuilder.ToString().TrimEnd());\n    records.Add(record);\n\n    return records;\n}\n</code></pre>\n\n<p>Note that this doesn\'t handle the edge case of fields not being deliminated by double quotes, but meerley having a quoted string inside of it. See <a href="https://stackoverflow.com/questions/6209/split-a-string-ignoring-quoted-sections">this post</a> for a bit of a better expanation as well as some links to some proper libraries.</p>\n'}, {'answer_id': 13387, 'author': 'pbh101', 'author_id': 1266, 'author_profile': 'https://Stackoverflow.com/users/1266', 'pm_score': 1, 'selected': False, 'text': '<p>I had to use a CSV parser in .NET for a project this summer and settled on the Microsoft Jet Text Driver.  You specify a folder using a connection string, then query a file using a SQL Select statement.  You can specify strong types using a schema.ini file.  I didn\'t do this at first, but then I was getting bad results where the type of the data wasn\'t immediately apparent, such as IP numbers or an entry like "XYQ 3.9 SP1".</p>\n\n<p>One limitation I ran into is that it cannot handle column names above 64 characters; it truncates.  This shouldn\'t be a problem, except I was dealing with very poorly designed input data.  It returns an ADO.NET DataSet.</p>\n\n<p>This was the best solution I found.  I would be wary of rolling my own CSV parser, since I would probably miss some of the end cases, and I didn\'t find any other free CSV parsing packages for .NET out there.</p>\n\n<p>EDIT:  Also, there can only be one schema.ini file per directory, so I dynamically appended to it to strongly type the needed columns.  It will only strongly-type the columns specified, and infer for any unspecified field.  I really appreciated this, as I was dealing with importing a fluid 70+ column CSV and didn\'t want to specify each column, only the misbehaving ones.</p>\n'}, {'answer_id': 13416, 'author': 'Jon Limjap', 'author_id': 372, 'author_profile': 'https://Stackoverflow.com/users/372', 'pm_score': 4, 'selected': False, 'text': '<p>If you\'re expecting fairly complex scenarios for CSV parsing, <em>don\'t even think up of rolling our own parser</em>. There are a lot of excellent tools out there, like <a href="http://www.filehelpers.com/" rel="noreferrer">FileHelpers</a>, or even ones from <a href="http://www.codeproject.com/KB/database/CsvReader.aspx" rel="noreferrer">CodeProject</a>.</p>\n\n<p>The point is this is a fairly common problem and you could bet that <em>a lot</em> of software developers have already thought about and solved this problem.</p>\n'}, {'answer_id': 13420, 'author': 'Jon Galloway', 'author_id': 5, 'author_profile': 'https://Stackoverflow.com/users/5', 'pm_score': 3, 'selected': False, 'text': '<p>I agree with @<a href="https://stackoverflow.com/users/303/notmyself">NotMyself</a>. <a href="http://filehelpers.sourceforge.net/" rel="nofollow noreferrer">FileHelpers</a> is well tested and handles all kinds of edge cases that you\'ll eventually have to deal with if you do it yourself. Take a look at what FileHelpers does and only write your own if you\'re absolutely sure that either (1) you will never need to handle the edge cases FileHelpers does, or (2) you love writing this kind of stuff and are going to be overjoyed when you have to parse stuff like this:</p>\n\n<p>1,"Bill","Smith","Supervisor", "No Comment"</p>\n\n<p>2 , \'Drake,\' , \'O\'Malley\',"Janitor,</p>\n\n<p>Oops, I\'m not quoted and I\'m on a new line!</p>\n'}, {'answer_id': 265320, 'author': 'Kevin', 'author_id': 19038, 'author_profile': 'https://Stackoverflow.com/users/19038', 'pm_score': 5, 'selected': False, 'text': '<p>Use an OleDB connection.</p>\n\n<pre><code>String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\\\InputDirectory\\\\;Extended Properties=\'text;HDR=Yes;FMT=Delimited\'";\nOleDbConnection objConn = new OleDbConnection(sConnectionString);\nobjConn.Open();\nDataTable dt = new DataTable();\nOleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM file.csv", objConn);\nOleDbDataAdapter objAdapter1 = new OleDbDataAdapter();\nobjAdapter1.SelectCommand = objCmdSelect;\nobjAdapter1.Fill(dt);\nobjConn.Close();\n</code></pre>\n'}, {'answer_id': 707092, 'author': 'MarkJ', 'author_id': 15639, 'author_profile': 'https://Stackoverflow.com/users/15639', 'pm_score': 6, 'selected': False, 'text': '<p>Microsoft\'s <a href="http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser.aspx" rel="nofollow noreferrer">TextFieldParser</a> is stable and follows <a href="https://www.rfc-editor.org/rfc/rfc4180" rel="nofollow noreferrer">RFC 4180</a> for CSV files.  Don\'t be put off by the <code>Microsoft.VisualBasic</code> namespace; it\'s a standard component in the .NET Framework, just add a reference to the global <code>Microsoft.VisualBasic</code> assembly.</p>\n<p>If you\'re compiling for Windows (as opposed to Mono) and don\'t anticipate having to parse &quot;broken&quot; (non-RFC-compliant) CSV files, then this would be the obvious choice, as it\'s free, unrestricted, stable, and actively supported, most of which cannot be said for FileHelpers.</p>\n<p>See also: <a href="http://msdn.microsoft.com/en-us/library/cakac7e6.aspx" rel="nofollow noreferrer">How to: Read From Comma-Delimited Text Files in Visual Basic</a> for a VB code example.</p>\n'}, {'answer_id': 7359992, 'author': 'Pieter', 'author_id': 761227, 'author_profile': 'https://Stackoverflow.com/users/761227', 'pm_score': 1, 'selected': False, 'text': '<p>I typed in some code. The result in the datagridviewer looked good. It parses a single line of text to an arraylist of objects.</p>\n\n<pre><code>    enum quotestatus\n    {\n        none,\n        firstquote,\n        secondquote\n    }\n    public static System.Collections.ArrayList Parse(string line,string delimiter)\n    {        \n        System.Collections.ArrayList ar = new System.Collections.ArrayList();\n        StringBuilder field = new StringBuilder();\n        quotestatus status = quotestatus.none;\n        foreach (char ch in line.ToCharArray())\n        {                                \n            string chOmsch = "char";\n            if (ch == Convert.ToChar(delimiter))\n            {\n                if (status== quotestatus.firstquote)\n                {\n                    chOmsch = "char";\n                }                         \n                else\n                {\n                    chOmsch = "delimiter";                    \n                }                    \n            }\n\n            if (ch == Convert.ToChar(34))\n            {\n                chOmsch = "quotes";           \n                if (status == quotestatus.firstquote)\n                {\n                    status = quotestatus.secondquote;\n                }\n                if (status == quotestatus.none )\n                {\n                    status = quotestatus.firstquote;\n                }\n            }\n\n            switch (chOmsch)\n            {\n                case "char":\n                    field.Append(ch);\n                    break;\n                case "delimiter":                        \n                    ar.Add(field.ToString());\n                    field.Clear();\n                    break;\n                case "quotes":\n                    if (status==quotestatus.firstquote)\n                    {\n                        field.Clear();                            \n                    }\n                    if (status== quotestatus.secondquote)\n                    {                                                                           \n                            status =quotestatus.none;                                \n                    }                    \n                    break;\n            }\n        }\n        if (field.Length != 0)            \n        {\n            ar.Add(field.ToString());                \n        }           \n        return ar;\n    }\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/1898', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/81/']