Quotes in between a csv column text cause skipping of remaining columns while importing csv data

I am using the following code to fetch data from a csv file:

    public DataTable GetCSVData(string CSVFileName)
    {
        string CSVConnectionString = "Driver={Mi开发者_JS百科crosoft Text Driver (*.txt; *.csv)};Dbq=" + ConfigurationSettings.AppSettings["CSVFolder"].ToString() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False;";

        using (OdbcConnection Connection = new OdbcConnection(CSVConnectionString))
        {
            DataTable CSVDataTable = new DataTable();

            string SelectQuery = string.Format(@"SELECT * FROM [{0}]", CSVFileName);

            OdbcDataAdapter Adapter = new OdbcDataAdapter(SelectQuery, Connection);

            Adapter.Fill(CSVDataTable);

            return CSVDataTable;
        }
    }

The exact problem is if a csv column contains a data, which is highlighted in bold letters,shown in Row1 below

Row1-> col1,"cdwdf" dsdfs,col2,col3

the col2 and col3 (columns after the highlighted text) are skipped while fetching the data using the above code and it continues with fetching data from the next row.

If the mentioned column text in Row1 is fully within quotes ("cdwdf dsdfs") the data is fetched correctly.

Any one please tell me how to fetch data from csv in such a situation...

Double quotes are a part of csv specification. If you have data that contains double quotes, than the entire field (or column) must be enclosed in double quotes and any double quotes inside the field must be escaped using double double quotes.

So your line should read like this:

Row1-> col1,"""cdwdf"" dsdfs",col2,col3

I haven't used any CSV libraries so I can't recommend any, but you could easily parse the file yourself. Just read the file line by line and split by ','. Problems with this are fields that span multiple lines....

EDIT: So to sum it up you'll need to modify your CSV input file or find a parser that is more forgiving or that will at least throw an exception when it finds a malformed CSV record. At first glance FAST CSV reader others suggested seems like a good place to start as it claims that malformed CSV causes it to fail with a meaningful exception.

I would use Fast CSV Reader as it is quite fast and good at identifying csv file structure.