Import xls file into dataset

Import xls file into dataset

• Swapnil11a

  
  Using Josh's link above, I was able to work out a solution with the following code:
  

  using (CSVReader csv = new CSVReader(strFileName)) {     string[] fields;     DataTable oTable = new DataTable();     DataRow oRows;     oTable = ds.Tables[dataTableIndex];     int intCounter = 0;     while ((fields = csv.GetCSVLine()) != null)     {         oRows = oTable.NewRow();         foreach (string field in fields)         {             oRows[intCounter] = field;             intCounter = intCounter + 1;         }         intCounter = 0;         oTable.Rows.Add(oRows);     } }

  
  
  This code uses the CSV Parser available here:  http://www.heikniemi.net/hc/archives/000152.html
  
  The code also assumes the following variables are initialized
  

  string strFileName int dataTableIndex DataSet ds

  
  
  and that the following using statement is at the top for the parser class
  

  using JouniHeikniemi.Tools.Text;

  
  
  Perhaps someone else can extend this or has a better example. I'm pretty new to the .NET FCL and just wanted to contribute back a little in case it helps.
  
  Later,
  
  

• Lawrence of Sinking Spring

  We followed something similar to the following in the past with success:
  http://www.devcity.net/Articles/36/1/delimit_xml.aspx
  
  Let me know if you need additional assistance,
  Josh Lindenmuth

  
  
  

• pdclose

  Thoughts:
  
  First Approach
  I suppose you could export an Excel file data to a comma separated variable file (*.csv) and then read that data into your application as text.
  
  Second Approach
  One approach could be to use COM/OLE Automation to communicate with Excel.  Prior to .NET, I accomplished this with VC++ using the MFC, COleClientItem and COleServerItem, COleDispatchDriver.  Rather than invoking the methods directly, I imported the typelib using the wrapper classes provided by the typelib.  VBA macros can be executed in Excel from your application using this approach.  In my case, historically, more documentation was available for VB than for VC++; there may be more now for VC# as well.  My mission then was to export data from an application directly into Excel.  I exported data from my application to *.CSV files first.  I then, through COM calls, imported the data into Excel from the *.CSV files I created.  Through COM and VBA, I created additional Worksheets displaying various graphs of the data.  In your case, you are pulling data in the opposite direction.  You could also use a similar approach through COM and the OLE Automation typelib to automate exporting of the data to *.CSV files and then pull the data into your application.
  
  In VC#, you can access the typelib by adding a reference to the typelib.  In the Solution Explorer right-click on the project, left-click Add Reference, left-click the COM tab, and finally locate the Component Name, the Microsoft Excel 5.0 or 11.0 Object Library.  You will then have access to Excel COM Wrapper objects that communicate with Excel. 
  
  Third Approach
  However, a better alternative in creating a dataset for your case would be to use ODBC or OLE DB connectivity.  There should be providers that are supported for Excel and ADO .NET such as the ODBC .NET Data Provider, the OLE DB Provider for ODBC Drivers, or the Microsoft Jet 3.51/4.0 OLE DB Providers.  OLE DB Providers are built on top of COM technology.
  
  It is my understanding that most functionality provided by the OLE DB Providers but accessed through the use of an ADO .NET adapter is supported.  In rare cases, direct use of the OLE DB will provide additional functionality that ADO .NET does not provide or support.  See extract below:
  
  [Microsoft ADO .NET Step by Step, Ch. 2, Creating Connections, Page 24]
  
  Microsoft ActiveX Data Objects (ADO)
  
  "Since ADO.NET merges the ADO object model with OLE DB, it is rarely necessary to go to directly to OLE for performance reasons.  You might still need to use OLE DB directly if you need specific functionality that isn't exposed to ADO.NET, but again, these situations are likely to be rarer than when using ADO."
  
  [/Microsoft ADO .NET Step by Step, Ch. 2, Creating Connections, Page 24]
  
  Using this approach, for each provider you will need to determine the necessary connection string used to connect to the database.  After that, you should be able to find ADO .NET examples for making calls by way of ODBC or OLE DB.
  
  Links of Interest:
  
  [Google - "ADO .NET ODBC Excel"]
  
  How To Use the ODBC .NET Managed Provider in Visual C# .NET and Connection Strings
  http://support.microsoft.com/default.aspx scid=kb;en-us;310988
  
  Data Access and Storage Downloads
  http://msdn.microsoft.com/data/downloads/
  
  .NET Framework 1.0 ODBC Data Provider
  The ODBC .NET Data Provider is an add-on component to the .NET Framework. It provides access to native ODBC drivers the same way the OLE DB .NET Data Provider provides access to native OLE DB providers.   
  http://www.microsoft.com/downloads/details.aspx familyid=6ccd8427-1017-4f33-a062-d165078e32b1&languageid=f49e8428-7071-4979-8a67-3cffcb0c2524&displaylang=en
  
  Understanding ODBC .NET Data Provider
  By Mahesh Chand
  06/26/2003
  http://www.vbdotnetheaven.com/Code/Jun2003/2101.asp
  
  see Installing the ODBC .NET Data Provider
  
  The connection string for an Excel database looks like following:
  Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls< XML:NAMESPACE PREFIX = O />  
  
  [/Google - "ADO .NET ODBC Excel"]
  
  [Google - "Microsoft OLE DB Simple Provider Excel"]
  
  OLE DB Providers Overview
  
  OLE DB Providers Overview
  http://msdn.microsoft.com/library/default.asp url=/library/en-us/oledb/htm/oledbprovmicrosoft_ole_db_providers_overview.asp
  
  

  ODBC

  Microsoft OLE DB Provider for ODBC

  Exposes data traditionally accessed using ODBC.

  OLE DB Provider for ODBC
  http://msdn.microsoft.com/library/default.asp url=/library/en-us/oledb/htm/odbcproviderthe_odbc_ole_db_provider.asp
  
  

  Microsoft Jet database

  Microsoft OLE DB Provider for Microsoft Jet

  Accesses data from Access, Paradox, dBASE, Excel, FoxProR, and more.

  OLE DB Provider for Microsoft Jet
  http://msdn.microsoft.com/library/default.asp url=/library/en-us/oledb/htm/oledbprovjet_overview.asp
  
  OLE DB Provider for Microsoft Jet - Unsupported Interfaces
  http://msdn.microsoft.com/library/default.asp url=/library/en-us/oledb/htm/oledbprovjet_overview.asp
  
  [/Google - "Microsoft OLE DB Simple Provider Excel"]
  
  Locating OLE DB Providers
  1.) Go to your Server Explorer in Visual Studio .NET 2003.
  2.) Right-Click on Data Connections to find the Data Link Dialog
  3.) Select Add Connection
  4.) Go to the Provider Tab to find all of the Providers
  5.) Under Data Link Properties you will likely find the following providers:
  Microsoft Jet 3.51 OLE DB Provider
  Microsoft Jet 3.51 OLE DB Provider
  Microsoft OLE DB Provider for ODBC Drivers
  
  This should put you on the correct path for accessing data from worksheets (spreadsheets) within Excel workbooks or files.
  
  James M. Sigler, II
  Dallas, TX
  jmsigler2@hotmail.com
  
  
  
  
  
  
  
  

• pexxx

  Thanks for your advice   i am trying it now. Boris
  
  
  
• Import xls file into dataset