Export Multi-line Records with Suprtool and Perl

By Dave Lo, Robelle Tech Support

When exporting Image datasets to other sources, such as Access or SQL, one of the common transfer file formats used is the "comma separated values" or CSV format. This is generally a flat file with one record per line, quotes around fields and commas between fields.

    "cust-id","comment"
    "12A","want web delivery"

Suprtool can easily produce this type of file with the OUTPUT,PRN command or with the STExport module. However, there is field type that cannot be handled this way directly: the Notes fields. These fields span multiple records even though they are logically one field. They usually look like the following:

    cust-id  seq-num  comment

    12A      001      want web delivery but
    12A      002      limited by bandwidth,
    12A      003      so use FTP.
    88X      001      Send doc in "PDF" format.
    99Z      001      Make sure all changes
    99Z      002      are approved by John.

In the CSV transfer file, we want to merge the related records into a single record, and also convert any double quotes to single quotes (you need to do something about internal quotes if you want to use quotes around fields in the CSV file!). For example:

    "12A","want web delivery but limited by bandwidth, so use FTP."
    "88X","Send doc in 'PDF' format."
    "99Z","Make sure all changes are approved by John."

How to Combine Multiple Notes into a Single Text Field?

Although Suprtool cannot produce this format directly, it can front-end the database extract portion, and let a straight-forward Perl script do the merging. To learn more about Perl, read our introduction to Perl.

1. First use Suprtool to dump the Notes dataset, sorted by customer number and sequence number:

    Get  Notes
    Sort cust-id
    Sort seq-num
    Ext  cust-id, ^i, comment  {tab delimited}
    Out  notefile
    Xeq

2. Send the resulting notefile to your other OS/directory space where Perl is available.

3. Save the following Perl script as merge.pl :

    # adjust these according to your in/out data format
    #
    #   Input:
    #
    $in_field_sep = "\t";

    #
    #   Output:
    #
    $quote = '"';
    $alt_quote = "'";
    $rec_sep = "\n";
    $field_sep = ",";
    $note_term = " ";

    $keycount = 0;
    $prev = "";
    while ($line = <STDIN>) {
      $line =~ /([^$in_field_sep]+)$in_field_sep(.*)/;
      $key = $1;
      $text = $2;
      $text =~ s/$quote/$alt_quote/g;
      if ($key ne $prev) {
        if ($keycount>0) {  # close previous quote
          print $quote . $rec_sep;
        }
        print $quote . $key . $quote . $field_sep . $quote;
        $keycount++;
      }
      print $text . $note_term;
      $prev = $key;
    }
    print $quote . $rec_sep;

4. Run the Perl script against your notefile to produce the CSV file.

    perl merge.pl <notefile >note.csv

How to Customize the Text Field

If your CSV file format requirements are slightly different, you can modify the Input/Output variables to suit your needs. Some common changes may include:

* Use two double-quotes to represent a single double-quote.

    $alt_quote = '""';

For example,

    "88X","Send doc in ""PDF"" format."

* Use a newline to terminate each portion of the note. This is often used when a single logical note is more than 64Kbytes long.

    $note_term = "\n";

For example,

    "12A","want web delivery but
    limited by bandwidth,
    so use FTP.
    "

* Use a tab to separate fields.

    $field_sep = "\t";

* There is no input field separator. For example, the key might be a 4-digit alphanumeric string, followed immediately by the comment.

    A123Awant web delivery but
    A123Alimited by bandwidth,
    A123Aso use FTP.
    B888XSend doc in "PDF" format.
    Z999Make sure all changes
    Z999are approved by John.

In this case, change the parsing of the "$line =~ ..." to

    $line =~ /([A-Za-z0-9]{4})(.*)/;

As you can see, a little bit of Perl can indeed live up to its name of being a "Practical Extraction and Report Language".

---

To learn more about exporting, including MS Access and Excel, read our tutorial, Sharing HP e3000 Data to the World.

You might also be interested in two Suprtool articles on exporting 3000 data to mySQL and exporting to Excel, and exporting to Oracle.

Or another article on exporting to XML, with an introduction to XML for newcovers.