ODBC Driver issue for.xlsx files in the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) ODBC driver.

Hi, I've tried everything I can think of and find online (see below) about this subject to no avail. My Windows 8.1 Surface Pro 3 does not have the.xlsx ODBC driver, even when I look it up in%windir% SysWOW64 odbcad32.exe. It only has the driver for.xls, which will eventually corrupt the.xls file used for the database after a few uses. On the Drivers tab in the ODBC Data Source Administrator, it says: 'To install new drivers, use the driver's setup program.' Where is this driver setup program and where can I download the.xlsx driver? Also, I just reformatted and started over again, re-installing Office 365 (includes Access) and everything else. It's a 64-bit Windows system and 32-bit Office.

Please advise! -- I've already tried the advice listed in the links below without any success.

Why Use ODBC? It is reasonably easy to insert data from Excel into SQL Server, or the reverse, from any other ODBC database to any other, using PowerShell. The most important direction is from Excel to SQL Server, of course. It is quicker than automating Excel and you can do it without requiring a copy of Excel. It is neater than SSIS too, and more versatile. The most important thing, though, is that you can aggregate before you send the data. It is possible to do a lot of filtering and aggregation of data before it ever gets to SQL Server, since you can turn an existing Excel Workbook into a poor-man’s relational database, or even create one.

This article will aim to show how this is done. I always feel slightly awkward in talking about ODBC. Anu script manager for windows 10 64 bit free download.

It is a Once and Future technology, developed before its time, but now showing its value for processing large volumes of data, despite its quirks, poor documentation and lackluster support. If you use the ODBC driver, then your Excel workbook becomes a little SQL-based relational database. Worksheets, or areas within worksheets, become tables. There are some features missing, of course, but you can do joins between tables, filter rows to taste, do aggregations and some string manipulations. This means that you need pull far less data into SQL because you can do a lot of selection and pre-processing before the data gets anywhere near SQL server. If, for example, you only need the total, count, and variance of a day’s readings, then why on earth would you want to import more than those aggregated figures?

Even if you do, these aggregations, performed on the original data, can be used as a ‘reconciliation’ check that you’ve gulped all the data into their final destination without error. I also prefer to use ODBC and the sequential data reader to read data from Excel, or any other ODBC source, because it is fast; and I like to use the bulk copy library to insert ODBC ‘reader’ data into a SQL Server table because it is extremely fast, so we’ll use that. When you have a large number of big spreadsheets to insert as a chore, then speed matters. The ODBC Excel driver (ACE) ODBC was conceived as a way of making it as easy to connect to a particular datasource such a relational database, text file, data document (e.g. XML), web-based data or spreadsheet Currently, the state of the art in ODBC for Access and Excel is the Microsoft Access Database Engine 2010 Redistributable which.

This includes the more popular OLEDB drivers which. These drivers enable you to access a range of data files via SQL as if they were a relational database. Formats include Access, CSV, delimited, DBase and Excel For developing on a general-purpose 64-bit desktop computer, you’re likely to hit a very silly Microsoft muddle, since many of the common Office Add-ins did not run in the 64-bit Office environment. This advice has become baked-in ‘best practice’., as most of us are, then you need to use the 64-bit version of the drivers. If you only have the 32-bit Office on your machine, then it will already have the 32-bit drivers, which won’t be visible to 64-bit PowerShell, and won’t work. You can’t install the 64 bit drivers when you already have the 32-bit drivers and I don’t think you can get anything good to happen by uninstalling the 32-bit drivers.

All three (or four if you include Visual Studio) must be 64 bit. I gather that one way out of this Catch 22 is to first install the 64-bit Office 2010 ODBC/OleDB drivers and after that the (32-bit) Office, but there is a better fix that involves tweaking the registry. See this for the. The ODBC Excel driver in ACE works with the latest Excel spreadsheet format up to 2010 as well as the old ones. I suspect that the latest version will work with Office 2013, though I haven’t yet tried it. This driver is valuable because of the flexibility it gives. It actually executes ODBC SQL, which is a subset of SQL92, so you can put in column aliases, change the column order, and filter out rows that you don’t want to import.