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 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. 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 (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);
//Build comma delimited string
for (int fieldCount = 0; i < dr.FieldCount; i++)
{
dataLine.Append(dr[fieldCount]);
//Exclude trailing comma from final field
if(fieldCount != dr.FieldCount - 1)
dataLine.Append(",");
}
sw.WriteLine(dataLine);
}
sw.Close();
}
}
}:rolleyes: