PARM !_INPUT_FILE, !_OUTPUT_FILE                                              
# *******************************************************************
# * PROGRAM NAME:  DELIM.QZPRGMS
# * WRITTEN BY  :  KAW 8/11/98
# * DESCRIPTION :  CONVERT A TAB DELIMITED FILE TO A PORTABLE
# *                SUBFILE FOR USE BY QUIZ.
# *
# * _INPUT_FILE => The Tab delimited file.
# *
# *                In Excel, make sure all the cells are not
# *                activated.  The best way is to copy the area
# *                you want to transfer to a new worksheet.
# *                Save the worksheet as Text (Tab Delimited)(*.txt).
# *
# *                Delete column headings.
# *
# *                Transfer the file to the HP.  Transfer as ASCII.
# *                Host Record Size 1000 (so the file won't wrap).
# *                Make sure to NOT convert Tabs to Spaces.
# *
# * _OUTPUT_FILE=> The name of the Quiz Portable Subfile.
# *                The portable subfile dictionary will have this
# *                name and this is the name of the file to be
# *                accessed by Quiz.  The data will be stored
# *                in a file of the same name ending with the letter
# *                "Q".
# *
# *                "_OUTPUT_FILE" and "OUTPUT_FILEQ" are temporary
# *                files.
# *******************************************************************
# *
# * MODIFIED:  KAW 12/31/98
# *            TRY TO DELETE MORE VARIABLES AS I GO SO THE FILES CAN
# *            BE BIGGER.
# * MODIFIED:  KAW 1/28/99
# *            RAISE HPMSGFENCE
# *******************************************************************

# *******************************************************************
# * First define all the fields.  This makes a lot of variables
# * since there is actually one variable for every field on every
# * record (_NUM_REC * _NUM_COL).
# *******************************************************************

SETVAR HPMSGFENCE 1

# Set TAB as delimiter
SETVAR _DELIM CHR(9)

# Get EOF for "_INPUT_FILE" so I know how many records to loop through
SETVAR _NUM_REC FINFO("!_INPUT_FILE","EOF")

# Determine the number of columns (fields) from the number of Tabs
#   in the first row.

# Set a variable to the first record
PRINT !_INPUT_FILE;START=1;END=1 >NEWFILE
INPUT _CUR_REC <NEWFILE

# Determine the length of "_CUR_REC"
SETVAR _LEN LEN('!_CUR_REC')
# Remove all the Tabs
SETVAR _NO_TAB REPL('!_CUR_REC','!_DELIM','')
SETVAR _NEW_LEN LEN('!_NO_TAB')
# The number of columns is the old length minus the new length plus one
SETVAR _NUM_COL _LEN - _NEW_LEN + 1

# First row (record)
SETVAR _REC 1

# For each row (record)
WHILE !_REC <= !_NUM_REC

   SETVAR _COL 1
   SETVAR _DELIM_0 0
   SETVAR _LENGTH_0 0

   # Print a line to the temp file "NEWFILE"
   PRINT !_INPUT_FILE;START=!_REC;END=!_REC >NEWFILE
   # Then set variable "CUR_REC" equal to the line
   INPUT _CUR_REC <NEWFILE

   # Take out any single quotes around the fields.
   #   Look for 'TAB"' and change to 'TAB'.
   #   Look for '"TAB' and change to 'TAB'.
   SETVAR _CUR_REC REPL('!_CUR_REC','!_DELIM"','!_DELIM')
   SETVAR _CUR_REC REPL('!_CUR_REC','"!_DELIM','!_DELIM')

   # Change any '""' to '"'
   SETVAR _CUR_REC REPL('!_CUR_REC','""','"')

   # For each column (field) except the last one
   WHILE !_COL < !_NUM_COL

      # Determine the position of the comma
      SETVAR _DELIM_!_COL POS('!_DELIM','!_CUR_REC',!_COL)
      # Determine the length of the column (field)
      SETVAR _LENGTH_!_COL _DELIM_!_COL - _DELIM_![!_COL - 1] - 1

      # Set variable to the field
      SETVAR _FIELD_![!_REC]_![!_COL]                                &
         STR('!_CUR_REC',_DELIM_![!_COL - 1] + 1,_LENGTH_!_COL)

      # Assume the field is numeric until I run across something text.
      #   If anything in the entire column is text, call the whole
      #   field text.
      IF !_REC = 1
         SETVAR _FIELD_TYPE_!_COL "N"
      ENDIF

      # It is numeric if all the characters are numbers and one "." or
      #    if the field is blank.
      IF _FIELD_![!_REC]_![!_COL] <> ""
         IF NOT NUMERIC('!'_FIELD_![!_REC]_![!_COL]'' - '.')
            SETVAR _FIELD_TYPE_!_COL "C"
         ENDIF
      ENDIF

      # Keep track of the longest column
      IF !_REC = 1
         SETVAR _MAX_LENGTH_!_COL 0
      ENDIF

      IF _LENGTH_!_COL > _MAX_LENGTH_!_COL
         SETVAR _MAX_LENGTH_!_COL _LENGTH_!_COL
      ENDIF

      # Next column
      SETVAR _COL _COL + 1

   ENDWHILE

   # For the last column

   # Set variable to the field
   #    Extract from the previous Tab to the end
   SETVAR _FIELD_![!_REC]_![!_COL]                                   &
      STR('!_CUR_REC',_DELIM_![!_COL - 1],                           &
      LEN('!_CUR_REC') - _DELIM_![!_COL - 1])

   SETVAR _LENGTH_!_COL LEN('!_FIELD_![!_REC]_![!_COL]')

   # Assume the field is numeric until I run across something text.
   #   If anything in the entire column is text, call the whole
   #   field text.
   IF !_REC = 1
      SETVAR _FIELD_TYPE_!_COL "N"
   ENDIF

   # It is numeric if all the characters are numbers and one "." or
   #    if the field is blank.
   IF _FIELD_![!_REC]_![!_COL] <> ""
      IF NOT NUMERIC('!'_FIELD_![!_REC]_![!_COL]'' - '.')
         SETVAR _FIELD_TYPE_!_COL "C"
      ENDIF
   ENDIF

   # Keep track of the longest column
   IF !_REC = 1
      SETVAR _MAX_LENGTH_!_COL 0
   ENDIF

   IF _LENGTH_!_COL > _MAX_LENGTH_!_COL
      SETVAR _MAX_LENGTH_!_COL _LENGTH_!_COL
   ENDIF

   # Increment to the next row (record)
   SETVAR _REC _REC + 1

ENDWHILE

# *******************************************************************
# * Now I have a whole bunch of variables "FIELD_X_Y" and I want to
# * put them in a new file where all the records are in fixed columns
# * based on the maximum length of the fields "MAX_LENGTH_X".
# *******************************************************************

# The data actually goes in to a file with the same name as
#   "_OUTPUT_FILE" but with a "Q" at the end.

IF LEN("!_OUTPUT_FILE") <= 7
   SETVAR _OUTPUT_FILEQ "!_OUTPUT_FILE" + "Q"
ELSE
   SETVAR _OUTPUT_FILEQ STR("!_OUTPUT_FILE",1,7) + "Q"
ENDIF

# Purge "_OUTPUT_FILEQ" if it already exists.

IF FINFO("!_OUTPUT_FILEQ","EXISTS")
   PURGE !_OUTPUT_FILEQ, TEMP
ENDIF

#File should be 1000B wide
FILE !_OUTPUT_FILEQ=!_OUTPUT_FILEQ;REC=500,1,F,ASCII

# Initialize the record counter
SETVAR _REC 1

WHILE !_REC <= !_NUM_REC

   # Set up the variable so I can append to it in the WHILE loop
   SETVAR _NREC ""

   # Initialize the column counter
   SETVAR _COL 1

   WHILE !_COL <= !_NUM_COL

      # If the column is numeric ("_FIELD_TYPE_X" = "N") convert
      #   the value to scientific notation using program SCI.QZPRGMS
      IF _FIELD_TYPE_!_COL = "N"
         IF _FIELD_![!_REC]_![!_COL] = ""
            SETVAR _FIELD_![!_REC]_![!_COL] 0
         ENDIF
         XEQ SCI.QZPRGMS !"_FIELD_![!_REC]_![!_COL]"
         SETVAR _FIELD_![!_REC]_![!_COL] "!_SCI"
      ENDIF

      # Append the variables "FIELD_X_Y" for the current row and use
      #   the "_MAX_LENGTH_X" variable to put spaces between columns
      #   for character fields.
      IF _FIELD_TYPE_!_COL = "C"
         SETVAR _NREC                                                &
            _NREC + _FIELD_![!_REC]_![!_COL] +                       &
            RPT(" ",_MAX_LENGTH_!_COL -                              &
            LEN('!'_FIELD_![!_REC]_![!_COL]''))
      ELSEIF _FIELD_TYPE_!_COL = "N"
         SETVAR _NREC _NREC + _FIELD_![!_REC]_![!_COL]
      ENDIF

      # Clean up some variables to make some room
      DELETEVAR _FIELD_![!_REC]_![!_COL]

      # Next column/field
      SETVAR _COL _COL + 1

   ENDWHILE

   ECHO !_NREC >>*!_OUTPUT_FILEQ

   # Next row/record
   SETVAR _REC _REC + 1

ENDWHILE

# *******************************************************************
# * Make the schema for the portable subfile.
# *******************************************************************

# Purge "_OUTPUT_FILE" if it already exists.

IF FINFO("!_OUTPUT_FILE","EXISTS")
   PURGE !_OUTPUT_FILE, TEMP
ENDIF

# ELEMENTS first

# Initialize the number of columns
SETVAR _COL 1
SETVAR _TOT_WID 0

WHILE !_COL <= !_NUM_COL

   # Numeric fields:
   IF _FIELD_TYPE_!_COL = "N"
      ECHO ELE FIELD!_COL NUM SIZ 19 LEA SIGN "-" &    >>!_OUTPUT_FILE
      ECHO    SIGNIFICANCE 1 PIC "^^^^^^^^^^"          >>!_OUTPUT_FILE
      ECHO                                             >>!_OUTPUT_FILE
   # Character fields:
   ELSEIF _FIELD_TYPE_!_COL = "C"
      ECHO ELE FIELD!_COL CHA SIZ ![_MAX_LENGTH_!_COL] >>!_OUTPUT_FILE
      ECHO                                             >>!_OUTPUT_FILE
   ENDIF

   # Keep a running total of the record width
   IF _FIELD_TYPE_!_COL = "N"
      SETVAR _TOT_WID _TOT_WID + 25
   ELSEIF _FIELD_TYPE_!_COL = "C"
      SETVAR _TOT_WID _TOT_WID + ![_MAX_LENGTH_!_COL]
   ENDIF

   # Next column
   SETVAR _COL _COL + 1

ENDWHILE

# Add a "filler" element to make the record width the same as
#    the file width.

SETVAR _FILLER_SIZE 1000 - !_TOT_WID

ECHO ELE FILLER CHA SIZ !_FILLER_SIZE                  >>!_OUTPUT_FILE
ECHO                                                   >>!_OUTPUT_FILE

#

ECHO SUBFILE !_OUTPUT_FILE ORGANIZATION DIRECT         >>!_OUTPUT_FILE
ECHO                                                   >>!_OUTPUT_FILE
ECHO RECORD !_OUTPUT_FILE                              >>!_OUTPUT_FILE

# ITEMS now

SETVAR _COL 1

WHILE !_COL <= !_NUM_COL
   # Numeric fields:
   IF _FIELD_TYPE_!_COL = "N"
      ECHO ITE FIELD!_COL DATATYPE PORT FLO SIZ 25     >>!_OUTPUT_FILE
   # Character fields:
   ELSEIF _FIELD_TYPE_!_COL = "C"
      ECHO ITE FIELD!_COL DATATYPE                   & >>!_OUTPUT_FILE
      ECHO    CHA SIZ ![_MAX_LENGTH_!_COL]             >>!_OUTPUT_FILE
   ENDIF

   # Next column
   SETVAR _COL _COL + 1

ENDWHILE

ECHO ITE FILLER DATATYPE CHA SIZ !_FILLER_SIZE         >>!_OUTPUT_FILE

# *******************************************************************
# * Clean Up
# *******************************************************************

PURGE NEWFILE,TEMP
DELETEVAR _@