CODEWITHSUNDEEP
c#csv
Miguel
Miguel

Reputation: 2079

Read csv file when with c# and dont read commas in double quotes

I'm reading a csv file using c# here is a little code snippet.

using (StreamReader readFile = new StreamReader("C:\\temp\\" + whichTable))
{
    while ((line = readFile.ReadLine()) != null)
    {
        row = line.Split(',');

        switch (row.Length)
        {
            case 5:
                if (counter == 0) 
                { 
                    break; 
                } 
                else
                {
                    v00.Add(Convert.ToInt32(Regex.Replace(row[0], @"[^\w\.@-]", "")));
                }

                if (row[1] == "") 
                { 
                    v01.Add((1)); 
                }
                else
                {
                    v01.Add(Convert.ToInt32(Regex.Replace(row[1], @"[^\w\.@-]", "")));
                }

                if(row[2]=="")
                {
                    v02.Add(2);
                } 
                else
                {
                    v02.Add(Convert.ToInt32(Regex.Replace(row[2], @"[^\w\.@-]", "")));
                }

                v3.Add(row[4]);
                v4.Add(row[3]);
                counter++;
                break;
        }
        counter++;
    }
    break;
}

as you can tell from my code i test the length of the string row to make sure that its five long exactly. My problem is that if have a field within the csv with a comma it then calculates to more then 5. My csv is well formed so when that happens i do have a that field double quoted. How can i tell c# only count the commas outside of double quotes? Thats really my question.

Upvotes: 3

Views: 10140

Answers (3)

Vishal Sen
Vishal Sen

Reputation: 1175

You can use ths bellow code ,Its working for me :

  private void ImportCSV(string filePath = @"E:\nucc_taxonomy_140.csv", string tableName = "TempTaxonomyCodes")
    {
        string tempPath = System.IO.Path.GetDirectoryName(filePath);
        string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @"\;Extensions=asc,csv,tab,txt";
        OdbcConnection conn = new OdbcConnection(strConn);
        OdbcDataAdapter da = new OdbcDataAdapter("Select * from " + System.IO.Path.GetFileName(filePath), conn);
        DataTable dt = new DataTable();
        da.Fill(dt);

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConfigurationSettings.AppSettings["dbConnectionString"]))
        {
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = 50;
            bulkCopy.WriteToServer(dt);
        }

    }

Upvotes: 0

Sam I am says Reinstate Monica
Sam I am says Reinstate Monica

Reputation: 31184

there are multiple possible workarounds, the simplest would be go down the row character by character and count the commas yourself. if you encounter a quotation mark, you can toggle a boolean, say. bool inQuotes, and when inQuotes is true, you just ignore commas.

Upvotes: 2

Oded
Oded

Reputation: 498952

Don't parse CSV yourself - the format is more difficult to parse properly than most people realize. There are many exsisting good CSV parsers that you can use instead.

There is the TextFieldParser library that lives in the Microsoft.VisualBasic.FileIO namespace (regular .NET libarary), and many third party ones - FileHelpers is a popular free choice.

Upvotes: 10

Related Questions