home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Windows NT Super Tune-Up Kit
/
PIE-WindowsNTSuperTuneUpKit-1997.iso
/
CONVERTR
/
CONVR302
/
CONVERT.DOC
< prev
next >
Wrap
Text File
|
1993-02-03
|
10KB
|
248 lines
CONVERT.DOC
02/03/93
Program written by:
Bruce Guthrie
Room H-4885
U.S. Dept of Commerce/ESA/OBA/BSISD
Washington, D.C. 20230
(202) 482-3234
This program was written in support of the National Trade Data Bank project and,
as a government program, is basically in the public domain.
Introduction:
This program converts data files from one format to another. Specifically, it
can take the following input file types:
ASCII-delimited file
Fixed-field file
dBaseIII (.DBF) file
and create the following files:
ASCII-delimited file
Fixed-field file
Lotus 1-2-3 worksheet (.WKS)
dBaseIII (.DBF) file
An ASCII-delimited file is one which has double quotes around each character
field and typically commas between fields. Leading and trailing spaces are
removed from character as well as all other values.
A fixed-field file places each field in the same column positions on each
record. The lengths of the fields are the same from record to record.
As an example, these might be an ASCII-delimited records:
"Economic Bulletin Board","202 482-3870",35
"EBB High-Speed","202 482-2584",100
Fixed-field file records might look like this for the same data:
Economic Bulletin Board 202 482-3870 35
EBB High-Speed 202 482-2584 100
WKS files are supported directly by Lotus 1-2-3 (all versions) as well as most
other spreadsheet programs. DBF files are supported by dBaseIII, dBaseIII+,
dBaseIV, and most other data base management programs.
Field definition file:
Unless you are reading a dBase file, the CONVERT program requires a field
definition file to figure out the characteristics for each field and also to set
certain file characteristics. The definition file can be created with any text
editor. The definition file consists of several records with the following
fields separated by spaces. Except for the record type indicator (which must
begin in column 1), all other fields can be placed in any columns:
(1) record type (see below)
(2) length of field on input
(3) number of decimal places for numeric data (if you don't know,
put a "?" here; for non-numeric data, a "0" is fine) on output
(4) length of field on output
Any characters after the field length are treated as comment fields. You would
typically use this to enter the field name or column position or any other
information of use to you.
The data record types accepted by CONVERT are as follows:
type C = character data
N = numeric
L = logical (T or F)
D = date (in yyyymmdd format)
Other records accepted in the field definition file are as follows:
type ; = comment (anything after semi-colon is ignored)
X = lines to ignore; specify "X=string" and any records
which begin with the "string" characters will be ignored;
defaults to X="_
Z = delimiter between fields; defaults to Z=,
The data fields (types C, N, L, and D) should be in the order the fields are
found in the source file.
Note that for fixed field files, you have to account for every byte in the file.
If you have something like this:
12345678_1_2345678_2_2345678_3 (column positions)
APPLE X Y 12 BANANAS
Even though you may think you only have five fields, the following .DEF file
will NOT work:
; Bad .DEF file: Note does not account for blank spaces
C 8 0 8 Fruit1
C 1 0 1 Class1
C 1 0 1 Class2
N 5 0 5 Value
C 11 0 11 Fruit2
You may want the Fruit1 field to be in columns 1 through 8 and Class1 to be in
column 10 but CONVERT will not know to skip column 9 so it will start reading
Class1 beginning in column 9, Class2 beginning in column 10, etc. To drop the
blank positions, you have to add dummy fields on input and ask for them to be
dropped on output:
; Good .DEF file: Spaces between fields are accounted for
C 8 0 8 Fruit1
C 1 0 0 Filler
C 1 0 1 Class1
C 1 0 0 Filler
C 1 0 1 Class2
C 1 0 0 Filler
N 5 0 5 Value
C 1 0 0 Filler
C 11 0 11 Fruit2
You can also use the input field length and output field lengths to either drop
fields using other formats (by specifying a zero length for the output field
length) or for creating fields on output (by specifying a zero length for the
input field length). You can also use this to expand on contract a field. For
example, if Fruit1 is 8 characters long but you only want it to occupy 4
characters on output (thus the field would be truncated), specify 8 for the
input field length and 4 for the output field length.
Running the program:
Some of the default values for this routine can be set using the CONFIGWS.EXE
program.
To run the program, you must specify at least one parameter (the name of the
input file). There are also some optional parameters which can be left off if
you want:
CONVERT infile [ outfile [ deffile ] ]
[ /OVERWRITE | /-OVERWRITE | /OVERASK ]
[ /DEF=deffile ] [ /FROM { FIXED | ASCII | ASCII=char | DBF } ]
[ /FIXED | /ASCII | /ASCII=char | /DBF ] [ /USEDEF | /NODEF ]
[ /TO { FIXED | ASCII | ASCII=char | WKS | DBF } ] [ /HEADER | /-HEADER ]
[ /SKIP | /MISSING | /ABORT ] [ /SAS ] [ /BEEP | /-BEEP ] [ /Q ] [ /? ]
"infile" is the file specification for the ASCII-delimited or fixed-field file
you want converted. You can specify a drive and path specification if
necessary. This parameter is required.
"outfile" is the file specification of the file you want to create. You can
specify a drive and path specification if necessary. If no outfile is provided,
the routine will presume you want the output file called the same thing as the
infile but you want the extension to be ".FIX" (if the output file is a
fixed-field file), ".PRN" (if the output file is ASCII-delimited), or ".WKS" (if
the output file is to be in a WKS format).
"deffile" is the file specification for the field definition file. You can
specify a drive and path specification if necessary. If no deffile is provided,
the routine will presume it is called the same thing as the infile but it has
the extension of ".DEF". Note that the deffile can only be provided if you also
specify the outfile name.
"/OVERWRITE" says to overwrite the output file if it exists already.
"/-OVERWRITE" says to abort if the output file exists already.
"/OVERASK" says to ask if the output file exists already. This is typically
the default but it's CONFIGWS-able.
"/FROM FIXED", "/FROM ASCII", "/FROM ASCII=char", and "/FROM DBF" specifies the
format for the input file. The routine will typically read the file and guess
its format for you. The "/FROM ASCII=char" form allows you to specify the
ASCII-delimiting character (usually a comma) instead of specifying it in the
definition file.
"/FIXED", "/ASCII", "/ASCII=char", and "/DBF" specifies the format for the input
file. This is a short for of the "/FROM format" specification.
"/USEDEF" is specified for dBase input files if you want CONVERT to read an
existing field-definition file. Since dBase files contain all of the field
information in the data base itself, CONVERT will normally pick up all field
information from there and simply ignore any field-definition file.
"/NODEF" is specified for ASCII-delimited input files if you want CONVERT to
figure out the field-definition file for you. There are some risks in this and
the operation takes longer than you may desire. Note that CONVERT will
automatically write out the field-definition file for you if you select this
option.
"/TO FIXED", "/TO ASCII", "/TO ASCII=char", "/TO WKS", and "/TO DBF" tells the
routine what sort of output file you'd like to create. Typically defaults to
"/TO WKS" although you can change this using the CONFIGWS.EXE program.
"/HEADER" is used in conjunction with WKS output files. If /HEADER is in
effect, the first row of the spreadsheet will contain the variable name for the
cell as provided in your control file. If none are provided, the field names
will be FIELD_01 onward. /-HEADER turns this off and is the default. Note that
the header line (if any) will show up in the output counts.
"/SKIP" says to skip records with bad data values; otherwise the routine
aborts when it runs into any. /SKIP, /MISSING, and /ABORT are mutually
exclusive.
"/MISSING" says to presume any missing fields in an ASCII-delimited record
should be filled in with blanks (for character fields) and 0 for numeric fields.
Incomplete records are written out (unlike in /SKIP). /SKIP, /MISSING, and
/ABORT are mutually exclusive. Note that the program will only print out the
first "bad" record. There may be others that show up after this one.
"/ABORT" says to abort when you run into bad records. This is typically the
default although you can change this using the CONFIGWS.EXE program. /SKIP,
/MISSING, and /ABORT are mutually exclusive.
"/SAS" tells the program to accept single decimal points as valid numeric data
and to convert them to zeroes. The decimal point convention is used in the SAS
language to designate missing data.
"/BEEP" beeps when the program is finished.
"/-BEEP" reverses /BEEP and is typically the default. You can change the
default with the CONFIGWS.EXE program.
"/Q" turns off the line-by-line status report.
"/?" (or "/HELP" or "HELP" ) gives you the syntax for the command.
Restrictions and Caveats:
The program cannot handle dBase files with Memo fields.
Most spreadsheet programs restrict a given field length to being 240 characters
or less.
Date fields which are in the form "yy-mm-dd" (instead of "yyyymmdd") should be
declared as character fields instead of date fields.
Date fields converted for WKS files are changed into strings of the "yy-mm-dd"
format.