Specifying Range in another form

How to read or write excel file using ACE OLEDB data provider?


This article describes the way to read or write into the excel workbook(or a file, used interchangeably) pro-grammatically using C#.NET language and ACE Oledb data providers by Microsoft.
This covers the following topics:

  • System Requirements
  • Development Environment
  • Versions of Excel files which can be read or written
  • How to build a connection string?
  • How to build a command string?
  • Possible errors and exceptions

System Requirements
To read/write the excel worksheet using ACE oledb providers, MS office need not to be installed on a machine. An installable package containing ACE oledb providers can be installed from ACE OLEDB Installer Location
Go to this link to install the required version and also check the system requirements.
Note: You can install either 32 bits version or 64 bits version but not both. Also, if you have 64 bits office installed then you can’t install 32 bits ACE oledb and vice versa.
Check the requirements carefully on the page.

Development Environment

  • ACE 12.0 oledb Data Providers dlls
  • Development IDE – Visual Studio [or simple Notepad]

Versions of Excel files which can be read or written
This ACE 12.0 oledb data provider can carry out operations on all excel files till version 2010.

How to build a Connection String?
A typical example of connection string:
Provider=Microsoft.ACE.OLEDB.8.0;Data Source=;Extended Properties="Excel 8.0;"

Below is the description for each part:

  1. Provider : It is the main oledb provider that is used to open the excel sheet. This can be Microsoft.Jet.OLEDB.4.0 for Excel 97 onwards Excel file format or Microsoft.ACE.OLEDB.12.0. So far it is tested with upto Excel 2010 version.
  2. Data Source : It is the full path of the excel workbook. Replace with the full path of your existing excel workbook/ file.
  3. Extended Properties (Optional) : Extended properties can be applied to Excel workbooks which may change the overall activity of the excel workbook from your program. The most common ones are the following :
  • HDR : It represents Header of the fields in the excel table. Default is YES. If you dont have fieldnames in the header of your worksheet, you can specify HDR=NO which will take the columns of the tables that it finds as f1,f2 etc.
  • ReadOnly : You can also open excel workbook in readonly mode by specifying ReadOnly=true; By Default Readonly attribute is false, so you can modify data within your workbook
  • FirstRowHasNames : It is the same as HDR, it is always set to 1 ( which means true) you can specify it as false if you dont have your header row. If HDR is YES provider disregards this property. You can change the default behaviour of your environment by changing the Registry Value- [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\FirstRowHasNames] to 00 (which is false)
  • MaxScanRows : Excel does not provide the detailed schema defination of the tables it finds. It need to scan the rows before deciding the data types of the fields. MaxScanRows specifies the number of cells to be scanned before deciding the data type of the column. By default the value of this is 8. You can specify any value from 1 – 16 for 1 to 16 rows. You can also make the value to 0 so that it searches all existing rows before deciding the data type. You can change the default behaviour of this property by changing the value of [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows] which is 8 by default.  Currently MaxScanRows is ignored, so you need only to depend on TypeGuessRows Registry value. Hope Microsoft fixes this issue to its later versions
  • IMEX : (A Caution) As mentioned above excel have to guess a number or rows to select the most appropriate data type of the column, a serious problem may occur of you have mixed data in one column. Say you have data of both integer and text on a single column, in that case excel will choose its data type based on majority of the data. Thus it selects the data for the majority data type that is selected, and returns NULL for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.
    For e.g., In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
    To work around this problem for data, set “IMEX=1” in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting.  You can change the enforcement of type by changing [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes] to numeric as well

Thus if you look into the simple connectionstring with all of them, it will look like:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\testexcel.xls;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE\""

or

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\testexcel.xlsx;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE\""

How to build a command string?
If you have ever written a MS SQL query then you will not have difficulty in writing a command string. Here, command strings are written or defined in the same fashion as in MS SQL.

Think of each excel sheet as a MS SQL table from which data is to be fetched. Thus, complete excel file as a database.
Below image is the snapshot of a sample excel sheet which we will try to read or write into.
Sample of an excel sheet

Read Command String
In order to read the excel sheet, “SELECT” command is used. Either you may want to read the complete excel sheet or you may be interested in reading just a block of data. Both scenarios are supported and this is what us different from defining the MS SQL “Select” query.
Range (block of data) is defined using A2:R5 format.
Don’t worry about ranges. I have described it in the below section.

  • How to read complete excel sheet?
    Below is the sample command string to read the complete data in an excel sheet:

    Select * from [NameOfExcelSheet$]

    Here, NameOfExcelSheet is the name of an excel sheet. Excel file can have multiple sheets. So, one has to specify the name of the sheet in order to fetch the data.
    This command string reads the complete excel sheet.
    Note: This may read empty rows, columns as well if there is any hidden data or there are empty rows in b/w the filled data.
  • How to specify range to read a block of data from excel sheet?
    Below is the sample command string to read the complete data in an excel sheet:

    Select * from [NameOfExcelSheet$A1:E5]


    Here, NameOfExcelSheet is the name of the excel sheet. Excel file/workbook can have multiple sheets. So, one has to specify the name of the sheet in order to fetch the data.
    This command string reads the complete excel sheet.
    And, text after ‘$’ sin specifies the range of data to be read. Thus, as per the above command string, it will read the data from the excel cell A1 till E5. In other words, it will read 5 rows from row# ‘1’ to ‘5’ and 5 columns from column# ‘A’ to ‘E’. This can be beneficial in reading the data in chunks which can be easily achieved by dynamically specifying the range in the above format.
    Another way to specify range is:

    Select * from [NameOfExcelSheet$A1:E]
    Specifying Range in another form

Here, as per the above command string, it will read all rows starting from row# ‘1’ and 5 columns from column# ‘A’ to ‘E’

Write Command String
This also corresponds to DML queries (INSERT,UPDATE and DELETE) in MS SQL.
You can write:

INSERT INTO [NameOfExcelSheet] VALUES('firsttextcol', 2, '4/11/2009');
[I assume First Column is char field, 2nd col is integer, 3rd is Date]

DELETE FROM [NameOfExcelSheet] Where secondintcol=2;

UPDATE [NameOfExcelSheet] SET secondintcol = 3 where firsttextcol = ‘firsttextcol’;

As in MS SQL, you can use [] (Square brackets) to allow spaces within column names and table names.

How to create an excel worksheet?
Simple. Use

Create table [NameOfExcelSheet] ()

For e.g. Create table [myTableName] (col1 int, col2 char(20))

How to Drop an excel workSheet?
To drop an excel worksheet, just use

Drop table [NameOfExcelSheet]

This will drop the worksheet.
Note:

  • About Deleting Worksheet:
    If this is the last worksheet, it will not delete the workbook file. You need to do it yourself.
  • About specifying ranges:
    When you specify a worksheet as your source, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range. Using MDAC you cannot add new rows beyond the defined limits of the range, otherwise you will receive Exception: “Cannot expand named range”

Possible errors and exceptions

  • 1)”Microsoft.ACE.OLEDB.12.0-provider” is not registered
    Reason:This is because of mismatch b/w compilation configuration in Visual Studio and the ACE version installed on the system.
    Resolution:Make sure that both are same. Compile your app using the 64x bits if 64 bits ACE is installed or use 32 bits ACE.

 

SPONSORED


Hotels.com CPA

DRESS YOUR HAIR Like Never Before. Coming to YOU in United States By GKHAIR
GKhair-US
GKhair Hair Tamer

47 thoughts on “How to read or write excel file using ACE OLEDB data provider?”

  1. After all the issues like ACE.OLEDB.12.0 is not installed in your machine and could not find installable isam , i am getting another error “Cannot modify the design of table ‘Persons’. It is in a read-only database.” . I am tring to create table in Excel sheet here is the code m using

    string con2 = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source= ” + flpath + “;Extended Properties= \”Excel 12.0;HDR=YES ;IMEX=1;READONLY=FALSE\””;

    OleDbConnection mycon1 = new OleDbConnection(con2);

    mycon1.Open();

    OleDbCommand cmd2 = new OleDbCommand(“CREATE TABLE Persons( P_Id int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) )”,mycon1);

    cmd2.ExecuteNonQuery();

    Like

  2. How to fix this problem:

    could not find installable isam oledbexception

    with database connection string:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + Server.MapPath(fileName) + “;Extended Properties=Excel 12.0;HDR=NO;IMEX=1

    Thanks in advance.

    Like

    1. The cause is “HDR=No;IMEX=1″ definition in the connection string without double quotes.
      Modify your connection string to:
      Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + Server.MapPath(fileName) + “;Extended Properties=\”Excel 12.0;HDR=NO;IMEX=1\”

      Note: Extended Properties are wrapped under double quotes “”

      Like

  3. i am uploading excel sheet into sqlserver using OLEDB but my problem is…
    If that excel contains Heading like EMPLOYEEINFO in first row and respective rows are fields(2ndrow) and data from 3rd row.. that i am not able to get fileld names conatined row number..
    I putted it is by default 1 so if we give fieldcolumns in first row it is working fine.. can u please tell me how to identify perticular row wich contains field names.

    Like

    1. Are you saying that first row contains only EMPLOYEEINFO (i assume it’s the table name) and second row contains the name of all columns?
      If the above assumption is correct, you can read the second row at first to determine the fields’s names, the same way you would do for reading data.

      Like

  4. Hello there, simply turned into aware of your blog thru Google, and found that it is really informative. I’m gonna watch out for brussels. I will appreciate if you continue this in future. A lot of other folks will be benefited from your writing. Cheers!

    Like

  5. Hi Sunil,

    I updated a DataGridView by reading data using OLEDB. Now I updated the data in DataGridView and I want the information to be reflected back to Excel. I need to update one row at a time…

    Is there any simple solution for it?

    Regards,
    Shaik Ameer Basha

    Like

  6. I used to be recommended this web site by means of my cousin. I am no longer positive whether or not this publish is written by way of him as no one else understand such certain approximately my trouble. You’re wonderful! Thank you!

    Like

    1. using (OleDbConnection oledbConnection = new OleDbConnection(connectionString))
      {
      oledbConnection.Open();
      OleDbDataAdapter oleda = new OleDbDataAdapter(“SELECT * FROM [” + this.sheetName + “$A5:O] “, oledbConnection);
      // Schema dataTable
      DataTable schemaDT = new DataTable();
      oleda.FillSchema(schemaDT, SchemaType.Source);
      foreach(DataColumn column in schemaDT.Columns)
      {
      string columnName = column.ColumnName;
      Type colDataType = column.DataType;
      }
      }
      This way, you can get the complete information of all Columns for the Section of Data (when a range is specified) in an excel workbook.

      Like

    1. Each sheet is nothing but a table. So, the below line can get you all worksheets aka tables.
      DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

      then, iterate over each row to get names,

      foreach (DataRow row in dataSet.Rows)
      {
      Console.WriteLine( row[“TABLE_NAME”]);
      }
      Note that each worksheet name will have ‘$’ appended.

      Like

      1. In my scenario, i had to read columns < 255. So, didn't face this problem. But yes, this is a problem with a provider which reads only 255 columns at a time. What i would suggest you is that (though a dirty approach), you can read the data in chunks. I mean, do the "Vertical Partitioning" of your data such that each partition contains at max 255 columns. Read each partition and then merge it with the earlier results

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s