Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Report against a directory of CSV files

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Report against a directory of CSV files
 Login/Join
 
Platinum Member
posted
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,


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Waz:
Are the format of the files consistent ?



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


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
I wonder what release that came in ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
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.


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Gold member
posted Hide Post
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


81.05 All formats
 
Posts: 56 | Location: Manchester | Registered: November 21, 2006Report This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
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?


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Platinum Member
posted Hide Post
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.


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Virtuoso
posted Hide Post
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.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Platinum Member
posted Hide Post
So something like this in the filedef:


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


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Platinum Member
posted Hide Post
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.


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Virtuoso
posted Hide Post
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.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by dhagen:
Read the doc and build a sample synonym from the server console.


There is a doc that explains this?


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Master
posted Hide Post
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


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Report This Post
Platinum Member
posted Hide Post
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.


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Virtuoso
posted Hide Post
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.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Platinum Member
posted Hide Post
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?


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Platinum Member
posted Hide Post
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,


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Report against a directory of CSV files

Copyright © 1996-2020 Information Builders