Sep 22, 2014

Read from and write to Excel with Database components

Our current Excel components use Excel Automation to work with Excel documents. This allows us to run macros and support almost any scenario but is not ideal for server environments. If you don't need to run any macros a better option will be to use the Database components to read from and write to Excel. Here are some tips to get you going.

  1. Download and install the Microsoft Access Database Engine Redistributable. This will install the OleDb drivers required to work with Excel through a database component.
  2. Use the DatabaseRead and DatabaseWrite components when reading or writing to an Excel document.
  3. The connectionstring for reads looks like "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\temp\write.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"
  4. For writing change IMEX=1 to IMEX=3.
  5. If you do not have headers then HDR=NO.
  6. For older files you can change the Extended Properties of the connectionstring.
  7. You can get more connectionstring information here.
  8. It seems as if a blank document must be initialised with a header for every row you want to write to. Just make it a space if you don't have headers.
  9. Read from a sheet: "select * from [Sheet1$]". Remember the $.
  10. Read from a range: "select * from MyRange" or "select * from [Sheet1$A1:B10]".
  11. Insert into a sheet with headers: "insert into [Sheet1$] (id,name) values('123','John')".
  12. If you don't have header use F1, F2, F... to refer to fields e.g. "select F1, F3 from [Sheet1$]".
  13. It supports other sql clauses like UPDATE and WHERE as illustrated here.
Some more info:


  1. Thanks of sharing this post…Python is the fastest growing language that helps to get your dream job in a developing area. It says every fundamental in a programming, so if you want to become an expertise in python get some training

    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery

  2. Bandicam crack is a lightweight screen recorder for Windows that can catch anything on your PC screen as a great video.I has been moreover deferential. exact play-success! Bandicam Serial Key

  3. May my adoration be your glow on this unique day, I am happy to the factor that you are right here. Upbeat Christmas, My One and simplest! I agree with Santa .
    Christmas Wishes for Wife