The Director’s Chair
Time Saver
by Sheldon Smith
New Feature
Advanced Techniques
The Resource Analyzer Challenge
FOCUS 101
Quick Tips
User Group Meeting Schedule

A New Twist on Comma-Delimited Files

Say you wish to extract data from a mainframe database so your users can work on it with Excel. You would like to create a compatible extract file, but you find that getting the field names in the first record is harder than you thought. And, with today’s large databases and file transfers the way they are, you certainly do not want to pass along the blank spaces normally associated with fixed-length records to your users.

What you have in mind is a comma-delimited file, one that is automatically acceptable to Excel, right down to the extension (.csv). In the past, when you held the output of a FOCUS database you could manually edit the data to get a file, which could then be acceptable to Excel. Now, the new HOLD FORMAT COMT feature in FOCUS will solve your problem without entering an editor. Here is how you use it: 

TABLE FILE CAR
SUM SALES AS UNITS
BY COUNTRY BY CAR BY BODYTYPE
ON TABLE HOLD FORMAT COMT AS UNITS
END

This produces two output files, UNITS MASTER and UNITS CSV. Here is the master:

FILE=UNITS      ,SUFFIX=COMT
SEGNAME=UNITS   ,SEGTYPE=S03
FIELDNAME   =COUNTRY      ,E01    ,A10   ,A10  ,$
FIELDNAME   =CAR          ,E02    ,A16   ,A16  ,$
FIELDNAME   =BODYTYPE     ,E03    ,A12   ,A12  ,$
FIELDNAME   =SALES        ,E04    ,I6    ,A06  ,$

This is the data file:

"COUNTRY","CAR","BODYTYPE","UNITS"
"ENGLAND","JAGUAR","CONVERTIBLE",0
"ENGLAND","JAGUAR","SEDAN",12000
"ENGLAND","JENSEN","SEDAN",0
"ENGLAND","TRIUMPH","HARDTOP",0
"FRANCE","PEUGEOT","SEDAN",0
"ITALY","ALFA ROMEO","COUPE",12400
"ITALY","ALFA ROMEO","ROADSTER",13000
"ITALY","ALFA ROMEO","SEDAN",4800
"ITALY","MASERATI","COUPE",0
"JAPAN","DATSUN","SEDAN",43000
"JAPAN","TOYOTA","SEDAN",35030
"W GERMANY","AUDI","SEDAN",7800
"W GERMANY","BMW","SEDAN",80390

You can transfer this to the PC and import it into Excel without further modification. Now let’s say you want to load an Excel extract file into FOCUS, DB2, or make it a transaction input. With SUFFIX=COMT you do not need to add the comma/dollar sign delimiter (,$) to the end of each record, as you would with SUFFIX=FIX. The comma (,) is the default delimiter and you may use double quotes to surround your alpha data. All fields must have an actual format of alpha. Once you have an MFD that accurately defines your data, you can use a TABLE request with HOLD FORMAT FOCUS or HOLD FORMAT DB2 to build the FOCUS or DB2 database.

Back Next