Can Webfocus do this?

We have 40 departments that export a csv file from their Kronos time keeper systems and upload them to a folder on our NAS for import in to Peoplesoft so people can get paid.

The files look like:
elapsed_time-cmp_2012_06_09
elasped_time-fdp_2012_06_09
elasped_time-cor_2012_06_09

The 3 different alpha charactors is the department the csv came from.

We want to run a WF report against all of these files to check the integrety of the data before they are imported in to people soft.

Can I make an ODBC conenction to a //network/storagelocation/ that will use all the files (*.csv) present?

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

Are the format of the files consistent ?

You could have WF read the directory, then read each csv file, and you could check for errors.

quote:

Originally posted by Waz:
Are the format of the files consistent ?

Yes, the files are generated from the same stored procedure.

If you can APP MAP to the network location, then cycle through each csv.

You will need to create a MASTER FILE for the structure.

Once you have defined the location of the files to WebFOCUS, I believe I remember seeing a technique in the "Techniques You Can Use on Monday" Summit presentation for concatenating groups of flat files with the same naming convention through use of the wild card character (*):

FILEDEF KTK_DATA DISK <app folder name>/elapsed_time-*_2012_06_09.csv
or
FILEDEF KTK_DATA DISK <app folder name>/elapsed_time-*.csv
or
FILEDEF KTK_DATA DISK <app folder name>/*.csv

As Waz states, you will need to build a master file description (named KTK_DATA.mas in my example) to read and report from the concatenated data files.

I wonder what release that came in ?

quote:

I believe I remember seeing a technique in the "Techniques You Can Use on Monday" Summit presentation for concatenating groups of flat files with the same naming convention through use of the wild card character (*):

FILEDEF KTK_DATA DISK <app folder name>/elapsed_time-*_2012_06_09.csv
or
FILEDEF KTK_DATA DISK <app folder name>/elapsed_time-*.csv
or
FILEDEF KTK_DATA DISK <app folder name>/*.csv

As Waz states, you will need to build a master file description (named KTK_DATA.mas in my example) to read and report from the concatenated data files.

When I first got the request for this functionality from the finance department I said it could take wild cards. As I also remember it from summit.

I think the hurdles will be to map the location as it is on a NAS, on a different domain then my WF server.

I just have get it to the point where the data is available to my MRE users. I am just the admin, not so much a report writer.

I reference a drive which is mapped to the WF server and contains various named files with the same format.
My functionality allocates the filenames as a dropdown option within the procedure. However, the following shows how we access and create a hold containing all the file names. You may be able to use something like this within a dynamic use statement?

  DOS dir DRIVE:\folder\folder\*  /b /s /o:-d /on > TEMP.FTM

FILEDEF TEMP DISK TEMP.FTM
FILEDEF MASTER DISK TEMP.MAS
-RUN

-WRITE MASTER FILENAME=TEMP, SUFFIX=FIX , $
-WRITE MASTER SEGMENT=SEG01, SEGTYPE=S1, $
-WRITE MASTER FIELDNAME=FILEN, ALIAS=E01, USAGE=A255, $

SET PAGE-NUM = OFF

DEFINE FILE TEMP
MASTER_NAME/A50=GETTOK(FILEN,255,-1,'\',50,'A50');
FIRST16/A16=EDIT(MASTER_NAME,'9999999999999999');
DDLIST/A255='<select name="mydropdown"><option value="'|MASTER_NAME|'">'|MASTER_NAME|'</option></select>';
OPTFILE/A255 = '<option>'|MASTER_NAME;

MASTER_3/A50=EDIT(MASTER_NAME,'$$999999');
NOPTFILE/A255 = '<option>'|MASTER_3;

END

TABLE FILE TEMP
PRINT NOPTFILE
WHERE MASTER_NAME CONTAINS 'csv'
ON TABLE HOLD FORMAT ALPHA AS DYNAMLST
END

The usage demonstrated by PBax is a good one, however I would recommend using the SYSTEM function to run the system command string as it will capture the return code in a variable allowing you to identify whether your request was a success or not.

I will also wait until the system command has completed before allowing WebFOCUS to continue with processing (it's also dialogue manager with eveything that im[plies). The advantage of this comes when you have a large directory full of files such as c:\windows\system32.

-SET &Command = 'dir c:\windows\system32\*  /b /s /o:-d /on > TEMP.FTM'

-SET &MyRetcode = SYSTEM(&Command.LENGTH, '&Command.EVAL', 'D4');

-? &MyRetcode

T

Update:

I have the ODBC connection done using the microsoft text driver. When I click test I can see the 3 csv files in the folder.

I have added an adapter under ODBC in the WF server console and built a synonym. When I run the test on the synonym it shows each of the 3 files in the directory as a table.

These files have no field names in the header. They are formated for the peoplesoft import so I can not make any changes to the actual file.

I can see it as a datasource in the MRE report assistant, but it looks like it is only reading one of the 3 files.

I think I am missing a step.

I need a MFD to read all the files in the directory as a single datasource?

I can see the datasource in the MRE datasource list. But trying to run a report using some of the field generates errors:

(FOC1394) CONNECT FAILURE
(1400) SQLCODE 160
: [IM003] Specified driver could not be loaded due to system error 1114
(Microsoft Text Driver (*.txt; *.csv)).
L (FOC1406) SQL OPEN CURSOR ERROR.

Kinda strange since the test worked each step of the way.

WF allows you to use wildcard characters to read multiple flat files with 1 table file. It creates parameters in the .mas and .acx that have a default, or can be changed at runtime to identify a different file pattern. No need to concat etc.

So something like this in the filedef:


APP FI ELAPSED_TIME DISK //network/location/offlatfiles/elasped_time*.csv

I surprised myself this morning by getting this working for the most part. The above error was a security issue and the MRE account did not have the correct right to access the data.

I have it all working and can build reports using the data.

But it is only ready the one file I checked when I built to the synonym. But I am a lot closer than I thought I would be.

quote:

Originally posted by Greg:
So something like this in the filedef:


APP FI ELAPSED_TIME DISK //network/location/offlatfiles/elasped_time*.csv

No. I did not say anything about FILEDEF. Read the doc and build a sample synonym from the server console. Read the screens carefully and it will guide you through the process.

quote:

Originally posted by dhagen:
Read the doc and build a sample synonym from the server console.

There is a doc that explains this?

quote:

Originally posted by Greg:
...But it is only read[ing] the one file I checked when I built to the synonym. But I am a lot closer than I thought I would be.

I think I am doing something similar in my process. I have 6 different csv's in 6 different subdir structures. They are all exactly the same layouts/field sizes, etc, just segmented by hospital entity (in my case).

vhs_beds.mas

FILENAME=VHS_BEDS, SUFFIX=COM     , $
  SEGMENT=VHS_BEDS, SEGTYPE=S0, $
    FIELDNAME=SERV_AREA_ID,      ALIAS=E1, USAGE=A5,    ACTUAL=A5, $
    FIELDNAME=BED_LABEL,         ALIAS=E2, USAGE=A256,  ACTUAL=A256, $
    FIELDNAME=DEPT_ABBREVIATION, ALIAS=E3, USAGE=A20,   ACTUAL=A20, $
    FIELDNAME=BED_NOTES,         ALIAS=E4, USAGE=A1000, ACTUAL=A1000, $
    FIELDNAME=BED_AVAILABILITY,  ALIAS=E5, USAGE=A1,   ACTUAL=A1, $

test.fex:

SET ASNAMES = ON
SET PCOMMA = ON

-SET &FILEPATH = '\\Server\';



-******************************************************************************
-SET &BED_FILE = 'HOSP1\BEDS.csv';
-SET &FULLFILENAME = &FILEPATH | &BED_FILE;

FILEDEF VHS_BEDS DISK "&FULLFILENAME"

TABLE FILE VHS_BEDS
PRINT
	HOSPNAME
	SERV_AREA_ID
	BED_LABEL
	DEPT_ABBREVIATION
	BED_NOTES
	BED_AVAILABILITY

	BY DEPT_ABBREVIATION NOPRINT
	BY BED_LABEL NOPRINT

ON TABLE HOLD AS FILE1
END


-******************************************************************************
-SET &BED_FILE = 'HOSP2\BEDS.csv';
-SET &FULLFILENAME = &FILEPATH | &BED_FILE;

FILEDEF VHS_BEDS DISK "&FULLFILENAME"

TABLE FILE VHS_BEDS
PRINT
	HOSPNAME
	SERV_AREA_ID
	BED_LABEL
	DEPT_ABBREVIATION
	BED_NOTES
	BED_AVAILABILITY

	BY DEPT_ABBREVIATION NOPRINT
	BY BED_LABEL NOPRINT

ON TABLE HOLD AS FILE2
END

This what you're aiming for? Using the same synonym to read multiple files dynamically?
-ABT

All my files are in the same directory, and the names all start with the same 'elasped_time-' and end with .csv. The format of the files are identical.

All 25 fileds are seen as F1 thru F25 and all are A255. I need F5 to be YYMD and F11 to be d12.1S. But when I try to define them that way and add them to the report the report fails. Says fields unrecognized.

So, if I have a series of file that are csv with a deliminator of ; and no headings, here is the master when I build the meta data via the console and answer the correct questions:

FILENAME=CAR_OUTPUT_TEST, SUFFIX=DFIX    , $
 VARIABLE NAME=&&FL_DIRECTORY,  PROMPT='Listening Directory', DEFAULT='baseapp', $
 VARIABLE NAME=&&FL_NAME,  PROMPT='File name pattern', DEFAULT='car_output_*', $
 VARIABLE NAME=&&FL_EXTENSION,  PROMPT='Data File Extension', DEFAULT='ftm', $
 VARIABLE NAME=&&FL_CONNECTION,  PROMPT='Connection to local or remote data files location', $
  SEGMENT=CAR_OUTPUT_TEST, SEGTYPE=S0, $
    FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10,  $     
    FIELDNAME=CAR, ALIAS=CAR, USAGE=A16, ACTUAL=A16, $                          
    FIELDNAME=MODEL, ALIAS=MODEL, USAGE=A24, ACTUAL=A24, $                      
    FIELDNAME=BODYTYPE, ALIAS=BODYTYPE, USAGE=A12, ACTUAL=A12, $                
    FIELDNAME=SEATS, ALIAS=SEATS, USAGE=I3, ACTUAL=A3, $                        
    FIELDNAME=DEALER_COST, ALIAS=DEALER_COST, USAGE=D7, ACTUAL=A7, $            
    FIELDNAME=RETAIL_COST, ALIAS=RETAIL_COST, USAGE=D7, ACTUAL=A7, $            
    FIELDNAME=SALES, ALIAS=SALES, USAGE=I6, ACTUAL=A6, $                        
    FIELDNAME=LENGTH, ALIAS=LENGTH, USAGE=D5, ACTUAL=A5, $                      
    FIELDNAME=WIDTH, ALIAS=WIDTH, USAGE=D5, ACTUAL=A5, $                        
    FIELDNAME=HEIGHT, ALIAS=HEIGHT, USAGE=D5, ACTUAL=A5, $                      
    FIELDNAME=WEIGHT, ALIAS=WEIGHT, USAGE=D6, ACTUAL=A6, $                      
    FIELDNAME=WHEELBASE, ALIAS=WHEELBASE, USAGE=D6.1, ACTUAL=A6, $              
    FIELDNAME=FUEL_CAP, ALIAS=FUEL_CAP, USAGE=D6.1, ACTUAL=A6, $                
    FIELDNAME=BHP, ALIAS=BHP, USAGE=D6, ACTUAL=A6, $                            
    FIELDNAME=RPM, ALIAS=RPM, USAGE=I5, ACTUAL=A5, $                            
    FIELDNAME=MPG, ALIAS=MPG, USAGE=D6, ACTUAL=A6, $                            
    FIELDNAME=ACCEL, ALIAS=ACCEL, USAGE=D6, ACTUAL=A6, $                        
    FIELDNAME=WARRANTY, ALIAS=WARRANTY, USAGE=A40, ACTUAL=A40, $                
    FIELDNAME=STANDARD, ALIAS=STANDARD, USAGE=A40, ACTUAL=A40, $                
  

And here is the ACX

SEGNAME=CAR_OUTPUT_TEST, DELIMITER=;, ENCLOSURE=", HEADER=NO, 
  CONNECTION=&&FL_CONNECTION, DIRECTORY=&&FL_DIRECTORY, NAME=&&FL_NAME, 
  EXTENSION=&&FL_EXTENSION, DATA_ORIGIN=FILE, $  

Now, if I do nothing and use this as the source in a report - or whatever - the process will automatically concatenate all file that match the pattern:

  baseapp/car_output_*.ftm

If I change the values of any of the GLOBAL variables, then I can completely change the source file settings.

My suffix in my master file is currently set to
ODBCSQL. I think it needs to be com to use PCOMMA.

Do I just edit the master file and change it to com? OR is there more to it then that?

Here is what I have so far.

It is only reading 1 file (I need it to read everything elasped_time*.csv in the directory.

It is only reading the first record. I am not having any luck using SET PCOMMA=ON since my master file suffix is not COM.

Master file:

FILENAME=ELAPSED_TIME_CSV, SUFFIX=SQLODBC , $
  SEGMENT=ELAPSED_TIME_CSV, SEGTYPE=S0, $
    FIELDNAME=F1, ALIAS=F1, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F2, ALIAS=F2, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F3, ALIAS=F3, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F4, ALIAS=F4, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F5, ALIAS=F5, USAGE=YYMD, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F6, ALIAS=F6, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F7, ALIAS=F7, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F8, ALIAS=F8, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F9, ALIAS=F9, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F10, ALIAS=F10, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F11, ALIAS=F11, USAGE=D12.1, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=F12, ALIAS=F12, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $

acx file:

SEGNAME=ELAPSED_TIME_CSV, TABLENAME="elapsed_time-cmp.csv", 
   CONNECTION=Elasped_Time, KEYS=0, $

fex:

TABLE FILE ELAPSED_TIME_CSV
PRINT
'ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F3' AS 'EMP ID'
'ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F5' AS 'Date'
'ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F11/D12.1S' AS 'Hours'
HEADING
"Kronos Export Integrety Check"
FOOTING
"&DATEMDYY <+0> "
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL' 'ELAPSED_TIME_CSV.ELAPSED_TIME_CSV.F11'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
(I removed the stuyle stuff for this post)
ENDSTYLE
END
[/code]

I tired to add the other and conenction infomation to the ACX like in dhagen's post above but it failed.

If I change the suffix to COM , $ (so I can use PCOMMA I got no records returned.

I feel I am close to getting this working.

UPDATE: I have it reading more than the first record.

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

Rules for reading CSV with suffix COM or COMT

You must FILEDEF the files.
You should have PCOMMA set to ON.
Your master can be changed to reflect the actual size, and usage instead of A255.