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

14 Comments

  • THANK YOU SO MUCH! I was having the hardest time figuring out how to load the first sheet without asking for the name of the sheet. This is great.

  • Hi,



    One bit of strangeness that I'm noticing. I'm writing a utility that copies an Excel file and then opens the file to both read and then update that file through the OleDb objects. The strange thing is that when I programmatically copy the Excel file and get the schema table for the copy, the worksheets are in a different order than the original Excel document. However, when I open the copy in Excel, close it, and then re-run my application, the worksheets are in the correct order again.



    Any thoughts?

  • IF the file is in a remote machine. How to give the path

  • I'm having problem opening csv files whose name contain '-'. Can anyone suggest workarounds for this problem?

  • try to use square brackets around sheet name (or file name in case of csv) if there are special characters in the name.

  • You may not be able to rely on WorkSheetNumber as given in example. Reason - Meta data information retrieved to get Worksheet names is sorted alphabetically. Does any one know a way to get the list of sheet names with out any sorting applied so that I can rely on index number to find a specific sheet?

  • Works fine except for file that contain certain characters, eg file12.03.04.csv will not work even with square bracket around it

  • This is an excellent and graceful example of something that many people are asking for but do not know how to do. Well done, this will certainly be something I keep in my pile of notes.



  • Hello Friends,



    One query regarding conversion of excel sheet data to dataset, that is If i need to pick only few columns from excel sheet then how do I do that?



    Currently we do

    @"SELECT * FROM "+SpreadSheetName;

    but if i want only say 1,3 and 5 col from excel sheet and put them in type dataset how should i do that.



    Thanks



    Regards,

    Vivek Jain

    +91-985-021-1094

  • Anyone sort out the issue with non alpha characters in the filename? - # . etc don't work.



  • Do you have this code in VB.NET?

    RGDS

    Sam

  • Did you notice that loading an excel sheet with ole causes numeric values in string columns to result as null? is there a solution for this ?

  • hi



    this code helped me lot, for getting excel sheet name



    thanks

    kvreddy

    singapore

  • has anyone seen setting HDR=NO yet the first row of the CSV is still treated as column headers?







Comments have been disabled for this content.