November 15, 2001
====================================================================
>From the Edge: Creating Fixed Field ASCII Files
Section: Data Export
Chapter: Running R:BASE Your Way!
Platform: R:BASE 2000 (ver 6.5++) for DOS/Windows
Build: 1.842xRT03 and Higher
====================================================================
When your critical data is managed and stored in an Industrial-
Strength, Mufti-User, True-Relational and Multi-Platform Database
known as R:BASE, you often receive requests from IT departments of
other departments to obtain your data in a fixed field format so
that they can use your critical data for their databases, including
main frame databases.
You can provide such data as Fixed Filed ASCII format using SPUT
and SFIL functions of R:BASE!
Simple Example:
Assumptions:
Table Name: TableName Column Position
Column1: Column1 TEXT (Position 01 - 40)
Column2: Column2 TEXT (Position 41 - 80)
Column3: Column3 TEXT (Position 81 - 120)
Column4: Column4 TEXT (Position 121 - 128)
Step 1.
Create a Blank Output File
OUTP FileName.ASC
OUTP SCREEN
Step 2.
Set NULL values to Blank (or anything else)
SET NULL ' '
Step 3.
Pre-Define all Variable(s) before WHILE Loop.
SET VAR vColumn1 TEXT = NULL
SET VAR vColumn2 TEXT = NULL
SET VAR vColumn3 TEXT = NULL
SET VAR vColumn4 TEXT = NULL
SET VAR vWrite TEXT = NULL
Step 4.
Create a Variable to display BLANK LINE (128 characters wide)
SET VAR vWrite = (SFIL((CHAR(32)),128))
Step 5.
Get the record(s) from appropriate Table/View
SET ERROR MESSAGE 705 OFF
DROP CURSOR C#1
DECLARE C#1 CURSOR FOR SELECT Column1,Column2,Column3,Column4 +
FROM TableName WHERE ..... ORDER BY .....
OPEN C#1
FETCH C#1 INTO +
vColumn1 INDIC IColumn1, +
vColumn2 INDIC IColumn2, +
vColumn3 INDIC IColumn3, +
vColumn4 INDIC IColumn4
WHILE SQLCODE <> 100 THEN
SET VAR vWrite = (SPUT(.vWrite,.vColumn1,1))
SET VAR vWrite = (SPUT(.vWrite,.vColumn2,41))
SET VAR vWrite = (SPUT(.vWrite,.vColumn3,81))
SET VAR vWrite = (SPUT(.vWrite,.vColumn4,121))
OUTP FileName.ASC APPEND
WRITE .vWrite
OUTP SCREEN
SET VAR vWrite = (SFIL((CHAR(32)),128))
SET VAR vColumn1 = NULL
SET VAR vColumn2 = NULL
SET VAR vColumn3 = NULL
SET VAR vColumn4 = NULL
FETCH C#1 INTO +
vColumn1 INDIC IColumn1, +
vColumn2 INDIC IColumn2, +
vColumn3 INDIC IColumn3, +
vColumn4 INDIC IColumn4
ENDWHILE
DROP CURSOR C#1
SET ERROR MESSAGE 705 ON
-- CLEAR VARIABLES accordingly
Additional Note:
If any column happens to be other than TEXT, such as
CURRENCY, then:
Use the following Example: (substitute accordingly)
Refer to Step 3.
Predefine Variable as:
Assuming Column3 is CURRENCY
SET VAR vColumn3 CURRENCY = NULL
Refer to Step 5.
SET VAR vWrite = +
(SPUT(.VWrite,(CTXT(FORMAT(.vColumn3,'99,999,999.00'))),81))
---
Download PDF version of this article at:
http://www.rbase.com/FromTheEdge/CreatingFixedFieldAsciiFiles.pdf
Enjoy the power of R:BASE Functions!
Very Best Regards,
Razzak.
