Import Formats¶
The following formats are supported for data import:
- ASCII (Single, X-Y, and Multi-column)
- Excel (Excel 97, Excel 95, v5, v4, v3)
- Lotus 123 (WK4, WK3, WK1, WKS)
- Quattro Pro (WB2, WB1, WQ1, WKQ)
- SigmaPlot (JPG, SPW, SP5, SPG)
- SPSS (SAV v7.5 and v8)
- Systat (SYS v8)
- TableCurve 2D Equation Reference (REF)
- DIF (Single, X-Y, and Multi-column)
- dBase (DBF III+, IV)
XY ASCII Files¶
This is the default data format. The default XY data file optionally contains a main title, X title, and Y title on the first three lines followed by successive lines of data. Almost any form of data can be converted so long as the X value precedes the Y value and so long as there is a delimiter between the values such as one or more spaces, a comma, a separate line, etc. Weights may follow the X and Y value by having an uppercase W preceding the weight value.
An X-Y ASCII data file must meet the following conditions:
- The X and Y-values must be sequential. The X-value must come first, followed by the Y-value and there must be some form of delimiter between the values. The first valid number is read into X1, the second into Y1, the third into X2, the fourth into Y2, and so on. The delimiter can be any whitespace character (space, new line, tab, etc.), a comma, or almost any other non-numeric character.
- The first three lines of the file can optionally contain a description of the data, an X-title, and a Y-title (if these begin with a numeric character, the string must be delimited by double quotes).
- Comment strings can exist in the data area, provided these are on a separate line or at the end of a line or numeric data. If a string begins with a number, it must be delimited by double quotes. Do not start a comment with the asterisk (*) or the W character.
- A concluding asterisk (*), alone or with comment text, marks the X-Y data pair as inactive. Such marked points are not processed in TableCurve 2D, although the points can be reactivated in the TableCurve Editor.
- A concluding W, followed by a number, is read as the weight for the data point. The numbers must be between 1E-30 and 1E+30. All weights are 1.0 by default.
- TableCurve 2D considers .PRN, .DAT, and .TXT to be default ASCII file extensions.
Typically, the standard TableCurve 2D X-Y ASCII file will look like:
Main description
X-title
Y-title
1.234 2.345
3.456 4.567
5.678 6.789
7.890 8.901
9.012 10.123
or:
1.234,2.345
3.456,4.567
5.679,6.789
7.890,8.901
9.012,10.123
All numeric values must be between 1E-30 and 1E+30. Scientific notation uses the standard format. For example, 1.2345E+12, 1.2345e+12, and .12345E+13 are valid entries. Space should not exist anywhere within the characters.
An ASCII data file can be created in any ASCII editor. The ASCII extracts of databases, spreadsheets, and other programs usually require no modification provided only two columns or fields of data are involved. The main problem with extracted or generated ASCII files will usually be how empty or numeric overflow values are handled. If such an X-value or Y-value is represented by a string, TableCurve 2D will skip over it and the proper X-Y-X-Y... sequence will be lost. If a value is stored as 0.0, which is frequently the case, TableCurve 2D will register this point as an active data pair, with possibly unfavorable consequences on the resulting analyses. Such files must be pre-edited.
Single Column ASCII Files¶
Frequently, the X-data will begin at a given point and thereafter vary with a constant increment. Many instruments, therefore, tend to generate single vector data files for space reasons. If you cannot save an X-Y file directly, TableCurve 2D can read the single column file. If the fourth line of the data file contains only a single numeric value, you will be asked if you wish to do a Single-Column Read. If you choose this option, you must specify the starting X-value and the X-increment so that the X-values can be automatically generated.
Multi-Column ASCII Files¶
If the fourth line in an ASCII data file contains three or more numeric entries, you are presented with an option to do a multi-column import. If you choose this option, you will be offered a column selection based upon all entries in the column, regardless of whether these are strings or numeric values. You must then select an X and Y column, and optionally a Weights column. Use the column number and the ASCII string information in the initial columns to determine the columns desired.
For TableCurve 2D to read a multi-column ASCII file successfully, it must be in one of two formats, both of which require all row entries for a given sequence of columns to be on a single line which concludes with a carriage return:
• Comma Delimited Format - Here all values are separated by commas. You may have any number of spaces within the data, but each column entry, including empty positions, must be separated from other entries by a comma. With this format, ASCII character strings do not have to be delimited with quotes. You will have to delimit a string with quotes, however, if it contains a comma.
• Space and Quote Delimited Format - In this case, there are no commas separating the different column entries. Rather, TableCurve 2D relies on a space or spaces between the entries of numeric values, and on double quotes beginning and concluding every character string. With this format, quoted character strings are essential. You must also use a pair of empty quotes to represent empty column positions.
The following two examples illustrate both formats and represent valid multi-column ASCII files:
Column 1,Column 2,Column 3,Column 4,Column 5
1.234,2.345,Data Missing,7.890,8.901
3.456,4.567,6.789,9.012,10.123
5.678,6.789,7.890,11.234,12.345
7.890,8.901,,13.456,14.567
"Column 1" "Column 2" "Column 3" "Column 4" "Column 5"
1.234 2.345 "Data Missing" 7.890 8.901
3.456 4.567 6.789 9.012 10.123
5.678 6.789 7.890 11.234 12.345
7.890 8.901 "" 13.456 14.567
It is always a good idea to carefully inspect the input data from multi-column files whose formats may not be fully compatible. The TableCurve 2D multi-column import procedure should read most multi-column ASCII files generated by commercial software.
If the fourth line of an ASCII file contains more than two floating point values, you will be offered a multi-column read option. A multi-column file must either be comma delimited or freeform with quoted strings.
Excel, Lotus, and Quattro Pro Files¶
The column identifier and the strings present in the first 100 rows are used to aid in selecting the columns desired. For a given column to be offered for selection, there must be at least one cell entry present for that column within the first 100 rows. Across all sheets or pages, the maximum number of columns available for selection is 4096. Use these text strings and the column designation to select an X and Y column, and optionally a Weights column for the data table.
The column selection list will show both the sheet and column in an Excel-like nomenclature. For example, the column designated as (3)!D is column D in the third sheet. For Excel 95, Excel 97, and Excel 2000, the name assigned to the sheet will also be shown following the worksheet number. The following considerations apply to importing data from spreadsheet files:
• All data to be imported must be in numeric or formula format. Numbers saved as strings will not be read. Strings may appear anywhere within the columns.
• For Lotus, Quattro, and versions of Excel prior to Excel 95, TableCurve 2D will seek to read a main title from the first row of the sheet or page. This title will be available only if the first row contains a single string entry. This title must thus suffice for all sets on the page. It can be in any column. With the exception of the title column, TableCurve 2D will offer the first string in each selected column as the X or Y variable title. In the title column, the second string is offered as the variable title.
• For Excel 95, Excel 97, and Excel 2000, the first row can contain any number of main titles, each applicable to the specific data set. This supports Excel 95/97/2000 as a data source in TableCurve 2D Automation. It is recommended that you place each data set title in the Y column of that particular set. If the Single X format is used, this means that all columns with data will have a main title in the first row except column A which will contain the X values. For the XY Pairs option, the titles should be in columns B,D,F,etc. TableCurve 2D will offer the row 2 string in each selected column as the X or Y variable title.
• Valid data entries must be present in the columns in order for that row’s entry to be added to the data table. If for a given row, the column specified for one variable has a numeric value and the column for another is empty or is filled with a string, no entry will be made to the table.
• All instances of a zero value in an X or Y column will be included in the data imported. Such points, if invalid, can be toggled off in the Section or TableCurve Editor option.
• You will not be able to select the same column for more than one variable.
• To insure proper X-Y correspondence, all columns are required to reside on a single page or sheet.
When the read operation is completed, you will be shown the number of points read and you will be offered the option of using the titles detected in the spreadsheet as the main, X, and Y titles for the data set. If you press Previous Titles in this titles entry form, the titles present prior to the read, if any, are restored.
SigmaPlot Files¶
TableCurve 2D can build its data table from the numeric values stored in SigmaPlot JNB, SPW, SP5, and SPG graph files. The import process is very similar to that of the spreadsheets. There are a few differences:
• Only the first available worksheet is available for import. This can be an embedded Excel worksheet.
• The SigmaPlot column selection list consists of the column labels assigned within SigmaPlot rather than the strings in the initial columns.
• Only numeric entries are read. Embedded strings and missing values are not imported.
SPSS Files¶
The SPSS SAV format used in v7.5 through v10 is supported for data import. The process is again nearly identical to spreadsheet import. The differences are:
• You must select columns based on the variable name and variable label assigned within SPSS.
• Valid X-Y pairs, those without system or variable-specific missing values, are added to the data table.
Systat Files¶
The Systat SYS format used in v8 is supported for data import. The differences relative to spreadsheet import are:
• You must select columns based on the variable name assigned within Systat.
• Valid X-Y pairs, those without missing values, are added to the data table.
TableCurve 2D Equation Reference Files¶
TableCurve Equation REF files contain all information necessary to evaluate the equation. When an equation reference file is selected for data import, discrete data is generated. You must supply a starting and ending X-value and also either an X-increment or total data count.
• Reference fits cannot be imported from v4 and prior releases of TableCurve 2D.
Data Interchange Format (DIF) Files¶
If a DIF file contains only one column of numeric data, a single-column read is offered. You must specify the starting X-value and the X-increment so that the X-values can be automatically generated as the Y values are read.
If a DIF file contains two columns of numeric data, the file is automatically read assuming the first column to contain the X-values and the second column the Y-values. If the variables need to be reversed, this can readily be done within the TableCurve Editor.
When a DIF file contains more than two columns of numeric information, a multi-column read is offered. You will be offered a column selection based upon a column ID and any title assigned to this column. Just as with the spreadsheet options, you must select a column for the X-variable, for the Y-variable, and optionally for the Weights.
dBase Files¶
TableCurve 2D can build its data table from the numeric information in dBase databases. Both dBase III+ and dBase IV type DBF files are supported. The import process is similar to that of a spreadsheet except numeric fields are selected for the X and Y variables. The following rules apply:
• Only numeric fields (type N in III+ and types N and F in IV) are offered for selection.
• If a dBase record is marked for deletion, the data from the selected fields will still be added to the data table, but this X-Y pair will be marked as inactive and will not be included in TableCurve 2D processing. Such points, if invalid, can be toggled off in the Section or TableCurve Editor option.
• Zero values in dBase data files are sometimes legitimate and other times simply represent a field that has yet to receive a value. All instances of a zero value in one or both of the fields will be included in the data imported, but these data pairs will be marked as inactive. Such points, if representing meaningful data, can be made active by using the Section or TableCurve Editor option.
dBase database files do not use a binary format for storing numeric values. Numeric storage is generally in fixed decimal position ASCII strings. This limits the range and precision of floating point values. The import procedure will preserve whatever precision exists within the original dBase numeric field. Under certain conditions, versions III+ and IV can both store ASCII scientific notation. Type N and F fields containing scientific notation will be correctly read by TableCurve 2D.
When selecting columns from a dBase file, you are selecting database field names for the X and Y, and optionally the Weight, variables.