I have an external file saved on a shared drive with 3 columns. I have tried to save this file as a csv file and an excel file in order to pull the data contained in the file into a Webfocus Report as an external table.

I am able to pull in the data and run a report, but when i do its taking my 3 columns and placing all the data into 1 column.

My Master File:
FILENAME=fits_project, SUFFIX=FIX, $
SEGMENT=fits_project, SEGTYPE=S1, $
FIELDNAME=CASE_NUMBER, ALIAS=CASE_NUMBER, USAGE=A10V, ACTUAL=A10,
MISSING=ON, $
FIELDNAME=ITEM_NUMBER, ALIAS=ITEM_NUMBER, USAGE=A3V, ACTUAL=A3,
MISSING=ON, $
FIELDNAME=ITEM_TYPE, ALIAS=ITEM_TYPE, USAGE=A4V, ACTUAL=A4,
MISSING=ON, $

My Fex Code:
ENGINE SQLORA SET ORACHAR VAR
ENGINE SQLORA SET VARCHAR OFF
SET ASNAMES = ON
APP FI fits_project_hold DISK baseapp/fits_project_hold.ftm
APP HOLDMETA baseapp
APP MAP DATA \\ftmvfxs1\cfisfm$\Dashboard
APP APPENDPATH DATA
APP HOLD DATA
APP SHOWPATH
-RUN
APP FI fits_project DISK DATA/fits_project.XLSX
-RUN
TABLE FILE FITS_PROJECT_HOLD
PRINT
CASE_NUMBER
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

Orginal file that is saved on my shared drive:
2020973741 1 PRSI
1820943742 2 RESI
1920933745 4 EMPL
1020953748 5 REFE
1520963744 6 REFE

How Report Data is being returned:
CASE_NUMBER
2020973741
,001,PRSI
1820943742
,002,RESI
1920933745
,004,EMPL
1020953748
,005,REFE
1520963744
,006,REFE

This message has been edited. Last edited by: Kathleen Butler,

I am a bit confused with the files and output.

Your original file has no commas, but the returned file does.

The master file you are using is for a fixed format file with a record length of 17.

Is fits_project_hold.ftm the orignal file ?

When posting code and data, please use the code tags.

Sometimes (often?) folks post a lot of non-relevant code with their issue. So it's hard to see the wood for the trees. To cut to the chase, here is what is relevant.

quote:

My Master File:
FILENAME=fits_project, SUFFIX=FIX, $
SEGMENT=fits_project, SEGTYPE=S1, $
FIELDNAME=CASE_NUMBER, ALIAS=CASE_NUMBER, USAGE=A10V, ACTUAL=A10,
MISSING=ON, $
FIELDNAME=ITEM_NUMBER, ALIAS=ITEM_NUMBER, USAGE=A3V, ACTUAL=A3,
MISSING=ON, $
FIELDNAME=ITEM_TYPE, ALIAS=ITEM_TYPE, USAGE=A4V, ACTUAL=A4,
MISSING=ON, $

My Fex Code:
APP FI fits_project DISK DATA/fits_project.XLSX
-RUN
TABLE FILE FITS_PROJECT_HOLD
PRINT
CASE_NUMBER
ON TABLE PCHOLD FORMAT HTML
END

Orginal file that is saved on my shared drive:
2020973741 1 PRSI
1820943742 2 RESI
1920933745 4 EMPL
1020953748 5 REFE
1520963744 6 REFE

How Report Data is being returned:
CASE_NUMBER
2020973741
,001,PRSI
1820943742
,002,RESI
1920933745
,004,EMPL
1020953748
,005,REFE
1520963744
,006,REFE

First of all, I'd suggest sticking to convention and use upper case for your FILEDEF (APP FI) if you are going to use it in the TABLE request.

Secondly, your FILEDEF references an XLSX file, which is not fixed format and your master is for fixed format.

Third, you need to indicate the length of the entire line of your fixed-length data

You need to make your data file a .TXT or .DAT file and your FILEDEF like this:

FILEDEF FITS_PROJECT DISK DATA/fits_project.txt (RECFM F LRECL 16

Fourth, your TABLE request references FITS_PROJECT_HOLD when the FILEDEF is only for FITS_PROJECT (no _HOLD portion)

Yes the orginal file does not have commas, so i am not sure why the new file contains commas.

The original file is pulling off a shared drive and i have tried saving it as a .xlsx format and a .csv file.

All code generated by WebFocus was uploaded to focal point for this example.

The new file has commas because you have FILEDEFed it as an Excel or csv. You data file is FIXED FORMAT. Look up FIXED FORMAT in the Describing Data manual.

You need to use the FILDEF (or APP FI) that I gave you and then fix the other items.

Actually, now that I look it I see that your master file is of type S1. That means the CASE_NUMBER is the key field, so MISSING=ON is incorrect for that field in your master file description.

PS - Waz is right that the Record Length is 17 not 16 as I wrote above.

This message has been edited. Last edited by: George Patton,

George,

You also missed some "wood" as the Excel file is not the one being tabled -

APP FI fits_project_hold DISK baseapp/fits_project_hold.ftm

"Wood" it not be great if folks just placed the relevant code here?

T

This message has been edited. Last edited by: Tony A,

Actually I did notice that Tony, although I may have expressed it a bit clumsily.

quote:

Secondly, your FILEDEF references an XLSX file, which is not fixed format and your master is for fixed format.

Thanks

This message has been edited. Last edited by: Kathleen Butler,

Focal Point: Please mark case as solved: This issue was solved internally. After identifying a piece of code was incorrect in the master file.

Thanks!

This message has been edited. Last edited by: Kathleen Butler,

Hi Lynsi,

You need to mark the topic closed by editting your very first post and making a change to the subject line.

To edit your post you need to click on the icon in the lower right of the section containing your post that resembles a pencil eraser over a folder icon.

T