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. Well, basically you are targetting a set of rows. Since ‘where’ is supported, you can specify conditions as follows: update set colName = ” where ‘colName’ >= ‘LowerBoundValue’ and ‘colName’ <= 'UpperBoundValue'. Think of how may a Sql query look like and then transform it as per it's syntax. Hope, it helps. BTW, i'll try your syntax as well or may be you can try and publish your results here for others

      Like

  1. I have a Windows 7 machine 64-bit with a 32-bit install of Office. I am writing a web app to read in from an Excel file using the Microsoft.ACE.OLEDB.12.0 provider. I installed the 32-bit version, since my Office install is 32-bit. I changed my project properties platform target to x86. I made sure the application pool in IIS enabled 32 bit. I am still getting the error “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered in the local machine.” What else can I do?

    Like

  2. help me i am having a problem here is my connection string
    “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=purchasingdetails.xlsx;Extended Properties=Excel 12.0″
    i am able to insert one record into excel sheet. when i try to enter second record it fails an errors pop ups saying ” Cannot expand named range.” please help me to resolve this error

    Like

  3. I have a client requirement to import an xlsx file that has headers, but read the headers into the data. However this is comflicting with the number format of the data. When I set IMEX=1 then I am getting the pre-formatted version of the number (to two decimal places) and I want the actual cell value (more like 8 decimal places). When I take out IMEX=1 then I lose the headers values, they go to NULL. I’ve tried most combinations of the extended properties, but with no sucess. Annoyingly, if I happen to have the file open in the background, it works perfectly with IMEX=1, full numer value + header values.

    Like

      1. Thanks for the response. That sounds like it would have been a good idea though. I can’t even remember how I solved the issue it’s been so long.

        Like

  4. I have client machines using a SharePoint 2010 visual web part compiled and run on a x64 machine. Most of the clients accessing this are on 32bit machines or if they are on x64 machines they have 32bit Office, I have installed 32bit ACE OLEDB on these machines and I get this error:

    The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

    I am assuming this is because the compiled code is on a x64 machine and cannot find the x64bit ACE OLEDB driver on 32bit clients.

    Can you confirm if this is correct and do you have a solution to this? Otherwise I will look at other options.

    Like

    1. Interestingly enough I got the x64 version installed on a machine with 32bit using the /passive switch on the installer.

      Like

      1. Do you mean side-by-side installation of x32 and x64 of ACE OLEDB?
        I have tried this earlier but it simply refused. It even didn’t allow me to install x32 on x64 Office and vice-versa.
        Can you send me a screenshot of this installation, if possible. I’ll check with a team.

        Like

  5. Hi,
    I ran into a problem and hope that you can give some direction.
    I am reading an Excel file through a vb .net program and populate it into a datatable. I use ACE data provider to connect to the Excel file. The Excel file has 500+ columns and it seems I can only bring back 255 columns of data from the Excel file. Is it a way to bring back all columns?
    Thanks.

    Like

    1. Hello Garrett,
      Another person was facing the same issue. And what i suggested was to read the columns in partitions where each partition has <=255 columns.

      [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]

      Hope that it will help you.

      Like

      1. Yeah, this is my last resort if I can’t find a way to do it. Found a third party library when Googling that claimed they can bring in more than 255 columns, just wondering how they do it.

        Like

  6. Can you provide sample code to close Excel workbook.

    i have tried with workbook.close() and excelapp.Quit(). But doesn’t work..

    Like

Leave a Reply to Garrett Chan Cancel 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