. 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) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
The 2007 Office System Driver are not intended:. As a general replacement for Jet (If you need a general replacement for Jet you should use ). As a replacement for the Jet OLEDB Provider in server-side applications. As a general word processing, spreadsheet or database management system; to be used as a way to create files.
(You can use Microsoft Office to create the files that Microsoft Office supports, or use development tools to create Office OpenXML compatible files.). To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.
This package contains components that are at the same version as the “The 2007 Microsoft Office Suite Service Pack 1 (SP1)” release. The Office System Drivers are only supported under certain scenarios, including:. Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files.
To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEYCURRENTUSER registry hive).
'When I attempt to create a new System DSN for an Excel 2010 Spreadsheet from within Impromptu, the above drive for the 'Create New Data Source' is unavailable. If I go to Control Panel/Administrative Tools/ Data Sources and attempt to create a System DSN, I have the above driver available. If I create a data source here, then, once I attempt to use it as a database for a new catalog in Impromptu, it is unavailable (it doesn't even show on the list of databases).
I am running Impromptu 7.5 on a Windows 7 64-bit installation. Any help would be greatly appreciated. This is working on another installation of the same version of Impromptu and Windows 7 with the difference being it is a 32-bit installation. Thank you for your time and assistance.'
Excel provides 29 different file types when you save your Excel workbook. When you save an Excel workbook for the first time or use the “Save As” function, you are asked for a folder, file name as well as the file type.
By default, Excel suggests the XLSX file type (unless your file doesn’t have VBA macros). The window looks similar to the screenshot on the right side. So which file type are you going to use? The answer – like so often: It depends.
Before we conclude in a decision tree, we we take a look at the most important file extensions in Excel with their advantages and disadvantages first. XLSX The “default” file extension is XLSX.
The large majority of Excel workbooks uses this format these days. Microsoft says in the Excel help text about the XLSX file format: The default XML-based file format for Excel 2007-2013. Cannot store Microsoft Visual Basic for Applications (VBA) macro code. Most of the time you use the XLSX format: It’s save (can’t store malicious code), has the maximum number of rows and columns and is best known. XLSX is available since Excel 2007 and replaces the old XLS file type.
It uses the open XML standard so which is documented well. Knowing these quick facts, there are the following advantages and disadvantages for the XLSX file format: Advantages Disadvantages.
The default file format and because of that the best known and widest spread file type. Can use up to 2^20 (1048576) rows and 2^14 (=16384) columns. Uses the open file format and is in general accessible with other applications. Secure because it can’t save VBA macros.
Can use and save all the functions available in Excel. A little bit contradicting: But not being able to include VBA macros can be an advantage and disadvantage at the same time. Needs more disk space than other file formats (e.g.
Can’t be opened with Excel versions before 2003 (e.g. 2003, at least without any further conversion of Excel extension/ add-in). XLS The XLS file type has been the default Excel file extension and format before Excel 2007. In Excel 2007, XLSX replaced the XLS format. Therefore, XLS is kind of outdated right now. In the daily life you won’t find it often any more.
But some data base tools still export data as XLS files. The XLS file type has some disadvantages towards the newer file types XLSX or XLSM: It’s rather unsafe as it can contain VBA macros with malicious code. Also, it needs more disk space than the other file types and at the same time has less rows and columns. On the other side the only advantage: You can easily open XLS files with older versions of Excel. XLSM If you include VBA macros in your XLSX file, you will be asked to change the file extension to XLSM.
Only that way you can save your macro within the Excel file (there are other file types possible, e.g. XLSB – but you can’t use XLSX for VBA macros).
Therefore, it has most of the advantages and disadvantages of the XLSX file type. Positive: You can immediately identify Excel files with VBA macros.
Install Microsoft Excel Driver (*.xls *.xlsx *.xlsm *.xlsb)
Advantages Disadvantages. The default Excel file type for workbooks with VBA macros. Can use up to 2^20 (1048576) rows and 2^14 (=16384) columns. Uses the open file format and is therefore accessible with other applications.
Can use and save all the functions available in Excel. Uses the open XML standard. Unsafe: Can contain malicious code within the VBA part. Needs more disk space than other file formats (e.g. Can’t be opened with Excel versions before 2003 (e.g. 2003, at least without any further conversion of Excel extension/ add-in).
XLSB Many people don’t know that there is an actual alternative to the XLSX and XLSM file type: The XLSB file extension. XLSB files store the data a little bit different than the XLSX or XLSM file types: They don’t use the XLM file structure. Instead, XLSB files try to save disk space because the data is stored in the binary structure. The main difference: Binary files are computer- but not human-readable. For more information on binary files in general, please refer to. The main disadvantage: Binary Excel files can contain VBA macros.
So unless you don’t know the origin of a file, please consider well before opening them. Besides that: All the other disadvantages seem minor. Advantages Disadvantages. Smaller file size. Faster opening and closing of files. Formulas with more than 8192 characters allowed. Can use and save all the functions available in Excel.
Security: Files can contain VBA code whereas XLSX files can’t. You can’t change the Excel ribbon.
Some third party tools (e.g. OpenOffice) might not be able to open your file. You can’t open your files with Excel 2003 or earlier (which nowadays should not be a problem any more). Not very well known. So people might get confused receiving XLSB files from you. CSV CSV stands for comma separated values. These files are basically plain text – so there are no formulas or formatting.
CSV is often used for data exchange. Excel provides the functionality to import or even directly open CSV files. The basic application for CSV files: Import or export data. Data base programs often export data as CSV files. Also if you want to transport Excel data into a database program, often the CSV file type works. Advantages Disadvantages.
Smaller file size. Faster opening and closing of files. Unlimited rows and columns. No code or macros possible and therefore save to open. CSV files are basically text documents and therefore can’t contain formulas or formatting, pure data. No more than one “worksheet”.
ODS ODS stands for Open Document Spreadsheet. The file type is designed to work on both Excel and other applications, e.g. From the Excel point of view, ODS doesn’t support all the features. Text and data is usually saved well though. But formatting and many other features (for example charts, tables, conditional formatting) might run into problems.
Advantages Disadvantages. Can be opened in Excel and LibreOffice. You might lose formatting or features which are not supported.
For a detailed lists of the features supported, please refer to. XLSX (Strict) Besides the regular XLSX format, Excel offers to save your Excel workbook as “Strict Open XML Spreadsheet”.
So what is the difference between those two? Basically, the two file types use the same structure.: The Strict variant has less support for backwards compatibility when converting documents from older formats.: The Strict variant of XLSX disallows a variety of elements and attributes that are permitted in the more common Transitional variant Advantages Disadvantages. Same as XLSX. Additionally: Opening in other applications than Excel might work better. Some backward compatibility problems might occur.
PDF As you’ve probably heard of PDF files, we won’t go much into detail here. PDF stands for portable document format and has the reputation of not easily being manipulated. Also, it’s quite save to say that how a PDF file looks on your computer, it will most probably look like this on most other computers. And that’s it for the advantages. I recommend using it when you present your final results in the following cases:. The recipient of your file should not edit it any more. Formatting should be exactly preserved.
There is no database included, just the summarized results. That could be the case if somebody wants to check your results on an iPad or a similar device. But: In many cases it would be professional (and nice) to also send at least parts of your Excel file.
That way, your recipient could at least take a look at the calculation process. Advantages Disadvantages. Static, therefore contents can not (easily) be manipulated. The document always look the same. Formulas etc.
Will be lost. Difficult (and troublesome) to further edit the file. (Depending on the contents) large file size. XLAM XLAM is the file extension for Excel add-ins. Also our add-ins (for example the popular ““) are XLAM files.
They can contain VBA macros, worksheets as well as forms, images and individual ribbons. If you double click on such file, it will open in Excel and you can use the funcationality of the add-in. But next time you open Excel again, it won’t be available any longer. You rather have to enable the add-in within the options (File – Options – Add-Ins). As the XLAM file type is not really an alternative for all the other file extensions above, we skip the advantages and disadvantages.
Comparison of file types So if we put all the advantages, disadvantages and facts from above together, we come to the following comparison. Follow the Professor Excel decision tree for finding the best file type for your Excel workbook. The first question: Do you just want to display your results only?
Then you could also consider the PDF format. But if the recipient of your work wants to edit or follow up your calculations, you shouldn’t use PDF. Does your file contain VBA macros?. If yes, choose XLSB if you have a large file.
Choose XLSM for a file size smaller than app. If no and you got a large file (larger than app. 10 MB), also choose XLSB. If no (you don’t have macros) and your file small, follow the tree on the right hand side.
The lower two levels of the decision tree are just about compatibility: If you want to open and edit your workbook with another software, choose the strict XLSX or ODF format. If you want to make sure that you can open it with versions of Excel 2003 or earlier, go for XLS.
Summary In conclusion, you can distill a quite short table (if you don’t need to consider old file types and other applications as LibreOffice). You just have to answer two questions: Do you need VBA macros and do you work with a large amount of data?
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. 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. In effect, it presents you with a SQL tables which can be named ranges, if it is an existing worksheet that you’ve added named ranges to. Select. from MyWorksheet $ C3: M8 In ODBC, if you specified, say, row 8 as the end of the table, you can only select rows up to row 8, even if you have inserted more rows beyond that limit, as ODBC allows.
If you use some flavours, such as the old MDAC ‘JET’ database engine, then you cannot add new rows beyond the defined limits of a range, otherwise you will get the Exception: 'Cannot expand named range' message If you wanted to define your table as being between the columns C and L, starting at row 3 you’d use. Select. from NameOfExcelSheet $ C3: M If you do this, then there is no limit to the length of the table so you can insert as many rows as you like. The ODBC provider adds new rows to the existing rows in the defined area as space allows The dreaded connection string Now, before we start doing interesting things with the ACE drivers, I ought to explain a bit about their connection strings. These contain the specification of the ODBC driver you wish to use, and the settings that you wish to transmit to the driver. Ignoring, for the time being, the extended property settings, For Microsoft Office Access data, set the Connection String to.
' Driver = Microsoft Access Text Driver. Csv ); DBQ = MyPath' But you’re likely to want some extended properties for the settings to add a few details about the way that the ODBC provider should tackle this particular connection. Because the defaults can be changed globally in the registry, it is rather better to specify these extended properties rather than to rely on the defaults. These extended properties are only relevant for the driver that you’re using. They are not always reliable and are poorly documented by Microsoft.
I’ll only mention the essentials. The driver needs to know if the first row of the table holds the name of the column. “HDR=Yes;” indicates that the first row contains column names, not data.
It will actually just use the first 64 characters of the header. “HDR=No;” treats the first row as data, but then the columns are named F1 onwards and you’d want to alias them in your SQL statements to give them meaningful column names.
The Excel ODBC doesn’t keep a detailed schema definition of the tables. (the Text and Access drivers by contrast do) The ODBC Excel driver will try to make sense of the data it finds by testing it to see what datatype it can use for the result. It does so by testing a number of rows before doing the import, and you can specify how many rows it tests before deciding the data type of the column by using MaxScanRows in the extended properties. By default the value of this is 8. You can specify any value from 1 – 16 for 1 to 16 rows.
You can also make the value to 0 so that it searches all existing rows before deciding the data type, but this slows things down. This is fine for a relational table but Excel often has mixed types in a column The ODBC Provider will try to return the data of the majority type, but return NULL values for the rest that won’t convert. If the two types are equally mixed in the column, the provider chooses numeric over text, and you lose all the text. Also, it will judge the length of the character datatype in the column from the first rows and if the first rows are less than 255 characters long it will truncate all the subsequent data to 255 characters even if cell values below are longer. By setting the Import Mode (IMEX=1). You can force mixed data to be converted to text, but only when it finds mixed values on the rows that it checks.
You can also open the Excel workbook in read-only mode by specifying ReadOnly=true; By Default Readonly attribute is false, so you can modify data within your workbook. However, this will lock the entire workbook from access until you close the connection. Let’s try it out. Just so you can prove all this to yourself, I’ve supplied an Excel workbook that represents the old PUBS database that used to be distributed with SQL Server and Sybase.
This means that you can use SQL from old examples that use PUBS and see what works. All you need to do is to convert the SQL Server version slightly by altering the names of the tables slightly to tell the driver that you want the entire worksheet of that name (the $ is the separator between the worksheet name and the range specification) So let’s pop together a very simple test-rig to try things out in PowerShell. Be warned, I’ve set this up in read-write mode so it will update your spreadsheet in some circumstances (CUD).
Employee handbook examples. Skills In this position, it pays to have People Management in your toolkit; employees who claim proficiency enjoy a higher median salary of about $12.31 per hour. Certifications and Degrees Salaries approach $12.50 per hour for Servsafe food manager certifications; non-accredited workers make much less.
To play along, you’ll need to download my Excel version of the PUBS database and alter the path to the excel file. SELECT aufname FROM authors $ UNION ALL SELECT lname FROM employee $ One could go on and on; even subqueries work, but I think I’ve made the point that there is far more power in this ODBC Excel driver than just the facility for pulling out raw data.
The same is true of the TEXT driver for OLEDB. It will do all this as well. To conform with the minimum syntax for ODBC, a driver must be able to execute CREATE TABLE, DELETE FROM (searched), DROP TABLE, INSERT INTO, SELECT, SELECT DISTINCT, and UPDATE (searched). SELECT statements can have WHERE and ORDER BY clauses. ACE does a bit better than this, since even the text driver allows SELECT INTO, and SELECT statements allow GROUP BY and HAVING. Creating a spreadsheet You can, of course use the ODBC driver to create an Excel spreadsheet and write data into it. Here is the simplest working demo I can write without blushing.
Be careful to ensure that the spreadsheet doesn’t exist as the whole point of the demo is to prove to you that it can create an entire spreadsheet workbook with several worksheets. GetSchema ( 'Views' ) Hmm. This is beginning to look a bit more like a database. With the Columns MetadataCollection, you can find out as much as you’d ever want to know about the data that is available in the spreadsheet so if you want to read all the worksheets straight into SQL Server, this is a wide-open goal. Creating Worksheets Going back to the PUBS Excel database, let’s create a peoples table and populate it with both authors and salespeople. This has to be done in three gulps since the driver seems to dislike the idea of doing a batch, and it kicks when I try to UNION the two results.
Close ( ) You’ll find you can UPDATE, INSERT and DELETE data perfectly happily this way. If you connect up a spreadsheet to a SQL Server database, then you can have a lot of fun copying entire databases into spreadsheets, and back again.
The problem is in the Workbook you create. Whether you name it XLS or XSLX it produces an XLSX spreadsheet, in the latest zipped.
The trouble is that, with my version of the driver, I can only get Excel to read it with the XLS filetype, since it says that there is an error if you try to open it as an.XLSX file. I suspect that the ODBC driver hasn’t been that well tested by Microsoft. Getting data into SQL Server from Excel using PowerShell Now, what about using PowerShell to copy the data, maybe filtered, sorted and aggregated, into SQL Server, using PowerShell and ODBC. In this direction we can save a lot of time by using the BCP library.
We’ll now describe the routine. We’ll keep this unpacked, as a script rather than a function, since this is designed to illustrate the process. We’ll start by defining our credentials, preferences, sources and destinations. We’ll read in the data from and excel spreadsheet and then spit it out into SQL Server, creating a table if necessary. To create the destination table (some of these spreadsheets are rather wide and therefore easier to import automatically), we’ll need to examine the metadata, and to interpret this to the SQL Server equivalent, so we’ll do that. To use the BCP library, it is good to have an indication of progress so I’ll show how you do that. I’ve provided the sample data so that you don’t have to scramble around to find something suitable.
This is some climate data, which is handy for checking things like date conversion. You will notice that although you can render numbers in a variety of ways, there is only one way of storing numbers in Excel, in the ‘ NUMBER‘ datatype (the other datatypes in Excel are LOGICAL, CURRENCY, VARCHAR and DATETIME). I’ve therefore had to specify the precision of numeric data, which is tough if you have some columns with integers and others with real decimal data with numbers after the decimal point (scale). Remember that this routine is just creating a staging table, not the final destination. All you need to do is to add your own statements to transfer the data to their final table with the CAST to the correct internal data type!
$PrecisionForNumericData =0 Try it. If you were doing this as a routine, you’d be wanting to wrap this script into a function with parameters by now, but you know how to do this already, I’m sure. I’m trying to give you the ‘workbench’ narrative here. Writing to Excel from SQL Server. The process of going from SQL Server to excel via ODBC is, I think, needlessly complicated, especially if you use parameterised queries (excellent for SQL Server but they add very little for writing to Excel).In this example, I’ll do the old and horrible approach of using insert statements.
There are other ways, including even using a dataset, but this is the most obvious. I’m not particularly happy with this sample because Excel whines a bit when it opens it, saying that it is in the wrong format, (which it is, but you try naming it XLSX) but it deigns to open it.
“The file you are trying to open, ‘MyExcelFile.xls’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?” More seriously, it complains that the numbers in the columns are ‘formatted as text’. It turns out that the data is saved in the correct format, but the next time the file is opened, all columns revert to varchar. Seasoned users of ODBC gets used to the bugs, but if anyone knows of a workaround to this, I’d be grateful.
Close ( ) CSV and Delimited ODBC Sources: Text AdventureWorks. Although the ACE drivers are used more by people reading Excel files, I must emphasize that there are drivers for a number of other formats.
It is pretty easy, for example, to turn a bunch of CSV files into a relational database. Just to prove it, I’ve created a CSV/Text version of AdventureWorks, together with its schema.ini.
This was originally created in this article. With this text-based database, you can do a lot of the sample AdventureWorks SQL examples with only a minor modification. Once you’ve installed the ACE drivers, you’ll can use a modified version of the routine I showed you or exploring the PUBS Excel database to play along. All you have to do is to nto a new directory with the name of your database (AdventureWorks) and point your connection string at the directory by giving it the full path to the directory.
I just altered two lines. Create table Log # csv ( MyInteger int, TheDate date TheMessage char ( 125 ) ) and do insert statements into it.
You can SELECT INTO as well, which is new to me. I didn’t notice this in previous incarnations of this driver.
With CREATE statements, you can use ‘ BIT, BYTE, LONGCHAR, CURRENCY, INTEGER, SMALLINT, REAL, FLOAT, CHAR or DATETIME (Out of curiosity, the OLEDB driver allows Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar char and Decimal). WHERE ProductModelID NOT IN ( 3, 4 ) Conclusions If only Microsoft put some energy into their whole range of ODBC drivers, including all the possible datastores that can be mapped to relational databases, they’d be the obvious way of transferring data, and would put Microsoft in great shape for providing ‘big data’ solutions. As it is, they are extraordinarily useful, but marred by quirks and oddities.
For me, ODBC is the obvious way to script data from Excel or Access into SQL Server, for doing data imports.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |