*
* DBASCIIC.KEX
*
* When exporting database records from something like Access or dBASE to a
* comma-delimited ASCII text file it can be difficult to view the file with
* KEDIT since fields are not lined up in specific columns.  This macro will
* go through the file and determine the appropriate widths needed for each
* field (the widest element plus 2 for spacing) and then align the fields
* in vertical columns with the EXPAND command.
*
* This macro assumes that the comma-delimited file has string fields
* enclosed in double quotes and that these fields may contain commas within
* them.  By default, the macro also assumes that the first record of the
* file lists the field names.  If this is not the case then set the
* variable below called "FieldNames" to zero.
*
* This macro also strips out the double quotes around strings when it is
* done since doing so results in somewhat more readable output.  If you do
* NOT want this to happen then set the variable "StripQuotes" to zero.
*
* If the macro determines that the current WIDTH setting would be exceeded
* by aligning the fields then an error is issued and the process is
* aborted.  You can then restart KEDIT and specify a wider WIDTH setting
* and try again.
*
* After determining the field locations the macro issues a SET TABS command
* for these columns.  Since SET TABS can only accept 32 discrete tab
* positions only records with 32 or less fields can be handled.
*
* Given a source file that looks like this:
*
* "Name","Address","Age"
* "John","Hartford, CT",26
* "Alice","Provo, UT",33
* "Bill","San Diego, CA",51
*
* the macro will convert it to this:
*
* Name   |Address        |Age
* ------------------------------
* John   |Hartford, CT   |26
* Alice  |Provo, UT      |33
* Bill   |San Diego, CA  |51
*
* Works with:
*
*    KEDIT for Windows 1.5
*    KEDIT 5.0 for DOS
*    KEDIT 5.0 for OS/2
*
* Original author: Kent Downs, MSG, 8/96
*

* Change this to 0 if your original file does not begin with a list of field
* names.
FieldNames = 1

* Change this to 0 if you do not want double quotes stripped from the file.
StripQuotes = 1

":1"
"preserve"
"set autosave off"
* ASCII tab character
tab = d2c(9)
prompt = "Determining optimal field widths. Please wait..."

* This might take a while
say prompt
"refresh"

* The first record should have comma delimiters. Count them to determine how
* many fields are in each record. This is complicated by the fact that a
* quoted string can have embedded commas which are not field delimiters.
NumFields = 0
FirstRecord = 1
call ProcessLine
FirstRecord = 0
say NumFields

if NumFields > 32 then do
   say "DBASCIIC Error 1: Too many fields per record for this macro."
   say "                  The limit is 32 fields."
   * Get rid of the tabs we've inserted
   "nomsg c/"tab"|/,/ all *"
   "set alt 0 0"
   exit 1
   end

"down 1"

n = size.1()
RefreshInterval = n % 20
j = 1
do while \focuseof()
   call ProcessLine
   "down 1"
   if j >= RefreshInterval then do
      say prompt
      "refresh"
      j = 1
      end
   else j = j + 1
   end

* It's quite possible that we'll exceed the current WIDTH setting
n = 0
do i = 1 to NumFields
   n = n + FieldWidth.i + 2
   end

if n > width.1() then do
   say "DBASCIIC Error 2: Expanded text will exceed the current WIDTH setting."
   say "                  Restart KEDIT with a WIDTH of at least" n"."
   * Get rid of the tabs we've inserted
   "nomsg c/"tab"|/,/ all *"
   "set alt 0 0"
   exit 2
   end

* Build a SET TABS command using the columns we've determined
TabCommand = "set tabs"
DisplayWidth = 0
do i = 1 to NumFields
   * Put in an extra space between fields
   DisplayWidth = DisplayWidth + FieldWidth.i + 2
   TabCommand = TabCommand DisplayWidth
   end

TabCommand

"expand all"
":1"

if FieldNames Then do
   * Generally the first line lists the field names. Put a divider in
   * (optional)
   "i" copies("-", DisplayWidth)
   end

"top"

if StripQuotes then 'nomsg c/"// all *'

* We probably don't want to save this file so reset the alteration count to 0
"set alt 0 0"
exit 0

* Determine the field widths necessary for the current line
ProcessLine:
   record = strip(curline.3())

   NewLine = ""
   i = 1
   do while length(record) > 0
      field = GetNextField()

      NewLine = NewLine || field
      if length(record) > 0 then NewLine = NewLine || tab"|"

      if FirstRecord = 1 then do
         FieldWidth.i = length(field)
         NumFields = NumFields + 1
         end
      else FieldWidth.i = max(length(field), FieldWidth.i)
      i = i + 1
      end

   "replace" NewLine
return

* Given the current database record, pick off the leading field and return this
GetNextField:
   if substr(record, 1, 1) = '"' then do
      * This is a quoted field
      n = pos('"', record, 2)
      * Pick the field off of the remainder of the record
      field = substr(record, 1, n)
      record = strip(substr(record, n + 1))
      end
   else do
      n = pos(',', record)
      if n = 0 then do
         * Only one field left
         if record = "," then field = ""
            else field = record

         record = ""
         end
      else do
         * Pick the field off of the remainder of the record
         field = substr(record, 1, n - 1)
         record = strip(substr(record, n))
         end
      end

   * Step over the comma field delimiter (if any)
   if length(record) > 0 then do
      record = strip(substr(record, 2))
      if record = "" then record = " "
      end

return field