CLR - OLEDB [reverse test]

[stanl]

The last thread looked at exporting An Access Table to Excel. The script below is essentially the same only reverses the process, now INSERTING data from an Excel Tab into an Access Table. The files used are in the attached zip.

Code (WINBATCH) Select Expand


;Winbatch 2013 - CLR - OLEDB: Insert Excel Data Into Access
;
;USES: Test.accdb and test.xlsx (both included in zip file)
;
;REQUIRES: ACE OLEDB Provider (installed with Office 2007/2010)
;          .NET Assemblies 4.0
;
;TESTED: Win7 32-bit
;
;Stan Littlefield July 17, 2013
;////////////////////////////////////////////////////////////////////////////////////////////////////////
;do required files exist
cACCDB = Dirscript():"test.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue","Missing Database File: ":cACCDB)
cXLS = Dirscript():"test.xlsx"
If ! FileExist(cXLS) Then Terminate(@TRUE,"Cannot Continue","Missing Excel File: ":cXLS)
;create Display Window
BoxOpen("Please Wait","Inserting Excel Sheet Into Access...")
;create connection string and initiate Provider
cConn = 'Provider=Microsoft.ACE.OLEDB.12.0;data source=':cACCDB  ; cannot use ';Connect Timeout=30;'
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oProvider = ObjectClrNew("System.Data.OleDb.OleDbConnection",cConn)
oProvider.Open()

;issue "SELECT INTO" with Command Object to create Excel Tab via Office ISAM Drivers
;NOTE: the SQL will create a new tab named DOW, but if that Tab already exists either an error will occur
;      or a DOW1 tab will be created.
oCommand = ObjectClrNew("System.Data.OleDb.OleDbCommand")
oCommand.Connection = oProvider
oCommand.CommandText = 'INSERT INTO [DOW] SELECT * FROM [DOW$] IN "" [Excel 12.0 Xml;DATABASE=%cXLS%;HDR=YES;IMEX=1];'
oCommand.CommandTimeout = 30
oCommand.ExecuteNonQuery() ;no need to return a recordset object
;recover memory before exiting script
oCommand.Dispose()
oCommand=0
oProvider.Dispose()
oProvider=0
BoxShut() ;close Display Window
Exit
;/////////////////////////////////////////////////////////////////////////////////////////////////////////////   



[Deana]

Stan,
Thanks for sharing your code. I posted in the tech database, in hopes that it will help the next user.
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/dotNet/System_Data+Insert~Excel~Data~Into~Access.txt

[stanl]

Stan,
Thanks for sharing your code.

Also of Note:  The CLR is quite fast... in fact for a test with 50 columns and 10,000 rows it seemed to run a bit faster than a similar script using COM. Once I break down the Objects to udfs with parameters to replicate my current COM lib udfs I may use the CLR exclusively for Win7 machines.