Importing CSV into SQL

Question

I am attempting to import data from a comma delimited csv file into SQL. The csv file has fields in quotes because some of the fields have commas as part of the data. If I split on the comma it is splitting the fields that have commas into 2 fields which I don't want. How do I split it correctly?

CSV file sample:

"LASTNAME","FIRSTNAME","MIDNAME","BUSNAME","GENERAL","SPECIALTY"
"BERRY","JOY","YVONNE ",,"MEDICAL PRACTICE, MD","FAMILY PRACTICE",
"BERRY","JOE"," ",,"IND- LIC HC SERV PRO","NURSE/NURSES AIDE"

Code:

       try
        {

            StreamReader sr = new StreamReader(strFilePath);
            string line = sr.ReadLine();

            string[] value = line.Split(',');

            System.Data.DataTable dt = new System.Data.DataTable();
            DataRow row;
            foreach (string dc in value)
            {
                dt.Columns.Add(new DataColumn(dc));
            }

            while (!sr.EndOfStream)
            {
                value = (sr.ReadLine()).Split(',');
                if (value.Length == dt.Columns.Count)
                {
                    row = dt.NewRow();
                    row.ItemArray = value;
                    dt.Rows.Add(row);
                }
            }
            SqlBulkCopy bc = new SqlBulkCopy(connsql.ConnectionString, SqlBulkCopyOptions.TableLock);
            bc.DestinationTableName = tablename;
            bc.BatchSize = dt.Rows.Count;
            bc.WriteToServer(dt);
            bc.Close();
        }
        catch (Exception ex)
        {
            //MessageBox.Show("Error importing Staff Summary: " + ex.Message);
            connsql.Close();
        }

You fell victim to one of the classic blunders, the most famous of which is “Never get involved in a land war in Asia,” but only slightly less well known is this: "Never use String.Split() when csv data is involved." — Joel Coehoorn, Sep 22 '20 at 20:06
https://stackoverflow.com/questions/54688699/read-csv-file-and-insert-to-localdb-asp-net-mvc/54718270#54718270 — Cinchoo, Sep 27 '20 at 01:02

Jonathan Wood · Answer 1 · 2020-09-22T22:15:18.947

As you are finding out, the task is more complex than just calling string.Split(). You need to parse the line and find which fields are quoted, and ignore any commas within those fields. In addition, pairs of quotes within such a field should be considered a single quote rather than the end of the field.

You'll need to parse the line character by character.

Alternatively, you can use an open-source library such as my SoftCircuits.CsvParser, which also supports fields that span more than one line. It even has classes that can map CSV columns to class properties. It's up to four times faster than the popular CsvHelper, and you can add this package right from within Visual Studio.

score 1 · Answer 2 · answered Sep 22 '20 at 23:17

1

You should use a CSV parser like TextFieldParser - splitting the string won't work. (Yes, you can use this in C# too, even though it's part of the Visual Basic library).

answered Sep 22 '20 at 23:17

EJoshuaS - Stand with Ukraine

11,977
56
49
78

Importing CSV into SQL

2 Answers2