Author Topic: Need some help with REXX  (Read 9790 times)

Per E. Johannessen

  • Sr. Member
  • ****
  • Posts: 251
  • Karma: +3/-0
    • View Profile
Need some help with REXX
« on: August 30, 2016, 04:44:38 pm »
Hi,

I've got some txt-files that I have to "manipulate" before importing them to a database, simple enough
for someone who knows REXX, but unfortunately I'm not (yet) one of those.
Does anyone know about a sample that reads a txt-file, does some manipulation to each line and
writes to a new txt-file? (I'm stuck with the "manipulation" part  :o )

The files are for business purposes and I can't go public with them, however, I can provide samples
if somone's interested. If so, please pm me.





mcs_5

  • Guest
Re: Need some help with REXX
« Reply #1 on: August 30, 2016, 07:04:06 pm »
What manipulation do you need?

Why don't you post a small sample file here, with the secret stuff removed?

Per E. Johannessen

  • Sr. Member
  • ****
  • Posts: 251
  • Karma: +3/-0
    • View Profile
Re: Need some help with REXX
« Reply #2 on: August 30, 2016, 09:17:54 pm »
Attached one example

mcs_5

  • Guest
Re: Need some help with REXX
« Reply #3 on: August 30, 2016, 10:19:24 pm »
So it looks like fixed width fields?

You can use linein and lineout for reading/writing files, but I guess you have that part done.

To extract parts of a line, you can use word() and substr().

Here's a word() example:

Code: [Select]
[C:\]rexxtry
  REXXTRY.CMD lets you interactively try REXX statements.
    Each string is executed when you hit Enter.
      Enter 'call tell' for a description of the features.
  Go on - try a few...             Enter 'exit' to end.
str = "12.08.16 22.08.16  20690       517902 Faktura               00005179023                          -1.788,00      -1.788,00"
  ................................................ REXXTRY.CMD on OS/2
say word(str, 1)
12.08.16
  ................................................ REXXTRY.CMD on OS/2
say word(str, 2)
22.08.16
  ................................................ REXXTRY.CMD on OS/2
say word(str, 3)
20690
  ................................................ REXXTRY.CMD on OS/2
say word(str, 4)
517902
  ................................................ REXXTRY.CMD on OS/2
say word(str, 5)
Faktura
  ................................................ REXXTRY.CMD on OS/2
say word(str, 6)
00005179023
  ................................................ REXXTRY.CMD on OS/2
say word(str, 7)
-1.788,00
  ................................................ REXXTRY.CMD on OS/2
say word(str, 8)
-1.788,00
  ................................................ REXXTRY.CMD on OS/2

To extract the customer name, use substr(). Example:
Code: [Select]
[C:\]rexxtry
  REXXTRY.CMD lets you interactively try REXX statements.
    Each string is executed when you hit Enter.
      Enter 'call tell' for a description of the features.
  Go on - try a few...             Enter 'exit' to end.
str = "Konto ...:      12345 Customer One AS                       Bank   9876 54 32109            Saldo NOK ...:      -1.788,00"
  ................................................ REXXTRY.CMD on OS/2
kunde = substr(str, 23, 37);
  ................................................ REXXTRY.CMD on OS/2
kunde = strip(kunde);
  ................................................ REXXTRY.CMD on OS/2
say kunde
Customer One AS
  ................................................ REXXTRY.CMD on OS/2

You have a variable number of datalines, so you can check if the first character on the line is blank, to find out if they have "stopped".

More lines sample:
Code: [Select]
[C:\]rexxtry
  REXXTRY.CMD lets you interactively try REXX statements.
    Each string is executed when you hit Enter.
      Enter 'call tell' for a description of the features.
  Go on - try a few...             Enter 'exit' to end.
str = "07.04.16 20.08.16  20333    959219158 Faktura               0572432251629592191584               -8.733,20      -8.733,20"
  ................................................ REXXTRY.CMD on OS/2
if (substr(str, 1, 1) \= ' ') then say "Flere linier!!!"
Flere linier!!!
  ................................................ REXXTRY.CMD on OS/2

And the other option:
Code: [Select]
[C:\]rexxtry
  REXXTRY.CMD lets you interactively try REXX statements.
    Each string is executed when you hit Enter.
      Enter 'call tell' for a description of the features.
  Go on - try a few...             Enter 'exit' to end.
str = "                                                                                            ────────────── ──────────────"
  ................................................ REXXTRY.CMD on OS/2
if (substr(str, 1, 1) == ' ') then say "Ikke flere linier";
Ikke flere linier
  ................................................ REXXTRY.CMD on OS/2

As long as you know the format of the input file, it looks like a solvable problem :)

Mikkel

Per E. Johannessen

  • Sr. Member
  • ****
  • Posts: 251
  • Karma: +3/-0
    • View Profile
Re: Need some help with REXX
« Reply #4 on: August 30, 2016, 11:48:10 pm »
Yes, infile has fixed widths.
I have done some tries with with linein and lineout and somehow got it to work.
Trouble for me is writing the code to extract Konto, CustomerName, Bank from one line and then append
the "items" starting with Dato etc. In this example the first Konto has only one "item-line" and the second
Konto has two.
Each Konto always has a minimum of one item line, but there is no maximum number of item lines, rarely more
than 70, though. Also, the thousand separator "." must be removed.

Per E. Johannessen

  • Sr. Member
  • ****
  • Posts: 251
  • Karma: +3/-0
    • View Profile
Re: Need some help with REXX
« Reply #5 on: August 31, 2016, 04:03:47 pm »
Made a new infile which hopefully makes what I'm trying to achieve it a bit clearer.
Added a third item line under Konto 67890 to illustrate that the field ItemId sometimes can be blank.
All in red text should be ignored. Normally the infile consists of several pages and on each page the first three lines;

1. Company AS
2.
3. ÅPNE KREDITORPOSTERINGER

is a header.

Text in blue needs to be extracted and added to each corresponding item line, green text, in order to write the new csv-file.

Please let me know if unclear.


Jan-Erik Lärka

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 275
  • Karma: +5/-0
    • View Profile
Re: Need some help with REXX
« Reply #6 on: August 31, 2016, 06:31:44 pm »
Hello,
this rexx script can successfully parse the first file.
Please provide the second (PDF) as a text file as well if possible for testing.

Run: raiko infile.txt outfile.txt

Adjusted code below:
Code: [Select]
/*
 * Filename: raiko.cmd
 *   Author: JAN-ERIK
 *  Created: Wed Aug 31 2016
 *  Purpose: Parse information from file
 *  Changes:
 */

IF ARG(1) <> '' THEN
DO
    PARSE UPPER VALUE ARG(1) WITH f_name'.TXT' o_name
    f_name = f_name'.TXT'
END
IF LENGTH( STRIP( o_name ) ) = 0 THEN
DO
    CALL CHAROUT 'STDOUT', 'Spara fil som:'
    PARSE PULL o_name
END

IF LENGTH( STRIP( f_name ) ) = 0 THEN RETURN 1
IF LENGTH( STRIP( o_name ) ) = 0 THEN RETURN 2
IF STREAM( f_name, 'C', 'QUERY EXISTS' ) = '' THEN RETURN 3
f_size = STREAM( f_name, 'C', 'QUERY SIZE' )
IF f_size < 100 THEN RETURN 4
input = CHARIN( f_name, 1, f_size )
CALL STREAM f_name, 'C', 'CLOSE'

header = COPIES( '─', 121 )
sum_end = COPIES( '═', 14 )
crlf = D2C(13)D2C(10)

output = ''
PARSE VALUE input WITH company(header)open_cred 'Dato 'dd'.'mm'.'yy' 'hh':'mi'  Side'page(crlf)input

CALL NxtCust
CALL CHAROUT STRIP( o_name ), output
CALL STREAM STRIP( o_name ), 'C', 'CLOSE'   
RETURN 0

NxtCust:
    PARSE VALUE input WITH 'Per dato' . ':' per_dato(crlf)(crlf)'Konto' . ':' cid customer 'Bank' bnr 'Saldo' currency . ':' saldo(crlf)input
    PARSE VALUE input WITH 'Kontaktperson:' contact 'Kreditmaks' . ':'max_credit(crlf)input
    PARSE VALUE input WITH 'Telefon' . ':' phonenr(crlf) . (crlf)input
Rerun:
    PARSE VALUE input WITH . (crlf) . (crlf)input
    DO WHILE DATATYPE( LEFT( input, 2 ), 'W' )
       PARSE VALUE input WITH dato 10 forfall 19 bilag 26 faktura 39 tekst 61 bestalld 87 valuta 93 belop 108 rest(crlf)input
       PARSE VALUE belop WITH pre'.'post
       belop = pre||post
       PARSE VALUE rest WITH pre'.'post
       rest = pre||post
       output = output||STRIP( cid )';'STRIP( customer )';'STRIP( bnr )';'STRIP( dato )';'STRIP( forfall )';'STRIP( bilag )';'STRIP( faktura )';'STRIP( tekst )';'STRIP( bestalld )';'STRIP( belop )';'STRIP( rest )';'crlf
    END
    PARSE VALUE input WITH . (crlf) . ? . sa_belop sa_rest(crlf) . (sum_end)' '(sum_end)(crlf) . (crlf)input
    IF SUBWORD( input, 1, 1 ) = 'Dato' THEN CALL ReRun
    IF SUBWORD( input, 1, 2 ) = 'Per dato' THEN CALL NxtCust
RETURN 0
« Last Edit: August 31, 2016, 10:58:59 pm by Jan-Erik Lärka »

Per E. Johannessen

  • Sr. Member
  • ****
  • Posts: 251
  • Karma: +3/-0
    • View Profile
Re: Need some help with REXX
« Reply #7 on: August 31, 2016, 07:39:54 pm »
Thanks, Jan-Erik.
Attached is the second file where I've also added "Customer Three" and four item lines.
Will try your code asap.

Per E. Johannessen

  • Sr. Member
  • ****
  • Posts: 251
  • Karma: +3/-0
    • View Profile
Re: Need some help with REXX
« Reply #8 on: August 31, 2016, 08:35:21 pm »
Great, it seems to work fine.

One little thing, DBExpert claimed it couldn't open the file for importing to a table, even though I saw that the file was created and existed in the right place. Turned out that the filename  of the output file started with an empty space, " outfile.txt" instead of "outfile.txt".

Jan-Erik Lärka

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 275
  • Karma: +5/-0
    • View Profile
Re: Need some help with REXX
« Reply #9 on: August 31, 2016, 10:58:27 pm »
Yes, I can see it now

Adjust 2 lines in the code, add a call to STRIP for the output filename:
CALL CHAROUT STRIP( o_name ), output
CALL STREAM STRIP( o_name ), 'C', 'CLOSE'

Jan-Erik Lärka

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 275
  • Karma: +5/-0
    • View Profile
Re: Need some help with REXX
« Reply #10 on: September 01, 2016, 09:10:17 am »
The design of the script and how it is supposed to handle the input data.

1. Handle parameters to the script

default file names f_name and o_name if no parameter otherwise the variables f_name and o_name contain the actual files entered. Note that the script assume that the input file use the extension .txt.

2. Input file

Check that the input name (f_name ) point to a file and that it exist and contain data

3. Define some vital parts of the input file

The "line" made up of 122 "─" characters tell us where to start.
The script can only parse the rest if it exist.
The 2 double line tell us where the sum end for each section.

4. Extract the vital parts of the file

Define the variable that will hold the output data and ensure it is empty.
Parse the file and use the header to get to the part with important data.
Extract other info as well even though the code doesn't use it in this version.
The extra data could be used to create output file name or ... ?

5. Extract data
This version of the code call itself as needed since this part of the file repeat itself¹.
First skip the row titles and the separator lines, instead extract each row data associated with it.
Only repeat if the next line start with a number.
Extract the sum and parse away the double lines defined.

If the row after begin with "Dato", we have more data for that customer¹
If the row after begin with "Per dato", we have to parse data for another customer¹

6. Return so we can continue
Save and close the output file ( o_name ) at the end as the output has been filled with data


¹) Rexx will fail if it has to do to many recursive calls (the function call itself to many times).
A loop (DO WHILE) may resolve the problem, if it appear.
« Last Edit: September 01, 2016, 10:40:47 am by Jan-Erik Lärka »

Per E. Johannessen

  • Sr. Member
  • ****
  • Posts: 251
  • Karma: +3/-0
    • View Profile
Re: Need some help with REXX
« Reply #11 on: September 01, 2016, 12:22:12 pm »
Thanks a lot for the explanation, it really helps for understanding the script.

Per E. Johannessen

  • Sr. Member
  • ****
  • Posts: 251
  • Karma: +3/-0
    • View Profile
Re: Need some help with REXX
« Reply #12 on: September 06, 2016, 05:08:31 pm »
Have another type of txt-files that I regurarly work with. Importing them with DBE, fixed lenght  fields, is easy enough, but for some unknown reason it  fails to import all the amounts to the table. Only one or two amounts are lost  when importing files that have up to approx 300 lines.
Don't know if it's a bug in DBE, but at least importing csv-files works correctly.

I'm in the process of "absorbing" the script you made for the first scenario and I think that script will be easier to understand if I start with a simpler script for what I describe in the attached info.txt

Jan-Erik Lärka

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 275
  • Karma: +5/-0
    • View Profile
Re: Need some help with REXX
« Reply #13 on: September 06, 2016, 07:24:09 pm »
This script resemble the first one, especially the beginning.

The part that parse the input file should be easier to follow as it use just a few lines within a loop.

This script can even parse the info.txt-file correctly, and leave out the comments and the part with the correct result.

Code: [Select]
/*
 * Filename: raiko62.cmd
 *   Author: Jan-Erik
 *  Created: Tue Sep  6 2016
 *  Purpose:
 *  Changes:
 */
IF ARG(1) <> '' THEN
DO
    PARSE UPPER VALUE ARG(1) WITH f_name'.TXT' o_name
    f_name = f_name'.TXT'
END
IF LENGTH( STRIP( o_name ) ) = 0 THEN
DO
    CALL CHAROUT 'STDOUT', 'Spara fil som:'
    PARSE PULL o_name
END

IF LENGTH( STRIP( f_name ) ) = 0 THEN RETURN 1
IF LENGTH( STRIP( o_name ) ) = 0 THEN RETURN 2
IF STREAM( f_name, 'C', 'QUERY EXISTS' ) = '' THEN RETURN 3
f_size = STREAM( f_name, 'C', 'QUERY SIZE' )
IF f_size < 100 THEN RETURN 4
input = CHARIN( f_name, 1, f_size )
CALL STREAM f_name, 'C', 'CLOSE'

crlf = D2C(13)D2C(10)

output = ''
DO WHILE LENGTH( input ) > 0
   IF DATATYPE( SPACE( TRANSLATE( SUBWORD( input, 1, 1 ), , '-,' ), 0 ), 'N' ) THEN
   DO
      PARSE VALUE input WITH val 20 acnt 26 dat 35 ben 56 lbl 59 cust(crlf)input
      output = output||SPACE( TRANSLATE( val,, '.' ), 0 )';'STRIP( acnt )';'STRIP( dat )';'STRIP( ben )';'STRIP( lbl )';'STRIP( cust )';'crlf
   END
   ELSE PARSE VALUE input WITH . (crlf)input
END
CALL CHAROUT STRIP( o_name ), output
CALL STREAM STRIP( o_name ), 'C', 'CLOSE'

Regards,
//Jan-Erik
« Last Edit: September 09, 2016, 06:41:34 pm by Jan-Erik Lärka »

Per E. Johannessen

  • Sr. Member
  • ****
  • Posts: 251
  • Karma: +3/-0
    • View Profile
Re: Need some help with REXX
« Reply #14 on: September 06, 2016, 07:39:14 pm »
Great, Jan-Erik, your help really makes my life easier, and I think it's of good value to others as well.

Rgds Per