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:

4 comments:

  1. Replies
    1. Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. big data projects for students But it’s not the amount of data that’s important.Project Center in Chennai

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Corporate TRaining Spring Framework the authors explore the idea of using Java in Big Data platforms.

      Spring Training in Chennai

      The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
  2. 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

    ReplyDelete
  3. 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

    ReplyDelete