Load Text Data
Last updated
Last updated
NOTE: ADVIZOR supports comma, tab, and "|" delimited text files by default. A configuration file may be used to provide other separators (see below).
TIP: Note that the Data Wizard windows can be resized as required for optimum display of data.
To display the Data Wizard you can: Select New Data Source in the Load Data Section from the File Ribbon.
To use a different name to identify this data source, type in a new name in the Data Source Name edit box. You can change this name and add a description. When the Remember Data Source Definition checkbox is checked (the default setting), the dataset and all of its configured properties will be remembered for “one-click” data loading the next time that you use the ADVIZOR application. Click Finish.
The Welcome wizard page displays. Click Next. In the Select Data Source Type wizard page, if not already selected, click the radio button for Text File (.txt or .csv). Click Next.
In the Specify Data File wizard page, specify the source file for your data. You can type the path name directly into the edit box or click the Browse button to locate the file.
If the first row of your data file identifies field names, be sure that a checkmark appears next to the First row of field names field. Click Next.
NOTE: For users of previous versions of ADVIZOR Workbench or Developer Edition (DE) products, note that for this release of ADVIZOR, the second line of the text file cannot contain the data type and format specifications used by ADVIZOR Solutions products.
The Configure the Dataset wizard page displays. In this page, you can:
Edit the table name or change field properties via the Configure View.
Preview the first 25 rows of data in the dataset via the Data View.
Two views appear on the top left side of the wizard page:
By clicking the Configuration View you can:
Edit the display name for the table.
Configure which fields to include in the dataset by checking or clearing the checkbox in the first column.
Change field properties, such as display name for the field, field data type, and the level of precision,
whether or not to group digits or show the values as a currency for numeric fields.
The Data View displays the data values for the first 25 rows in the dataset. This is a view-only mode to help confirm it is the desired dataset and is the default view for the page.
To edit the table name, type the new table name in the Table Name edit box.
To select a field to include in the dataset, if a checkmark appears in the first column of the row, the field is already included in the dataset. If no checkmark appears, click on the check box to select the field. Repeated mouse clicks on the checkbox will toggle field selection between including in the dataset and excluding from the dataset.
TIP: Click on the first column header to select all or exclude all fields from the dataset.
To deselect a field for inclusion in the dataset, if a checkmark appears in the first column of the row, the field is already included in the dataset. Click the check box to clear the checkmark, thus excluding the field.
To change properties of a field, highlight the cell to be changed. For the Display Name column, type in the desired name. For the Data Type, Precision and Currency Symbol columns, note the drop down arrow that displays to the right of the cell. Click the arrow to display a list of options to choose from and make a selection from the pulldown list. For the Group Digits and As Currency columns, click on the checkbox to toggle whether or not to use these options.
TIP: For currency columns, the currency symbol defaults to the symbol used by the computer's regional settings, which can change if the project is opened on a different computer. To use a specific symbol regardless of the computer's settings, select a symbol from the pulldown list.
Click Next.
Review the information displayed in the Dataset Summary wizard page. This information includes:
Source Type – Text File (*.txt or *.csv).
Data Source - The name of the file.
Source Table – The name of the file.
Display Name – The name of the table as it will appear in ADVIZOR.
Rows – The number of rows of data to be loaded.
Selected Fields – The number of fields you selected in the Configure the Dataset wizard page.
Click Next to proceed. Note that when you click the Next button, the dataset is loaded immediately. You can no longer modify the dataset properties.
The Loading Dataset wizard page displays. It shows the progress as data is loaded into ADVIZOR.
The Completing Data Wizard page is displayed. In this page, a default data source name is included to uniquely identify this dataset.
Options can be specified that control how a text file ais read with a configuration file placed alongside the data file with an "ini" extension. For instance if the data file is named "eFashion.txt" a file placed in the same directory named "eFasion.ini" would be recognized and parsed for optional settings.
The currently available options:
UseHeader: Does the first line in the file containing the data field names. Valid values: True, False. Default is False.
FieldDelimiter: What are the possible field delimiter characters? Valid values are any characters; the default characters are the comma, tab, and '|' characters.
ObserveQuotes: Is the delimiter character used to separate fields escaped by quotes? Valid values are: True, False. Default is false.
NullBadData: Is invalid data replaeced by missing data? Valid values are True, False. Default is false. If invalid data is not replaced by missing data, it causes reading of the input file to be stopped.
The format of the file is a valid XML string in the following format:
An example of a valid configuration file might be:
This configuration file would set the field delimiter to a comma character, read field names from the first line in the data file, ignore delimiters between quote characters, and replace bad data with nulls without generating any errors. This configuration would be useful for loading CSV files.
