Available In: TicketBench Pro, TicketBench Enterprise
By importing data, your project can have dynamic text, called data fields, that change from ticket to ticket as they are printed. For example, suppose you want your customers' names on the tickets you print. Data import allows you to open a file or database that contains the customer information and drop the fields (e.g. FirstName, LastName) on the ticket where you want them to appear.
To import data from a delimited file, click on the Data Import button in the toolbar. The Data Import window will open. Select the tab named 'Delimited File'. The 'Current File' area shows the file that has been selected to import from. It will show 'No File Selected' since we haven't chosen a data file yet. The area just below is called 'Select File'. At this point you should know the delimited and escape characters for your delimited file (see What is a Delimited File for more information). Ensure that the correct delimiter and escape characters are selected. Click on the 'Select File' button to open a file browser dialog. Use this dialog to navigate to the file you wish to import. Single click on the file to select it, then press the Open button.
Once the file has been selected, the 'Data Import' dialog changes to show the data contained within the file in the table at the bottom of the window, and a listed entitled 'Select Fields to Import' displays all the fields you can add to your project. Highlight those fields you want to use in your project. Take a quick look at the table of data values to ensure that the data looks like it opened properly. If you chose the wrong escape or delimited characters, the data may look jumbled. If you have correctly determined these two characters, you will see a uniformity of data. For example, in a heading like 'DateOfBirth' you will see a column of dates. If you've made a mistake, this column may contain lots of different values instead of just dates. Once you are satisfied with the file and how it has been interpreted, and you have selected the fields you want to import, press the 'OK' button.
The window will go away and you will see the name of the file you selected on the workbench to the right of the ticket you are working on. A list of the data fields you chose to import will appear directly under the file name. Now just drag each value and drop it on the project where you want the data to appear. Once you have placed the fields, you can preview the page to see how it will look when it is printed. Notice that the values from the data file appear where you placed the fields on the project.
If you need to have the same field value appear on the ticket in more than one location, you can either copy and paste the required field or double click on the data file path that appears on the workbench to reopen the Data Import window. Inside the Data Import window, you can select the fields you would like to add more than one value of to the workbench, then press the 'OK' button. The selected fields will appear on the workbench directly under the data file name.
If you need to have values from other delimited files (or databases) you will need to repeat the process of opening the files and placing the data values on the document where needed.
Double clicking on a data field value will open the Data Field Properties window.
To import data from a database, click on the Data Import button in the toolbar. The Data Import window will open. Select the tab named 'Database', then select the type of database you wish to open - Microsoft Access or any other type of database using an ODBC Connection String.
To connect to a Microsoft Access database, select 'Microsoft Access' as the Data Source Type and press the 'Browse' button. A file browser dialog will open. Use this dialog to navigate to the Access database file you wish to import. Single click on the database to select it, then press the Open button. Once you have selected the Access file, its path name appears in the text field labeled 'File'.
To connect to a different type of database using an ODBC Connection String, select 'ODBC Connection String' as the Data Source Type and press the 'Build' button. The 'Select Data Source' window will open. Click on the tab labeled 'Machine Data Source', then double click on the type of database you would like to connect to. The rest of the process of connecting to the selected database depends upon the database type and the driver you have installed. Please see the driver documentation for more detailed instructions.
Note: To use an ODBC Connection String, you must have a valid driver for the desired database type installed on your computer.
After connecting to the desired database, press the 'New Query' button to open the 'Query Builder' window. The Query Builder allows you to either enter a SQL query yourself using the 'Manual Input' option or build a query with the 'Query Wizard' option. (The 'Manual Input' method uses a powerful SQL query capability to select data. Only use this approach if you know SQL. A description of how to use SQL is outside the scope of this document. There are many sources that can explain how to use SQL to build data sets for import into your document). Select the option you wish to use and press the 'Next' button.
If you selected the 'Manual Input' option, simply enter the SELECT statement you wish to use into the field labeled 'SQL Statement' and the name of this query into the 'Query Name' field. Press the 'Finish' button to exit the Query Builder when you are finished.
If you selected the 'Query Wizard' option, use the 'Table/Query' drop-down list box to select the table from which to wish to import data. Then select the fields you want to import from that table by selecting the fields and clicking on the '>' button to move the selected fields from the 'Available Fields' list box into the 'Selected Fields' list box. Once you have selected all the tables and fields you want to import, press the 'Next' button.
Enter a name for the query you just built and select the sort order and column on which to sort. Press the 'Finish' button to close the Query Builder window and return to the Data Import dialog.
Once you return to the 'Data Import' dialog, you will see that your selected fields appear in the 'Select Fields to Import' list box and the values for the first 250 fields show up in the table at the bottom of the window. Press the OK button to add the selected fields to the project.
The window will go away and you will see the name of the query you created on the workbench to the right of the ticket on which you are working on. A list of the data fields you chose to import will appear directly under the file name. Now just drag each value and drop it on the project where you want the data to appear. Once you have placed the fields, you can preview the page to see how it will look when it is printed. Notice that the values from the database appear where you placed the fields on the project.
If you need to have the same field value appear on the ticket in more than one location, you can either copy and paste the required field or double click on the query name that appears on the workbench to reopen the Data Import window. Inside the Data Import window, you can select the query values you would like to add more of to the workbench, then press the 'OK' button. The selected fields will appear on the workbench directly under the data file name.
If you need to have values from other databases (or delimited files) you will need to repeat the process of connecting to the database and placing the data values on the document where needed.
Double clicking on a data field value will open the Data Field Properties window.
Double clicking on a data field value will open the Data Field Properties window. In the Data Field Properties window you can change the following properties:
Font Face: The font the data field will be rendered in. Example: Times New Roman.
Font Style: Draw the data field in one of four styles: Normal, Bold, Italics, or Bold Italics.
Font Size: The point size the font will be drawn in. This is any value greater than zero, and can include fractions of a size. Example: 12.5
Text Color: The color the data field will be drawn in. Click on the button to select from forty predefined colors, or press More Colors to select a custom color.
Line Spacing: The amount of space between multiple lines of text. The default is zero. Values greater than zero increase the amount of space between the lines. Values less than zero decrease the amount of space between the lines.
Border Color: The color of the border around the data field. Click on the button to select from forty predefined colors, or press More Colors to select a custom color. This has no effect if the border style is None.
Border Style: The type of border that will be drawn around the data field. If the selected value is None, then no border will be drawn.
Source File: Indicates the source file or database the data field is drawing its information from.
Design Options: Allows you select which value appears on the workbench while designing your project. View Initial Value will show the first value contained in the data file. View Longest Value will show the longest value for that field contained in the data file. View Shortest Value will show the shortest value for that field contained in the data file.
Wrap Long Lines: Some data fields contain very long lines. This option, along with the Characters Per Line field, allows you automatically wrap lines of text that are longer than a specified number of characters.
Characters Per Line: Specifies the maximum number of characters per line in the data field. This option is only available if Wrap Long Lines is selected.
To reshape the data field border, click the text once to select it and move the mouse over one of the black 'handles' around the edge of the border. Press and hold the left mouse button, then drag the mouse until the border is the desired size. Release the left mouse button to finish. Moving a handle in the corner of the border will move two sides of the border. Moving a handle in the middle of a side will just move that side.
Holding the Shift or Ctrl key on the keyboard while reshaping the border will force the width and height to remain proportionally the same.
To move a data field, move the mouse over it, then press and hold the left mouse button. Drag the mouse until the data field is in the desired position. Release the left mouse button to finish.
Rotate: The rotation of the data field. Positive values rotate counter-clockwise, negative values rotate clockwise. To rotate data field, click the data field once to select it and click on the Object menu. Select Rotate, enter the desired angle, then press the OK button.
Top Align: Move the data field to the top of the surrounding border. This can be combined with the left, right, or center horizontal alignments.
Bottom Align: Move the data field to the bottom of the surrounding border. This can be combined with the left, right, or center horizontal alignments.
Center Vertically: Move the data field to the vertical center of the surrounding border. This can be combined with the left, right, or center horizontal alignments.
Left Align: Move the data field to the left side of the surrounding border. It will also cause each line of text to line up on the left side. This can be combined with the top, bottom, or center vertical alignments.
Right Align: Move the data field to the right side of the surrounding border. It will also cause each line of text to line up on the right side. This can be combined with the top, bottom, or center vertical alignments.
Center Horizontally: Move the data field to the horizontal center of the surrounding border. It will also cause each line of text to line up based on their center points. This can be combined with the top, bottom, or center vertical alignments.
To align data field, click the data field once to select it. Make sure there is only one data field selected. Then, in the toolbar, press the button corresponding to the alignment action you wish to perform.
A delimited file is any file that holds repeating records, each record containing any number of fields. The field values are separated by a delimiter characters, usually a tab or comma. Each record is separated by an end of line character. Here is an example of a delimited file called MembersCommaDelim.txt that comes with TicketBench Enterprise.
First Name,Last Name,Nickname,Date
Paul,Painter,The Paint Guy,1/16/1998
Daniel,Davis,,10/10/1990
Jean,Jones,Jeanie,
In the above file, we can see that the delimited file has records with four fields. First Name, Last Name, Nickname, and Date are the names of the fields (or column headings if that makes more sense) and these values are usually the first values in a delimited file. Each value is separated by a comma so that is the delimiter; i.e., the comma tells us when one value ends and another value begins. For example, in the second row (record) notice that the nickname is three words long. In the third row, there is no value for Nickname, therefore two commas are side by side to denote the absence.
All rows must have the same number of delimiters, except when the field value actually contains the delimiter character as part of the data. For example, if the data value is an address and the address has a comma in the field value:
124 NW Canal Street, Seattle, WA 98107
Since this value has two commas, we need to use an escape character to warn the importing application not to interpret these commas as delimiters. We enclose the value with the escape character. For the sake of this example, let's have the escape character be &. So & would go at the front and back of the value telling the importer when to start ignoring the delimiter and when to stop:
"124 NW Canal Street, Seattle, WA 98107"
Most of the time, you won't have to worry about this since the application you use to create delimited files will handle it on it's own. It is useful, however, to be able to open a delimited file and be able to determine what the delimiter and escape characters are.