This is a really difficult situation I'm trying to figure out, maybe someone can point me in a better direction but so far:

I'm trying to create an export file that has three lines of text as a header, and then each field is comma delimited by double quotes and commas. The real zinger on this connundrum is the fact that they don't want any trailing spaces at the end.

I am currently appending the first report (which does the three lines of heading), to the second batch which does the raw data, and reading it back to the user using an MFD with a set size. I'm not sure if it's doable but I believe the MFD is creating these excess spaces after the last field on each row.

Has anyone run into this and if so is there a better or any solution for this?

Thanks!

is the header row longer than the max lrecl on the data?
does the 'set size' mfd have a single data field defined with suffix=fix?
do you have a dummy mfd like this
FILE=VARREC, SUFFIX=FIX
SEGNAME=BASE, SEGTYPE=S0, $
FIELD=MYRECORD, ALIAS=E01, USAGE=A80, ACTUAL=A80, $
and then a second copy of it, varrec2
FILEDEF VARREC DISK ..headerfile
FILEDEF VARREC2 DISK ...datafile
FILEDEF OUTPUT DISK ..wherever
TABLE FILE VARREC
PRINT *
ON TABLE SAVE AS OUTPUT
MORE
FILE VARREC2
END
is this what you're doing?

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

The headers are actually shorter than the data rows and the SUFFIX=FIX is defined only once in the MFD as follows:

FILENAME=FILENAME,SUFFIX=FIX, DATASET='filename.csv'
SEGNAME=ROOT_SEG,SEGTYPE=S1,$
FIELD=F_LINE, ALIAS=F_LINE, FORMAT=A1200,$

I do believe that last row that sets the size to A1200 is causing this but I don't know how to go around giving it that large a playroom for variables that may take up less than their total field size since they are all alphanumeric.

The procedure you mentioned is very close to what I'm doing but I only have one dummy MFD created so that I can resend the final append file out, it would read:

TABLE FILE FILE_NAME
PRINT
F_LINE AS ''
ON TABLE PCHOLD FORMAT ALPHA
END

What is format of your output file? If it is a text file, you can use ON TABLE SAVE, then use
-REPEAT with -READ NOCLOSE and -WRITE within the loop. Then you can remove the trailing spaces before using -WRITE statement.

Lusheng,

The format is ALPHA, how would that syntax look like for a REPEAT? Does it matter how long the MFD field definition is?

yep, append would have been a route to try.
-read and -write i don't think will work
when i've tried to do that without knowing EXACTLY how long each record is, the -read which has a fixed spec will read on into the next record. yuck.
syntax would be something along these lines...
FILEDEF HEADER DISK ...wherever
FILEDEF INPUT DISK ...wherever
FILEDEF OUTPUT DISK ...whereever
-RUN
-REPEAT loop1 3 TIMES
-READ HEADER &MYREC.A1200
-WRITE OUTPUT &MYREC.A1200
-loop1
-REPEAT endloop WHILE &IORETURN NE 0
-READ INPUT &MYREC.A1200
-WRITE OUTPUT &MYREC.A1200
-endloop
but i'm 99.9% sure this wont work.

Is your max lrecl really 1200?
why are you PCHOLDing and not just HOLDing? are you on a remote box somehow?
When you read each file (header and data) individually with your dummy mfd, can you sucessfully read and write each?

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

The field size is fairly large, but I put 1200 because I wasn't sure how long the rows were gonna be after production.

I have tried exporting to Excel and then converting to comma delimited, however they also require the fields to have quotation marks in order to read them in (*shrugs* don't really know why that is), and when I hard code them in, the csv conversion throws in extra quotes, so that really throws the format off.

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

so you're PCHOLDing your final output file...i see...but we've got to build it first, and the 2 steps of glueing header to data can be done all on the unix box before you bring it down, yes?
so lets take it one step at a time:
Tell us if you can read your header file with the dummy master ok
and if you can read your data file with the dummy master ok.
(Have you ever used the SQUEEZ function to clean up extra spaces in character strings? perfect for this situation...reread that csv data file with a dummy master with 1 field per record, and use the SQUEEZ to smush up the empty spaces)

I think the first mistake is the post-processing step. If you create your file with the heading lines and then the data lines, make sure the format of those two sections of the file is what you require, then there is no need for a MFD to read and write again.

This example creates variable length records using the TAB output format which does not have trailing blanks. Since only one column (a computed one that concatenates multiple columns) is in the request, there are no unwanted tab characters in the output file either. No post-processing necessary.

SET HOLDLIST=PRINTONLY
FILEDEF H001 DISK C:\TEMP\H001_&YYMD|_&TOD...TXT (APPEND
-RUN

TABLE FILE CAR
SUM
COMPUTE HEADING1/A100 = 'THIS IS HEADING LINE 1 CREATED ON &DATEYYMD AT &TOD';
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS H001 FORMAT TAB
END
-RUN

TABLE FILE CAR
SUM
COMPUTE COSTX/A7 = FTOA(DEALER_COST, '(D7Lc)', 'A7'); NOPRINT
COMPUTE SALESX/A6 = EDIT(SALES); NOPRINT

COMPUTE ALPHA_LINE/A200 = '"' || COUNTRY || '","' || COSTX || '","' || SALESX || '"';

BY COUNTRY NOPRINT
ON TABLE HOLD AS H001 FORMAT TAB
END

Susannah,

No haven't tried a SQUEEZE, I really wouldnt know how that would look with the code. Is that a SET function that needs to be done?

Francis,

How would I get the H001 file back to the user? This is basically the problem since they don't have access to the server to get the final output file.

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

Before my suggestion, how were you intending to deliver the file to the user? What was that MFD being used for? Were you displaying the final result in the browser?

The final file was used as a source file for a program they are using to import this data. Basically, as is, the user runs it and then when it's done it creates a file that then asks the user where it should be saved at.

No fuss, no muss on our part at that point since it's in the right format and all they need to do is name it whatever it is with whatever extension.

The MFD is just used so that, like in your example, I can read in the TAB file from the Webfocus directory out to the screen or to pchold format. I'm not sure if there is another way to not have to look for the file in the server directory than to do it this way.

i was assuming your 3 header lines did NOT match the record layout... something like a Report Header that only had 1 long field.
Not even close, right?

I don't understand. It sounds like the method the user receives the file hasn't been figured out yet.

You're replacing some existing process with a new WebFOCUS one? How did the file get to the user before?

If you change the HOLD to PCHOLD, you may have what you require - on my Windows XP workstation, at the end of the program, I get a Windows pop-up box asking me what I want to do with the file:



click Save and this opens:



I'm not sure you will get the same behaviour, but it might work. Unfortunately, the Save dialog box does not have the correct name and extension but this can be changed by the user.

The code:

-*-- Create a file with variable length records --------------------------------
-*   No trailing blanks

SET HOLDLIST=PRINTONLY
FILEDEF H001 DISK H001.TXT (APPEND
-RUN

TABLE FILE CAR
SUM
COMPUTE HEADING1/A100 = 'THIS IS HEADING LINE 1 CREATED ON &DATEYYMD AT &TOD';
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE PCHOLD AS H001 FORMAT TAB
END
-RUN

TABLE FILE CAR
SUM
COMPUTE DUMMY/A25 = 'THIS IS HEADING LINE 2'; NOPRINT
COMPUTE HEADING1/A400 = 
  DUMMY | DUMMY | DUMMY | DUMMY | 
  DUMMY | DUMMY | DUMMY | DUMMY | 
  DUMMY | DUMMY | DUMMY | DUMMY | 
  DUMMY | DUMMY | DUMMY | DUMMY;
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE PCHOLD AS H001 FORMAT TAB
END
-RUN

TABLE FILE CAR
SUM
COMPUTE COSTX/A7 = FTOA(DEALER_COST, '(D7Lc)', 'A7'); NOPRINT
COMPUTE SALESX/A6 = EDIT(SALES); NOPRINT

COMPUTE ALPHA_LINE/A200 = '"' || COUNTRY || '","' || COSTX || '","' || SALESX || '"';

BY COUNTRY NOPRINT
ON TABLE PCHOLD AS H001 FORMAT TAB
END

This message has been edited. Last edited by: Francis Mariani,

Actually, the three headerlines aren't the same size, one is like 3 different words, the second is like 2 words, and the third is a whopping 400 character line with the field column headers but with different names.

Francis, this might actually work, but let me see if it can get the other lines as well.

I've updated the code in my previous post to have a 400 character heading line.

I'm running the code at this moment to see if it will run, there are actually three lines, so i'd have to do the first part three times before even getting to the data part...

Francis,

You are awesome! This totally works, I had some errors and so the program jumped to the next run (the program does two versions depending on the user input) but I believe it's just a debugging issue on some definitions, but thank you for the solution, this really helps with not using MFD's on a variable sized text output file

Change the PCHOLD to HOLD untul you've finalized the program, then change it back. You'll get to see the errors.

Totally worked! It even got the file size about 25% smaller than previously.

Thanks to Francis and Susannah for your help on this, it will definitely speed up our processes here.

Thanks!

I'm glad it worked out.