Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to connect flat file?
Go
New
Search
Notify
Tools
Reply
  
How to connect flat file?
 Login/Join
 
Gold member
posted
I've looked around and not found a complete, start to finish, guide on how to connect to a flat file as a data source in webfocus 5.25.

Here is our situation. We have an excel file with 5 columns on a windows machine and a unix server which is running both webfocus and our webserver. I need to know:

1) What format from excel will work best (.csv from excel does not put in quotes).
2) Where on the server does the flat file need to reside.
3) How do I verify the master file is correct.

I figured out how to make the master file and can write a .fex that can read the master file. The problem is that it returns 0 rows. I suspect that it is not even opening the file to look at it.

As always, any help would be appreciated.
 
Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
George, you can export from excel to csv, with quotes, the export type you should select is CSV(MS-DOS), its not the first csv choice in the list, look down ****her. The first choice CSV (comma delimited) won't work, you're right. Then all you need is the master. If you have column headers in your export, use SUFFIX=COMT
To read excel directly, you need to have specified a range in the actual worksheet, giving it a range name. There are some articles on the techsupport site on reading excel directly.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
Originally posted by susannah:
[qb] George, you can export from excel to csv, with quotes, the export type you should select is CSV(MS-DOS), its not the first csv choice in the list, look down ****her.
[/qb]
Are you certain? I have excel 2002 and when I output to csv(msdos) and open it in a text editor it still does not have quotation marks.


quote:

[qb]Then all you need is the master.[/qb]
I'm 85% sure my master file is okay.
FILE=phone_dist, SUFFIX=COM
SEGNAME=ROOT_SEG, SEGTYPE=S1, $
FIELD=ACCT_UNIT, ALIAS=ACCT_UNIT, USAGE=I5, ACTUAL=I4, MISSING=ON, TITLE='Account ,Number', DESCRIPTION="Account Number charged for this department's phone charges", $
FIELD=NAME, ALIAS=NAME, USAGE=A20, ACTUAL=A35, MISSING=ON, TITLE='Name', DESCRIPTION='Name of the Account Manager assigned to their account number', $
FIELD=DEPT, ALIAS=DEPT, USAGE=A20, ACTUAL=A50, MISSING=ON, TITLE='Department', DESCRIPTION='Department associated with the account number and account manager', $
FIELD=ROOM, ALIAS=ROOM, USAGE=A20, ACTUAL=A50, TITLE='Room', $
FIELD=CAMPUS, ALIAS=CAMPUS, USAGE=A20, ACTUAL=A10, TITLE='Campus', $
FIELD=EMAIL, ALIAS=EMAIL, USAGE=A20, ACTUAL=A50, MISSING=ON, TITLE='Email,Address', DESCRIPTION='Email Address of the account manager for this account', $


quote:

[qb]To read excel directly, you need to have specified a range in the actual worksheet, giving it a range name. There are some articles on the techsupport site on reading excel directly. [/qb]
Is this possible on a unix server? I've heard that it is not an option in our environment.
 
Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
well, works for me; but any two environments are gonna be different. Anyway, it shouldn't make a difference.
What about your filedef statement? are you sure its pointing to the .csv file?
Your 0 lines error can happen if your master is fine, but your filedef is pointing to pluto.
issue a check on the mfd
CHECK FILE MYFILE
FILEDEF MYFILE DISK D:\IBI\...\myfile.csv
TABLE FILE MYFILE
PRINT *
END
--caveat: i'm win2k server, don't know beans about unix filedefs
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
The file def was the issue. I didn't have it pointed in the right area. Thank all of you for your help.
 
Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to connect flat file?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.