Open topic with navigation
Excel spreadsheets are a popular way to store and view small to medium-sized data sets, and data stored in Excel can be used in DMExpress tasks in the following ways:
DMExpress can access an Excel spreadsheet as a source/target by doing the following:
This article describes the ODBC method, specifically for Microsoft Excel 2010 and 32-bit DMExpress on either 32-bit or 64-bit Windows; it does not work on 64-bit DMExpress as there is currently no 64-bit ODBC driver for Excel.
This technique will not work on UNIX or Linux systems due to limitations of ODBC functionality on those platforms. For access to Excel data on UNIX / Linux, you must either export the data to CSV or use a free or commercial conversion utility.
To identify the data area to be accessed by DMExpress, you need to create a named range that defines that region in your spreadsheet. This may be any portion of a single worksheet; to use multiple sheets in a workbook, you will need to create named ranges for each and treat them as separate "tables".
Each defined named range will appear to DMExpress as a database table. The "column names" will be the values in the first (header) row of the range. The data types of these columns will be determined by examining the (sample) values in the second row of the range, just below the header.
To define a named range for a source, do the following:
The process to define a named range for a target is the same, but you will not select the full set of rows in which you expect the data to be written; instead, note the following:
Next, you need to create a database connection to your spreadsheet in DMExpress. If the same database is being used as both source and target, there is no need to redefine a database connection for the target; just be sure to enable the database connection for writing, as described in the following steps:
Now you can use the database connection to define your "table" as a data source and/or target. If the same database is being used as both source and target, you will see both named ranges in the Tables node described in the following sections; simply select the appropriate one for your source/target.
Note that when using an Excel spreadsheet as a target in DMExpress, only insert functionality is supported; update and delete are not.
For more information on specifying database sources/targets, see Source Database Table dialog and Target Database Table dialogin the DMExpress Help.
If you expect to receive similar Excel data sets often (such as daily or weekly) from the same data provider, it is a good idea to create a "template" file for this. Define the named range in this file, fill it with blank cells and give it to the data provider. Whenever it is time deliver a new data set, the data provider should make a copy of the template and insert the new values within the named range in the new copy. This will avoid the need to define the named region again.
Also, it is useful to have standard name and folder location for your Excel file. This will allow you to define your ODBC source only once, and re-use it each time you get new data. Whenever you get a new input file, move it to your standard location and re-name it (if necessary). This will avoid the need to create an ODBC source again.
Copyright © 2016 Syncsort All rights reserved.