Saving Excel Spreadsheet using ADO.net gives inconsistent results

Question

I wanted to be able to interrogate an Excel spreadsheet and programmatically save all the worksheets as text files (.csv). - I succesfully managed to do this using the .NET COM interop Excel collection of interfaces but even though this works beautifully I thought it would be nice if I could achieve the same results using the ADO.net classes.

For the purpose of this query please regard an individual Exel worksheet as a 'table'.
Having coded this I find that the Jet4.0 Excel Database engine in conjunction with the ADO datareader object, does not interpret the 'table' in the same way as the COM interop interfaces. The problem being that where a cell contains a "," (comma) the ADO datareader interprets that cell as two cells.

Eg "16, The Ridings" is interpretted as two cells even though when viewed in Excel it is in one cell. This means that when I save the file as a comma delimited CSV some of the rows have more cells than others - I am unable to figure out how to override this undesirable behaviour and wondered if anyone has come across this problem (and solved it)

Here is the basics of the code:

using (DbConnection connection = factory.CreateConnection())

{

connection.ConnectionString = conn;

using (DbCommand command = connection.CreateCommand())

{

//Create a directory based on the Spreadsheet name to store the csv versions of the worksheets

string newDirectory = _fileNameandPath.Remove(_fileNameandPath.LastIndexOf("."));

if (!Directory.Exists(newDirectory))

Directory.CreateDirectory(newDirectory);

foreach (string workSheetName in _workSheets)

{

//Create a csv file for this worksheet

StreamWriter sw = File.CreateText(newDirectory + "\\" + workSheetName + ".csv");

// Worksheets are referenced by their worksheet names

// We require all rows from the worksheet

command.CommandText = "SELECT * FROM [" + workSheetName + "$]";

if (connection.State != ConnectionState.Open)

connection.Open();

StringBuilder dataLine = new StringBuilder();

//Populate the DataReader with data from the worksheet

using (DbDataReader dr = command.ExecuteReader())

{

while (dr.Read())

{

//Reset the string builder

dataLine.Remove(0, dataLine.Length);

dr.

for (int i = 0; i < dr.FieldCount; i++)

{

dataLine.Append(dr[i]);

if(i != dr.FieldCount - 1)

dataLine.Append(",");

}

sw.WriteLine(dataLine);

}

sw.Close();

}

}

}

}