I have a dataset (textfile) with variabele length fields and variable number of strings.
Is there a smart way to read this and use as database?

example:

keyfield keyname manyfieldsstring
123      operdoc abdss sjsjsj sjskaa sjswkwk sjsjsjsj jddkskk kwkqa djsaa kskka
125      subdoc1 sajaaa ejwjw wwwwwjwww wjwjqkak dkdodes sjs sj ajaaaa sjskdld ddkdkssa skskaak
112      subdoc2 sjsjaa sh ahaa ah sj sjs dkdodes

I was thinking of a segment with a setting occurs=variable but that would only work if the length is fixed.

This message has been edited. Last edited by: FrankDutch,

Well, I tried fooling around with the OCCURS=VARIABLE and using a variable format but that didn't work.

Using MacGyver or Dialogue Manager, you could substring the pieces doing a GETTOK for the space and write each entry on a separate record, hold, then report.

Or isn't there a filetype where you can specify the delimiter, a space in your case.

Is there a limit to the number of strings that can occur? If so, just DEFINE that many fields and use GETTOK to extract the strings. Although I'm not sure what is returned if you try to extract token 10 and there are not that many. If it returns blanks you're OK and you could set a field to MISSING if so. You could even set this up in the MFD. Otherwise you could read the dataset to find out many occurences are in the longest record (count number of delimiters(space) + 1 and then use DM to lop and define that many fields to re-read the file.

That's a tricky one. Nothing like trying to read your data when you don't know what your data contains, huh?

try suffix=dfix,delimiter=' '
it works for csv or pipe files with variable numbers of fields.

let me look up an example, and i'll get back to you.
..found one

FILE=KWCRECSW, SUFFIX=DFIX  
SEGNAME=BASE, SEGTYPE=S0, $
FIELD=KWIUPC  , ALIAS=E01, A10, A10,   $
FIELD=DOL    ,  , D11.2, A10,   $
FIELD=STORE ,  , A6,  A6,   $
FIELD=ADATE   ,  , A10, A10,   $
FIELD=IREPCODE     ,  , I6, A10,   $
FIELD=CUSTNUM ,  , A10  , A10 ,   $
FIELD=CREDNUM ,  , A8  , A8 ,   $
$$$$ userdefined feild  should be 8 but is 20 sometimes
FIELD=IREPCODE2      ,  , I8, A20,   $
 
FIELD=TOD   ,  , A6,  A6,   $
FIELD=transnum,,I10,A10,$
FIELD=what1,,A10,A10,$
FIELD=what2,,A10,A10,$
FIELD=SALESTAX, , D11.2, A10,$
 
FIELDNAME=DELIMITER ,ALIAS='|' ,USAGE=A1  ,ACTUAL=A1  ,$

  DEFINE ....

i use this master to read a file of pipe delimited fields, the Actual can be some gigantic number, big enough to accomodate the biggest possible goofy thing in the inbound data; you don't have to know the max record length. you don't have to have all the fields populated on the inbound. but it will read the fields in order...so if you have missing fields at the end of the record..that's ok...but if you have missing data in the middle... its not going to know...if it were pipe delimited and you had 2 consecutive pipes, that would work, but for space delim with 2 consecutive spaces??
hmmm. that might be a toughy ....

This message has been edited. Last edited by: susannah,

The problem with DFIX is that multiple delimiters are treated as missing data.
So unless keyfield and keyname are always the same length it will throw in extra columns
and real data could be lost.

In the ideal world support for OCCURS=VARIABLE with DFIX applied at segment level for a
multi-segment description is what is needed.

Darins suggestion of redefining the data based on a maximum number of occurences is just
about the only way to go as the private exits that exist in FOCUS are not available in WebFOCUS.

If the token exceeds the maximum available then there is no problem and it returns nothing.

FILENAME = DIABLODATA, SUFFIX = FIX,$
SEGNAME = A, SEGTYPE=S1,$
FIELDNAME = KEY ,ALIAS = ,USAGE = A9 ,ACTUAL = A9 ,$
FIELDNAME = NAME ,ALIAS = ,USAGE = A8 ,ACTUAL = A8 ,$
FIELDNAME = COL ,ALIAS = ,USAGE = A100 ,ACTUAL = A100 ,$
DEFINE COL1/A10=GETTOK(COL, 100, 1, ' ', 10, 'A10');,$
DEFINE COL2/A10=GETTOK(COL, 100, 2, ' ', 10, 'A10');,$
DEFINE COL3/A10=GETTOK(COL, 100, 3, ' ', 10, 'A10');,$
DEFINE COL4/A10=GETTOK(COL, 100, 4, ' ', 10, 'A10');,$
DEFINE COL5/A10=GETTOK(COL, 100, 5, ' ', 10, 'A10');,$
DEFINE COL6/A10=GETTOK(COL, 100, 6, ' ', 10, 'A10');,$
DEFINE COL7/A10=GETTOK(COL, 100, 7, ' ', 10, 'A10');,$
DEFINE COL8/A10=GETTOK(COL, 100, 8, ' ', 10, 'A10');,$
DEFINE COL9/A10=GETTOK(COL, 100, 9, ' ', 10, 'A10');,$
DEFINE COL10/A10=GETTOK(COL, 100, 10, ' ', 10, 'A10');,$
DEFINE COL11/A10=GETTOK(COL, 100, 11, ' ', 10, 'A10');,$
DEFINE COL12/A10=GETTOK(COL, 100, 12, ' ', 10, 'A10');,$

This message has been edited. Last edited by: <JG>,

Ginny, Darin, Susannah, JG

Lot of suggestions to work out.
The last one might bring me a step in the good direction, but the disadvantage is that each field gets a different name, so I need to make an extra step.
I can do a table on this database, put it in a hold file and create a new mfd with the occurs is variable since the hold file has all the fields in fixed length.

Thanks for the help.

I think Macgyver and GETTOK are the best/simplest way to get this to work.

Ah
so multiple spaces, if they were to exist, would cause problems.
so i'ld read the file under a 1-field master
SQUEEZ out the extra blanks
CTRAN the blanks to a pipe (or STRREP)
and then read the result with DFIX master..
with the delimiter being a |.
As Frank says, this is such a cool language. so many ways.

There are probably more efficient ways, but here is a way to manipulate the file with a single TABLE command.

It reads the line one char at a time and build the rows.

EX -LINES 7 EDAPUT MASTER,tst_data,CV,FILE
FILENAME=tst_data, SUFFIX=FIX,$
SEGNAME=ROOT,
  FIELD=FCHAR,ALIAS=  ,A1 ,A1,$
SEGNAME=OCCRS, OCCURS=VARIABLE
  FIELD=CHAR,ALIAS=  ,A1 ,A1,$
  FIELD=COUNTER, ORDER, I9, I4,$

FILEDEF TST_DATA DISK tst_data.ftm

-RUN

-* keyfield keyname manyfieldsstring
-WRITE TST_DATA 123      operdoc abdss sjsjsj sjskaa sjswkwk sjsjsjsj jddkskk kwkqa djsaa kskka
-WRITE TST_DATA 125      subdoc1 sajaaa ejwjw wwwwwjwww wjwjqkak dkdodes sjs sj ajaaaa sjskdld ddkdkssa skskaak
-WRITE TST_DATA 112      subdoc2 sjsjaa sh ahaa ah sj sjs dkdodes

-RUN

DEFINE FILE TST_DATA
 WORDTXT/A255 = IF COUNTER EQ 1 
                THEN FCHAR | CHAR 
                ELSE
                IF CHAR EQ ' ' 
                THEN LAST WORD
                ELSE
                IF LAST CHAR EQ ' ' 
                THEN CHAR 
                ELSE SUBSTR(255,LAST WORD,1,254,254,'A254') || CHAR ;
 WORDNO/I9    = IF COUNTER EQ 1 
                THEN 1
                ELSE
                IF CHAR EQ ' '
                THEN LAST WORDNO
                ELSE
                IF LAST CHAR EQ ' ' AND CHAR NE ' '
                THEN LAST WORDNO + 1
                ELSE LAST WORDNO  ;
 LINENO/I9    = IF COUNTER EQ 1 THEN  LAST LINENO + 1 ELSE LAST LINENO ;
 FIELD1/A255  = IF WORDNO EQ 1 THEN WORDTXT ELSE LAST FIELD1 ;
 FIELD2/A255  = IF WORDNO EQ 2 THEN WORDTXT ELSE LAST FIELD2 ;
END
 
TABLE FILE TST_DATA
 SUM   
       MAX.FIELD1
       MAX.FIELD2
       LST.WORDTXT
 BY    LINENO
 BY    WORDNO
 WHERE TOTAL WORDNO GT 2
END

Frank All you need to do is create a hold file and then re-write the master to contain an OCCURS segment

TABLE FILE DIABLODATA
PRINT
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 COL11 COL12
BY KEY
BY NAME
ON TABLE HOLD AS BETTERDATA FORMAT ALPHA
END
-RUN
-WRITE BETTERDATA FILENAME = BETTERDATA, SUFFIX = FIX,$
-WRITE BETTERDATA SEGNAME = A, SEGTYPE=S1,$
-WRITE BETTERDATA FIELDNAME = KEY ,ALIAS = ,USAGE = A9 ,ACTUAL = A9 ,$
-WRITE BETTERDATA FIELDNAME = NAME ,ALIAS = ,USAGE = A8 ,ACTUAL = A8 ,$
-WRITE BETTERDATA SEGNAME = B, SEGTYPE=S0,OCCURS=12,$
-WRITE BETTERDATA FIELDNAME = COL ,ALIAS = ,USAGE = A10 ,ACTUAL = A10 ,$
TABLE FILE BETTERDATA
PRINT COL
BY KEY
BY NAME
END

Waz

Your solution works perfect!
I now can decode a book. and a lot of other things....

Frank, there is a limit on the record length of the file.