Import Data Wizard: Verify Text File Format
The Import Data Wizard allows you to import data into your portfolio, as well as update existing data. This page allows you to verify and modify the text file format being used to import a specific text file that is being imported.
& For help importing data, see Importing and Exporting Data.
Determining the Status of the File Being Processed
The top section of this page indicates the file being processed and the current status of that processing. The following are the messages that will typically appear in this window.
· Default settings have been assigned for reading this text file.
This message appears if TradingSolutions has determined the settings to use for this file.
· The formatting settings that were previously used for this file have been reloaded.
This message appears if the settings that were used for processing this file previously differ from the defaults that TradingSolutions would normally select. The previous settings are given precedence to allow you to modify the settings associated with a file and use them each time the file is updated.
Ä Note: Format settings are stored using the first half of the filename. If you import multiple files with the same name, even if they have different extensions, the format settings from the previous import will be used. This can cause problems if you are using data from multiple data sources.
: Example: If you import IBM.TXT from a CD-ROM and then download IBM.CSV (or IBM.TXT) as a file from the Internet and import it, the format settings used for the original IBM.TXT will be used by default.
Ä Note: To change the format settings to the TradingSolutions defaults, press the Load Default Settings button.
· This file could not be opened. It may currently be opened by another application.
This message appears if the selected file could not be opened to read its information. This typically occurs when another application currently has the file open, preventing TradingSolutions from reading its contents.
: Example: If you create the file IBM.CSV using Save As for a spreadsheet in Microsoft Excel and still have that spreadsheet open, Excel will not allow that file to be read by other applications until the spreadsheet is closed.
Ä Note: To continue processing this file without canceling the import process, press the Back button until you return to the Select Files page and then use the Next button to reread the files.
· The filename contains valid date information.
This informational message appears when the name of the file can be interpreted as a date. If the file does not contain a date field, all of the records in the file will be assumed to be for the date in the file name.
Ä Note: When the date information in the file name is used for the file, only symbols that already are in the portfolio will be updated. Symbols for data that is not already in the portfolio will be ignored.
Previewing Text File Processing
The Verify Text File Format page is divided into two sections. The upper section displays a field table of the individual fields found in the text file based on the selected options. The lower section allows you to modify the options used for importing the text file.
Ä Note: One field must contain Date or Date/Time information unless valid date information is reported to have been found in the file name (indicating an end-of-day file). Only one field can contain date information and one field can contain time information.
The field table contains the following information.
p Name
This value is the field name that will be assigned to this particular field. If the First Row Contains Field Names checkbox is checked, these field names will be taken from the values in the Row 1 column. Otherwise, generic field names will be generated for each field.
Ä Note: If field names are not found in the file, default names will be based on standard field orders found in text files. Specifically, the fields will be named Open, High, Low, Close, Volume, OpenInt. This format assumes that the first integer field is the first field. If fewer than six fields exist, the most likely subset will be assigned.
Ä Note: Typically, these names will not need to be changed. If you do want to change the field names, you can do so by modifying the table directly. If you choose the option to select individual ticker symbols or fields from the files on the Select Files page, you can also change the field names on the selection tree.
p Type
This value is the type of data that this field contains. This can be one of the following settings.
· Numeric
This value indicates that the field contains numeric information.
Ä Note: If a field contains numeric information, but should be ignored, set it to Ignored.
· Date
This value indicates that the field contains date information.
· Date/Time
This value indicates that the field contains date and time information.
· Time
This value indicates that the field contains time information.
· Ticker
This value indicates that the field contains ticker information. Including a ticker column in a file allows you to import multiple symbols from a single file.
· Data Name
This value indicates the name of the security associated with the ticker in the ticker column. It is rarely used since it makes data files much larger.
· Ignored
This value indicates that the values in this field should be ignored.
Ä Note: Typically, these types will not need to be changed. If you do want to change the data type selected for a field, you can do so by selecting the type in the field table, then selection a new type using the data type combo box that appears in that cell.
p Width
This value is the width of the field in characters. It is only displayed when the file record format is set to fixed width.
p Row 1 / Row 2 / Row 3 / …
These rows display the first 25 rows of data following any skipped lines. If the Row 1 contains field names, the First Row Contains Field Names checkbox should be checked so that these names are used for the field names.
Ä Note: If First Row Contains Field Names is selected, Row 2 is permitted to be blank. Any other blank lines will be interpreted as the end of the data.
Continuing Without Making Any Changes
In most cases, the default processing for a text file should be acceptable. This can be verified by checking the field names and making sure each field has data in the displayed rows.
If the status window at the top of the page indicates that the formatting settings that were previously used for this file have been reloaded, it may be necessary to load the default processing for the text file. To do this, press the Load Default Settings button.
Ä Note: The remaining options should only be adjusted if the text file does not appear to be processed correctly.
Ä Note: If you frequently use a file type that requires you to make adjustments, please contact NeuroDimension technical support to determine if default processing for your files can be added to TradingSolutions.
Selecting a File Record Format for a Text File
Text files imported into TradingSolutions must have at least two fields – a date field and one or more fields with numeric data. These fields are typically separated in a text file using a field delimiter; however, they can also be separated by being placed into fixed-width columns. Most text files contain fields that are separated using a comma or a tab. TradingSolutions will automatically try to determine the correct file record format for every text file.
If only one or two fields appear in the field table and the values in the rows are made up the values of multiple fields, the default formatting for the text file may be incorrect. To fix this, use the File Record Format controls to specify a different format to use. When a new file record format is specified, the field table will be updated to reflect the new settings automatically.
Ä Note: Only text files that have date-based information in a column format are supported. Other types of data in text files should be placed into this format externally before being imported into TradingSolutions.
If you have previously imported data from a text file with the same file name (even if the extension of the file was different), the settings used for that import will be displayed. If these settings do not appear to be appropriate for the current file, press the Load Default Settings button to have TradingSolutions determine the default settings.
The following controls are available for specifying the file record format:
· Fixed Width
This format indicates that the fields in a text file are separated into fixed-width columns. In other words, a certain number of characters in each line will be used for the information in each field. The width of each field can be unique and is specified using the Width entry in the field table.
Ä Note: When TradingSolutions auto-detects field width, it assumes any information is right-justified.
· Delimited
This format indicates that the fields in a text file are separated using a field delimiter. Once this option is selected, use the Field Delimiter box to select or enter the field delimiter character.
¨ Lines to Skip
This setting allows header information before the beginning of the data to be skipped. Examples of this are source and copyright information for the file.
Ä Note: Field name information is not considered to be header information as long as it is delimited in the same way as the data fields. Because of this, a row with field names should not be skipped over. Instead, it should be in Row 1 of the field table and the First Row Contains Field Names checkbox should be checked.
¨ Field Delimiter
This value specifies the character that is used to separate the columns in a delimited file. It is not used for fixed width files.
þ First Row Contains Field Names
This setting indicates that Row 1 of the field table contains the names of the fields. This row must be delimited in the same way as the other rows.
Ä Note: if this option is selected, Row 2 is permitted to be blank.
Ä Note: In addition to these options, lines beginning with "<!—" are ignored automatically.
Specifying the Data Format for a Text File
The following options are available for specifying the format of the individual pieces of data in the file.
¨ Date Delimiter
This value specifies the character that separates the elements in a date field. If these elements are not separated by a delimiter, this value should be set to "<none>".
: Example: For dates in the form "08/30/66", this value should be the slash ( / ).
¨ Date Order
This value specifies the order of the date elements.
D Day
M Month
Y Year
: Example: The value "YMD" indicates the date elements will appear in the order: Year, Month, Day.
Ä Note: The month field can be represented with a number or with the first three letters of the month. For example, "01" and "Jan" are both valid, but "January" is not.
þ Four Digit Years
This setting indicates that dates include four digits for the year value. If it is not selected, dates are assumed to have two digit years.
¨ Time Delimiter
This value specifies the character that separates the elements in a time field. If these elements are not separated by a delimiter, this value should be set to "<none>".
: Example: For times in the form "10:30:00", this value should be the colon ( : ).
Ä Note: This setting is only available if a Date/Time or Time field is in the file.
¨ Time Seconds
This value specifies the number of digits used for the seconds in a time value. Some programs, such as MetaStock, permit three digits to be used in the seconds column and use it to indicate the number of ticks in a minute.
Ä Note: While the importing of three-digit seconds is supported, these values will be converted to normal seconds by removing the hundreds digit. Values over 59 may result in the file not being able to be imported.
¨ Decimal Symbol
This value specifies the character being used as the decimal symbol in the file.
What Do I Do Next?
After verifying the formatting of the text file, press the Next button to continue. After all of the text files have been processed, the Start Import page will be displayed. If you indicated that you wanted to select individual ticker symbols or fields from the files, the Select Data page will be displayed.
Ä Note: If a file cannot be opened or valid date information for a file cannot be found, the Next button may not be available.
If you would prefer not to import any data at this time, press the Cancel button. If you would like to change which files are being imported, press the Back button.
How Did I Get Here?
This page is displayed for text file imports after the Select Files page in the Import Data Wizard. It is only displayed if you select to view the text file import options for each file being imported on that page or if a problem is encountered processing a particular text file.