I have read the many posts and have used them to get this far, but for some reason I am having trouble with how my flat file is being printed out.

My first question is, how does the master file know what delimeter you have?

I looked at a previous post where Susanne was listing step by step the process and I noticed she created a fill field for when there was a space in her flat file... do I have to do that?

My flat file is reading the fields well but when there is no data for a certain "field" then it populates it with the next fields data... help?

Flat File Data is as follows:

2008-01-16 11:00:51,662 U=1111111 E=abc FM=17736856 TM=121764352 PS=lotsoftextisinthisspace
2008-01-16 11:00:52,932 U= E=abcd FM=16319376 TM=121764352 PS=lotsoftextisinthisspace
2008-01-16 11:00:54,618 U=2222222 E=ab FM=16250184 TM=121764352 PS=lotsoftextisinthisspace

Master file is as follows:

FILE=my_logfile, SUFFIX=FIX
SEGNAME=BASE, SEGTYPE=S1, $
FIELD=LOG_DATE, ALIAS=LOG_DATE, FORMAT=A25, $
FIELD=U_FLD, ALIAS=U_FLD, FORMAT=A11, $
FIELD=E_FLD, ALIAS=E_FLD, FORMAT=A5, $
FIELD=FM_FLD, ALIAS=FM_FLD, FORMAT=A20, $
FIELD=TM_FLD, ALIAS=TM_FLD, FORMAT=A20, $
FIELD=PS_FLD, ALIAS=PS_FLD, FORMAT=A500, $

MY fex file:

FILEDEF my_logfile DISK /server/app/ibi/apps/appfolder/my_file.txt
-RUN

TABLE FILE MY_LOGFILE
PRINT *
END


If anyone can give me any help, that would be great... even if it is just telling me that this has been answered and maybe giving me the link... I think I've looked though

Thank you

SUFFIX=FIX assumes that you have a file with no delimeter and they are most often fixed length. Most often, these files contain data whose columns always start in the same position. When you create the master, you must account for every character on a line.

There are other formats like COM, COMT, TAB, TABT which are more specific formats for fixed files. You can look these up on the web site for more detail.

Now, in your case you have one column that seems to be of a fixed size and the rest of the line is keyword driven with the columns of varying sizes. If that is true, your effort will be a bit more complex.

Assuming tht is true, create a master for the way you want your flat file to look after you are done with what I'm going to tell you next. The one in your post should work fine.

Then, and here is where it gets a bit complicated if you don't have too much experience, you will have to use Dialogue Manager to -READ the original file, use subroutines to create variables representing the fields in the result master, then -WRITE them to the file that is filedef'd to the result master.

I will give you more detail in a later post if this is what you want to do and your input data is not in neat columns.

If your data is in neat columns, then just add fillers for the stuff you don't want to print. Just remember, you have to account for all the character in a row.

Clear as mud?

Yes, thank you!

I figured there was an easy way to identify the delimeter but I didn't know and I tried searching everywhere (not good enough I guess)!

I am a pretty experienced developer so feel free to get into details on the next step. Yes each column can have varying sizes and will not always be fixed.

Could I just add a comma delimeter to the file and edit the master file to COM? So I would need to read my_logfile and trim the date/time so that I can get rid of the ",435" ending and then go in and put in commas...

I am up for suggestions though

S

S

if you are able to change the format of the txt file you can make it to a TAB delimited or an COM delimited file. The last on would have problems with your time, unless you change that comma to a point.
With the comma delimited (and tab) you don't have to think about the actual format, but in a comma delimited file you need a special end of line: a ",$"
In the master file you also can set a pointer to the location, if the location is not fixed than your filedef is better.
In the master you also might need to set the ACTUAL format. PE the first field could be ACTUAL=HYYMD.
If some fields are sometimes empty you can add the line "MISSING=ON".

How would I change the format of the text file? Just by placeing TAB or COM in the suffix spot in the master file?

I think the problem is that I get the text file as is. I don't think it's really delimeted or has any sort of format. I guess I could create a 1 field master file and then parse everything out using defines and keywords in the file ... is that a long way of doing it?
S

S,

If you want to avoid changing the file before you process it, this is what I would do. I'm only going to do a couple of fields and you can do the rest.

APP FI INPTTXT DISK appdir/inpttxt.txt
APP FI MYLOGFILE DISK appdir/my_logfile.txt
-READLOOP
-READ INPTTXT NOCLOSE INPTLINE.A80.  <== or whatever the max length of the line is
-IF &IORETURN NE 0 GOTO ENDLOOP;
-SET &LOG_DATE=SUBSTR(80,&INPTLINE,1,23,23,'A23');
-SET &UPOS=POSIT(&INPTLINE,80,'U=',2,'I2'); 
-SET &EPOS=POSIT(&INPTLINE,80,'E=',2,'I2'); 
-SET &ULEN=&EPOS-&UPOS-3;  <== You might have to play with this
-SET &UFLD=SUBSTR(80,&INPTLINE,&UPOS+2,&EPOS-2,&ULEN,'A11');
... <== Extract rest of fields here
-WRITE MYLOGFILE &LOG_DATE.A23. &UFLD.A11. &EFLD.A5.  ...
-GOTO READLOOP
-ENDLOOP
... Rest of Code

What do you think of this?

Okay so I was doing something similar with a define. I redid the master to read the file as having 1 field then:

DEFINE FILE MY_LOGFILE
LOG_DATE/A23 = SUBSTR(3000, LOGFILE, 0, 23, 23, LOG_DATE);
U_FLD/I2 = POSIT(LOGFILE, 3000, 'U=', 2, 'I2');
USER_ID/A7 = SUBSTR(3000, LOGFILE, U_FLD+2, U_FLD+9, 7, USER_ID);
E_FLD/I2 = POSIT(LOGFILE, 3000, 'E=', 2, 'I2');
FM_FLD/I2 = POSIT(LOGFILE, 3000, 'FM=', 3, 'I2');
TM_FLD/I2 = POSIT(LOGFILE, 3000, 'TM=', 3, 'I2');
PS_FLD/I2 = POSIT(LOGFILE, 3000, 'PS=', 3, 'I2');
END

I just was having a hard time when there was no data in the field... but I see in your post.. you minus the positions to get the lenghts for the substring (-SET &ULEN=&EPOS-&UPOS-3; <== You might have to play with this)... makes sense!

I will try both ways... but I had a question about your way:
Is &IORETURN a reserved variable... or where/when would I set it if not?
Is the input text file an empty text file that I need to create or where does that come from... is it auto created?

Other than that... that looks great!

Thank you I will try this.

S

&IORETURN is a system variable that is used to check the results of a -READ to see if you are at end of file.

The input text file is your original file with the U=, etc. The output will be a file to match the master that you put in your original post that won't have all that stuff in it and you will have fixed column widths and it will be easier to report on.

You are correct about the empty field. You would probably want to check the results of the length calculation to make sure that it wasn't zero. If zero, set to spaces else do the substring.

Isn't this fun!

This is the way I ended up doing it:

FILEDEF my_logfile DISK /server/app/ibi/apps/appfolder/my_logfile.txt
-RUN

DEFINE FILE MY_LOGFILE
LOG_DATE/A23 = SUBSTR(3000, LOGFILE, 1, 23, 23, LOG_DATE);
U_POS/I2 = POSIT(LOGFILE, 3000, 'U=', 2, 'I2');
E_POS/I2 = POSIT(LOGFILE, 3000, 'E=', 2, 'I2');
FM_POS/I2 = POSIT(LOGFILE, 3000, 'FM=', 3, 'I2');
TM_POS/I2 = POSIT(LOGFILE, 3000, 'TM=', 3, 'I2');
PS_POS/I2 = POSIT(LOGFILE, 3000, 'PS=', 3, 'I2');
U_LEN/I2 = E_POS - U_POS - 3;
E_LEN/I2 = FM_POS - E_POS - 3;
FM_LEN/I2 = TM_POS - FM_POS - 3;
TM_LEN/I2 = PS_POS - TM_POS - 3;
U_FLD/A11 = SUBSTR(3000,LOGFILE,U_POS+2,E_POS-2,U_LEN,'A11');
E_FLD/A05 = SUBSTR(3000,LOGFILE,E_POS+2,FM_POS-3,E_LEN,'A5');
FM_FLD/A10 = SUBSTR(3000,LOGFILE,FM_POS+3,TM_POS-3,FM_LEN,'A10');
TM_FLD/A10 = SUBSTR(3000,LOGFILE,TM_POS+3,PS_POS-3,TM_LEN,'A10');
PS_FLD/A2000 = SUBSTR(3000,LOGFILE,PS_POS+3,3000,2000,'A2000');
END


TABLE FILE MY_LOGFILE
PRINT
LOG_DATE
U_FLD
E_FLD
FM_FLD
TM_FLD
PS_FLD
END

Thanks to all who helped!

That works too. Glad you got it working.

S

Nice solution!

If you don't mind to have the E= and U+ and such in your datafields, there is an even simpler solution.
You only need to change your master file.
I've taken your data from this post and created the follwing example for you:

FILEDEF DATFIL DISK logfile.txt (RECFM V
FILEDEF MASFIL DISK logfile.mas
-RUN

-WRITE MASFIL FILE=my_logfile, SUFFIX=DFIX
-WRITE MASFIL SEGNAME=BASE, SEGTYPE=S1, $
-WRITE MASFIL FIELD=LOG_DATE, ALIAS=LOG_DATE, FORMAT=A12, ACTUAL=A12, $
-WRITE MASFIL FIELD=LOG_TIME, ALIAS=LOG_TIME, FORMAT=A12, ACTUAL=A12,$
-WRITE MASFIL FIELD=U_FLD, ALIAS=U_FLD, FORMAT=A13, $
-WRITE MASFIL FIELD=E_FLD, ALIAS=E_FLD, FORMAT=A7, $
-WRITE MASFIL FIELD=FM_FLD, ALIAS=FM_FLD, FORMAT=A23, $
-WRITE MASFIL FIELD=TM_FLD, ALIAS=TM_FLD, FORMAT=A23, $
-WRITE MASFIL FIELD=PS_FLD, ALIAS=PS_FLD, FORMAT=A503, $
-WRITE MASFIL FIELD=DELIMITER, ALIAS=' ', FORMAT=A1, ACTUAL=A1, $

-WRITE DATFIL 2008-01-16 11:00:51,662 U=1111111 E=abc FM=17736856 TM=121764352 PS=lotsoftextisinthisspace
-WRITE DATFIL 2008-01-16 11:00:52,932 U= E=abcd FM=16319376 TM=121764352 PS=lotsoftextisinthisspace
-WRITE DATFIL 2008-01-16 11:00:54,618 U=2222222 E=ab FM=16250184 TM=121764352 PS=lotsoftextisinthisspace

FILEDEF LOGFILE DISK logfile.txt (RECFM V LRECL 600
-RUN

TABLE FILE LOGFILE
PRINT *
END

Hope this helps ...

GamP,

Wonderful Solution!
I'd never heard of or used file type DFIX.
I cracked open my 'Describing Data' manual, consulted the Index a few times, and learned a lot!

It's neat, too, how you created a *.mas dynamically with WRITE's. Knew it was possible but never saw it used before. I can just image the possibilities when this is combined with DM and some &vars and loops in the WRITE statements. Such a script would illustrate the remarkable power of the FOCUS scripting language to 'boot strap' itself into a dynamically described process.

Chris Burtt

GamP

I tried your sample and it worked very nice, a great way to approach this problem!

I am able to edit my master and change the file, but for some reason the Print Out is only giving me the first two columns...

MASTER FILE logfile.mas
-----------
FILENAME=logfile,SUFFIX=DFIX
SEGNAME=BASE,SEGTYPE=S1,$
FIELD=LOG_DATE, ALIAS=LOG_DATE, FORMAT=A12, ACTUAL=A12, $
FIELD=LOG_TIME, ALIAS=LOG_TIME, FORMAT=A12, ACTUAL=A12, $
FIELD=U_FLD, ALIAS=U_FLD, FORMAT=A13, $
FIELD=E_FLD, ALIAS=E_FLD, FORMAT=A7, $
FIELD=FM_FLD, ALIAS=FM_FLD, FORMAT=A23, $
FIELD=TM_FLD, ALIAS=TM_FLD, FORMAT=A23, $
FIELD=PS_FLD, ALIAS=PS_FLD, FORMAT=A503, $
FIELD=DELIMITER, ALIAS=' ', FORMAT=A1, ACTUAL=A1, $


FEX FILE
--------
FILEDEF LOGFILE DISK /server/app/ibi/apps/appfolder/logfile.txt (RECFM V LRECL 600
-RUN

TABLE FILE LOGFILE
PRINT *
END
-EXIT


OUTPUT
------
PAGE 1

LOG_DATE LOG_TIME U_FLD E_FLD FM_FLD TM_FLD PS_FLD DELIMITER
2008-01-16 10:53:11,653
2008-01-16 10:53:12,834
2008-01-16 10:54:47,377


Did I miss something (obvious most likely )? It's always good to have multiple options of doing something.

Thanks so much!

S

This nice solution from GamP should work.

Have you run the code he provided as is? That works.

Is your logfile really only 1 space between the time and u data, more spaces could cause this.

The code you have, and copying the data from the forum, seems to work okay, and I cannot see another reason other than the data in the file for it not to run properly.

i agree w/ Alan... my next step would be to read 1 record and take a look at the BITVAL of that blank after the 653.
It might have a funky nonascii character in it
that isn't a 32 (blank) but is something else goofy, like a carriage-return-line-feed, which i've encountered reading whacky flat files.
look up BITVAL and HEXBYT in your Using Functions manual.
If that turns out to be revealing, then use the CTRAN function to clean out all those whacky values and change them to , say, blanks (32).
Do a complete read and write of your file using a master that is just 1 field per record.
Then readback your cleaned output under the nice DFIX master you just made.
That's what i would try , anyway.