DonXML Blog

The East Coast Don

Converting Excel Worksheets and CSV files to a DataSet

I’ve been working on a bunch of cool new code that I’ll be releasing soon, but here’s an odd ball code snippet that I recently had to create:

I needed to open an Excel spreadsheet and then load the data into a database.  I found some good examples using OLEDB’s extended properties to open both Excel file and CSV files, but the Excel solution required that you needed to know the WorkSheet name.  With a little searching I found out that you can pull the all the WorkSheet names by using the OLEDB GetSchema method.  With a little bit of corrections for ADO.Net the code turned out to be relatively easy:

private DataSet GetExcelWorkSheet(string pathName,string fileName,int workSheetNumber)
{  
  OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+pathName+@"\"+fileName+";Extended Properties=Excel 8.0;");
  OleDbCommand ExcelCommand = new OleDbCommand();
  ExcelCommand.Connection = ExcelConnection;
  OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);

  ExcelConnection.Open();
  DataTable ExcelSheets = ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,new object[] {null, null, null, "TABLE"});
 string SpreadSheetName = "["+ExcelSheets.Rows[workSheetNumber]["TABLE_NAME"].ToString()+"]";

  DataSet ExcelDataSet = new DataSet();
  ExcelCommand.CommandText = @"SELECT * FROM "+SpreadSheetName;
  ExcelAdapter.Fill(ExcelDataSet);
 
  ExcelConnection.Close();   
  return ExcelDataSet;
}

Here’s the same thing, but opening a CSV file:

private DataSet GetCVSFile(string pathName,string fileName)
{
  OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+pathName+";Extended Properties=Text;");
 OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM "+fileName,ExcelConnection);

  OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
  
  ExcelConnection.Open();

  DataSet ExcelDataSet = new DataSet();
  ExcelAdapter.Fill(ExcelDataSet);
   
  ExcelConnection.Close();   
  return ExcelDataSet;
}

DonXML
Posted: Aug 21 2003, 04:41 PM by DonXML | with 11 comment(s) |
Filed under:

Comments

No Comments