If you have powershell available, just paste this into a powershell command prompt: foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator()) { $v = New-Object PSObject for ($i = 0; $i -lt $provider.FieldCount; $i++) { Add-Member -in $v NoteProperty $provider.GetName($i) $provider.GetValue($i) } $v } Credits and more advanced usage: http://dbadailystuff.com/list-all-ole-db-providers-in-powershell
Adodb (ActiveX Data Objects DB) is an API layer over OLE DB. It works well with MS-based databases such as Sql Server, providing a consistent API and optimizations. That being said you can use ADODB to connect with non-MS data sources as well but that would mean that you will require an OLEDB/ODBC Provider for … Read more
ended up using this: using (OleDbConnection conn = new OleDbConnection(connString)) { conn.Open(); dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, “TABLE” }); Sheet1= dtSchema.Rows[0].Field<string>(“TABLE_NAME”); }
The following method has proven useful for me. It’s super quick and practical and doesn’t require PowerShell: Open up Notepad and create an empty text file, then click File -> click Save -> and save it with the File name: TestConnection.udl to your desktop. Go to your desktop and double-click on the TestConnection.udl file you … Read more
Using .Net 4.0 and reading Excel files, I had a similar issue with OleDbDataAdapter – i.e. reading in a mixed data type on a “PartID” column in MS Excel, where a PartID value can be numeric (e.g. 561) or text (e.g. HL4354), even though the excel column was formatted as “Text”. From what I can … Read more
Basically, if you’re on a 64-bit machine, IIS 7 is not (by default) serving 32-bit apps, which the database engine operates on. So here is exactly what you do: 1) ensure that the 2007 database engine is installed, this can be downloaded at: http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en 2) open IIS7 manager, and open the Application Pools area. On … Read more
There are many articles about writing code to import an Excel file, but this is a manual/shortcut version: If you don’t need to import your Excel file programmatically using code, you can do it very quickly using the menu in SQL Server Management Studio (SSMS). The quickest way to get your Excel file into SQL … Read more
Take a look at Linq-to-Excel. It’s pretty neat. var book = new LinqToExcel.ExcelQueryFactory(@”File.xlsx”); var query = from row in book.Worksheet(“Stock Entry”) let item = new { Code = row[“Code”].Cast<string>(), Supplier = row[“Supplier”].Cast<string>(), Ref = row[“Ref”].Cast<string>(), } where item.Supplier == “Walmart” select item; It also allows for strongly-typed row access too.
Can you not just loop through the sheets from 0 to Count of names -1? that way you should get them in the correct order. Edit I noticed through the comments that there are a lot of concerns about using the Interop classes to retrieve the sheet names. Therefore here is an example using OLEDB … Read more
Install the following to resolve your error. 2007 Office System Driver: Data Connectivity Components AccessDatabaseEngine.exe (25.3 MB) This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2007 (*.mdb and .accdb) files and Microsoft Office Excel 2007 (.xls, *.xlsx, and *.xlsb) … Read more