Excel spreadsheet
Microsoft Excel is one of the most widespread office applications world wide. You can use data from any Excel workbook. To import data from an Excel workbook, select Excel spreadsheet as the data source type.
An Excel data source has the following settings:
File name
In the first field 'File name', you can browse for the Excel file you want to work with. You can also use an expression in this field to specify parameters to be used with the file, or give other commands.
Select a table
Most data sources are organized as tables. You can simply select all data from a table by selecting the option Select a table and selecting the appropriate table from the drop down list(see below).
By default Excel exports each sheet as a table. Rows in that sheet are objects and the columns contain information about that object.
To access specific regions of a sheet, define named ranges; you can access those as tables as well. If you want to define a name for a cell or cell range on a worksheet, follow these steps:
1. Select the cell, range of cells, or nonadjacent selections that you want to name.
2. Click the Name box at the left end of the formula bar. Name box.
3. Type the name that you want to use to refer to your selection. Names can be up to 255 characters in length.
4. Press ENTER.
Please refer to Excel online help for more information on naming ranges.
Join multiple tables
Using the 'Join multiple tables' query method you can combine data from multiple sheets.
Most data sources, are organized as tables. You can select data from multiple tables at once using this by selecting the option Join multiple tables and selecting the appropriate tables from the drop down list. See below.
For each selected table, you must specify the name of the table and the ID column.
The ID column is use to related rows in all tables.
Missing rows
This data source will return as many rows as it find in the first table.
For each row it will look for the first matching row in the remaining tables. If no such row can be found for a specific table, the columns of that table will return an empty value.
Specify SQL statement
Most types of data sources contain more data than you need in a particular merge. For example, an Excel sheet may contain all contacts for your company, but you only want to merge customers located in Europe. You specify what subset to use by defining a query based on the type of data source.
variaDoc works with SQL, or Structured Query Language, a standardized language for specifying database queries. Most data sources are compatible with this standard.
If you select 'Specify SQL statement' as the query method you can specify custom queries. See SQL Quick Reference for a quick SQL reference and links to resources.
SQL statements for selecting data typically look like this:
SELECT field1, field2 FROM table WHERE field1="Some value" | An SQL statement that fetches data from a database is generally referred to as a 'query' |
Example
A sample project that uses a Microsoft Excel workbook can be found in Samples\XLS in the installation folder. Just double-click the .exe file and run the sample.
Specify an XPath query
XML data sources use XPath to specify queries. Although its purpose is similar to SQL, the syntax is quite different. For example, the XPath to select all products from a particular brand type looks something like this:
/catalog/products[@brand="Special Brand"]Queries are expressions
Both SQL statements and XPath queries are expressions. This means that you can include both data references and VB.NET script.
While merging, variaDoc will first evaluate the expression, resulting in a query. This query is then processed. The result is used as data for the merge. If the resulting query is invalid an error will occur.
| If a query for a data source is valid but has no rows, variaDoc will skip over all the items (pages, documents etc.) below the data source. A warning is generated during merging. |