I have decided to place all code in articles at my English site and refer to them in the blogpost instead of trying to ‘formatting’ the code here.
My good local friend Stefan Holmberg had the kindness to give me some additional input about ADO.NET 2.0. In return I have updated the sample in use where the method UpdateBatchSize of the SQLDataAdapter allow us to add all records in a batch mode to the database.
This post is about how we can:
- Retrieve data from closed workbooks (Excel 2007) with a new Provider and
- Update a SQL Server 2005 EE (Express Edition) database with the retrieved data.
With the release of Office 2007 the Jet OLEDB Provider has been replaced with a new Provider. The new Provider’s name is “Microsoft ACE OLEDB 12.0” and there is the following good news with it:
- The 255 charachter limitation is removed.
- It reads both Excel 8 ‘source databases’ (version 2000/2002 (XP) and 2003) and Excel 12 (version 2007) ‘source databases’. It means that the provider can read workbooks in the following file formats XLS, XLSX, XLSM and XLSB.
The provider works with both classic ADO and with ADO.NET. Since this blog is about .NET and Excel I decided to create an example (available on my English site) where we use ADO.NET and with untyped Datasets:
The use of Datasets can be discussed but here I find it to be a rather smooth solution, especially when using ‘local’ dataset where the data from it is merged into a ‘public’ dataset.
At present I’ve not yet find any sources that explicit discuss the provider and Excel workbooks as ‘database sources’. Please let me know if You know of any source related to the subject.