Easy Exports with a Simple Template

by Mike Hanson

Published 1997-12-01    Printer-friendly version

Download the code here

Templates can come in all shapes and sizes. You might make the assumption that I like to write only sophisticated templates to solve complex and intriguing problems. Quite the contrary, I use templates to enhance my productivity for most of the problems that I encounter. That’s the benefit of Clarion: If the system isn’t productive with the things that you need to do, then you can write a template to improve it.

One of the requirements for most of my programs is the ability to export data for use in other programs (like spreadsheets and word processors). I have created a commercial template that allows the user to pick and choose fields for export, but this can be overkill. Much of the time the user simply wants everything exported to a file, without intervention. For that matter, they may want to export multiple files all at once.

In my case, I like to use CSV files for exports. (This is the BASIC file driver in Clarion.) It’s a good choice for a universally understood file format. Let’s look at how we might do this by hand.

1. Copy and paste the source file definition in the dictionary. The new file’s Driver must be changed to "BASIC" and the Pathname specified. Finally, any numeric picture fields (like dates and phone numbers) must be changed to strings, so as to accommodate the formatted output.
2. Add a Process procedure to access the records in the source file. The new target file would be specified as an "Other" file for the procedure.
3. Add the file creation and opening statements at the start of the procedure.
4. Add the file closing statement at the end of the procedure.
5. In the "Activity" embed, assign the values to the target fields, and then add the target record.

In my opinion, that’s too much work to do for each file. I’ll have too many extra definitions in the dictionary and, if my source file changes, I have to remember to change the exported file too. Therefore, this is a perfect situation for a template.

Let’s look at what we can expect the template to do for us. I’ll number the points correspondingly with those above.

1. The target file definition can be derived from the source definition. It can also be declared locally, instead of globally, which is generally a better programming practice.
2. We must still create the Process procedure manually, because no template can be smart enough to predict what we want to do.
3. The file creation and opening statements can easily be handled by the template.
4. Ditto for the close statement.
5. Of course, the template can also do the field assignments, because it knows everything about the file from the dictionary.

So we’ve eliminated four out of five steps. There is one additional step, though, and that’s populating the extension template into the procedure. Beyond that, everything else is handled automatically by the template.

There is one more thing that I want the template to do, which would have been somewhat difficult to do manually: I optionally want the first record of the file to contain the field names. The reason this is difficult is that all of the field names are strings. A string cannot be outputted in a numeric field. Therefore, we would have had to create a separate file definition just to output the header records. These header records would had to have been specified manually: a tedious and error-prone operation.

To alleviate this problem, the template will not use the BASIC file driver. Instead, it will use the ASCII file driver (one string per line), and it will format the records individually. Because the template is writing the code, this complexity is not an issue. In fact, it probably takes less code internally to use an ASCII file with a single field than it does to create a complex BASIC file with multiple fields.

As I mentioned earlier, the target ASCII file definition will be defined locally. If you planned on creating many export procedures within the same program, you could change the template to define the file in global data, then just assign the filename as required. This is a good exercise for you to perform on your own.

First we have to write the header of the extension template. It contains the name in the #EXTENSION line, plus any necessary #PROMPTs to control code generation. Our header looks like this:

#EXTENSION(mhExportBasic,'Export Records to CSV File using Process'),DESCRIPTION('Export Records')
#BOXED('Export Records to CSV File')
  #PROMPT('Filename:',@S80),%ExportFilename,REQ,DEFAULT('EXPORT.CSV')
  #PROMPT('Maximum Record Size:',@N6),%MaxRecSize,DEFAULT(16000),REQ
  #PROMPT('Include field names as first record',CHECK),%IncludeHeader,DEFAULT(%True),AT(10,,180)
#ENDBOXED

You’ll notice that we prompt for a filename. The default is "EXPORT.CSV", but you can change it to any constant, or a variable prefixed with an exclamation point. If you decide to use a variable, it must be global, module or static local.

Next, you can control the maximum size of the export record. (If you decide to declare this file globally, you should probably remove this option and just set it for something big like 32,000.) Finally, adding the field names as the first record is optional.

Now we must generate the file definition. This is done in the %DeclarationSection embed:

#AT(%DeclarationSection)
  #IF(SUB(%ExportFilename,1,1) = '!')
MH_Export       FILE,DRIVER('ASCII'),NAME(%(SUB(%ExportFilename,2,LEN(%ExportFilename)-1))),PRE(MH_Exp),CREATE
  #ELSE
MH_Export       FILE,DRIVER('ASCII'),NAME('%ExportFilename'),PRE(MH_Exp),CREATE
  #ENDIF
                  Record RECORD
                    STRING(%MaxRecSize)
                  END!RECORD
                END!FILE
#ENDAT

The only interesting thing here is the name determination. If the first character is an exclamation point, then it uses the remaining letters as the name of a variable. Otherwise, it encloses the entire value in a constant string.

We are using the ASCII file driver, and there is a good chance that you don't have any other files of this type in your application. Therefore, we must have the template tell Clarion to add the ASCII file driver to the project. This is done in the %CustomGlobalDeclarations embed. This is a special embed that is always processed during generation, even when no code is actually produced. (If you used another embed point instead, you would discover that the desired library is forgotten when no code is generated.) Here's the embed:

#AT(%CustomGlobalDeclarations)
  #FIX(%Driver, 'ASCII')
  #ADD(%UsedDriverDLLs,%DriverDLL)
  #PROJECT(%DriverLIB)
#ENDAT

The next step is to insert the CREATE and OPEN statements, and to add optionally the header record to the file. This is done in the %AfterFileOpen embed:

#AT(%AfterFileOpen)
CREATE(MH_Export)
OPEN(MH_Export)
  #IF(%IncludeHeader)
MH_Exp:Record = |
    #FIX(%File, %Primary)
    #FOR(%Field)
      #IF(INSTANCE(%Field) < ITEMS(%Field))
'"%FieldID",' & |
      #ELSE
'"%FieldID"'
      #ENDIF
    #ENDFOR
ADD(MH_Export)
  #ENDIF
#ENDAT

Now you can see the method that is used to produce the output record. We just assign a multi-part string value to the target record, with each field being represented on a separate line. The resulting code might look like this:

MH_Exp:Record =       |
  '"Comments",'     & |
  '"No",'           & |
  '"Flag",'         & |
  '"LName",'        & |
  '"FName",'        & |
  '"City",'         & |
  '"State",'        & |
  '"Zip",'          & |
  '"Phone",'        & |
  '"Sex",'          & |
  '"MaritalStat",'  & |
  '"Retired",'      & |
  '"Birthday",'     & |
  '"Sales"'

Notice how the #IF(INSTANCE(%Field) < ITEMS(%Field)) condition prevents the trailing concatenation characters for the last field. The resulting record value would be:

"Comments","No","Flag","LName","FName","City","State","Zip","Phone","Sex","MaritalStat","Retired","Birth

The CLOSE statement is done in the %BeforeFileClose embed:

#AT(%BeforeFileClose)
CLOSE(MH_Export)
#ENDAT

Finally, we have to export the data record in the %ProcessActivity embed. This technique is quite similar to that used in exporting the header record. The difference is that we have three different field types: string, number and formatted.

String fields include any fields that hold simple string values that must be clipped for export, and enclosed in quotes.

Number fields include any fields that have simple numerical values that are exported without quotes.

Formatted fields include any fields that are numbers to be interpreted as something else (like dates, phone numbers, etc.). These are fixed-length strings, enclosed in quotes. The FORMAT statement is used to produce the required string value.

The type can be determined by looking at the %FieldPicture. If it begins with @S, then it should be a clipped string. If it begins with @N, then it is a simple numeric. Anything else is a formatted field. The code looks like the following:

#AT(%ProcessActivity)
MH_Exp:Record = |
  #FIX(%File, %Primary)
  #FOR(%Field)
    #CASE(UPPER(SUB(%FieldPicture,2,1)))
    #OF('N')
      #IF(INSTANCE(%Field) < ITEMS(%Field))
%Field & ',' & |
      #ELSE
%Field
      #ENDIF
    #OF('S')
      #IF(INSTANCE(%Field) < ITEMS(%Field))
'"' & CLIP(%Field) & '",' & |
      #ELSE
'"' & CLIP(%Field) & '"'
      #ENDIF
    #ELSE
      #IF(INSTANCE(%Field) < ITEMS(%Field))
'"' & FORMAT(%Field,%FieldPicture) & '",' & |
      #ELSE
'"' & %Field & '"'
      #ENDIF
    #ENDCASE
  #ENDFOR
ADD(MH_Export)

The generated source might look like this:

MH_Exp:Record = |
'"' & CLIP( Cus:Comments) & '",' & | !Cus:Comments
Cus:No & ',' & | !Cus:No
Cus:Flag & ',' & | !Cus:Flag
'"' & CLIP( Cus:LName) & '",' & | !Cus:LName
'"' & CLIP( Cus:FName) & '",' & | !Cus:FName
'"' & CLIP( Cus:City) & '",' & | !Cus:City
'"' & CLIP( Cus:State) & '",' & | !Cus:State
'"' & CLIP( Cus:Zip) & '",' & | !Cus:Zip
'"' & FORMAT(Cus:Phone,@p(<<<)###-####pb) & '",' & | !Cus:Phone
'"' & CLIP( Cus:Sex) & '",' & | !Cus:Sex
'"' & CLIP( Cus:MaritalStat) & '",' & | !Cus:MaritalStat
Cus:Retired & ',' & | !Cus:Retired
'"' & FORMAT(Cus:Birthday,@D9b) & '",' & | !Cus:Birthday
Cus:Sales !Cus:Sales

Well, that's all there is to it. You now have a simple extension template that can be added to any Process procedure to export your records to a CSV file. Of course, you can change the range and filter settings for the Process to restrict the exported records to a subset of your total data file. You can also call multiple Processes from a Source procedure to export a number of files. The real benefit here is that you have the ability to export data without wasting a bunch of your valuable time.

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942