I have a file with 3 columns of data in MS Excel. I would like to upload this to the server to use it as I would any other table. I know we have to create a master file to accompany the file, but my confusion comes in as to knowing what type of file this should be uploaded as - .csv, .txt, .ftm? Can anyone shed any light on this matter or point me in the direction as to where I can find information on how to do this?

Thanks,

Will

SLU Will,

I did this quite awhile ago so I do not remember all the details. However, I do remember that I was able to upload the XLS file to our Windows 2K server which has WebFOCUS running on it. Then I THINK I had to define the XLS file as an ODBC data source to Windows. The other thing I had to do was to define a RANGE in the XLS file for all the rows and columns. Sorry for the vagueness of mt information. It has been awhile since I did this but I do know you can use the XLS directly as the data source.

SLUWILL
as mickey says, create a range in your excelfile, the top row of the range needs to be column names,make them simple.
then
start
control panel
admin tools
datasources odbc
system dsn
and create one, using the excel driver, and pointing to your file.

Hi Will.

If you don't want to mess with the ODBC definition, you can simply save your Excel file as a .csv file on the WebFOCUS server. Then create a master file with the fieldnames and definitions as usual, but add the SUFFIX=COM statement to the filename line like this
FILENAME = MYFILE, SUFFIX = COM, $
so WebFOCUS knows it's a comma-delimited file. You can find more info and the documention in the "Describing Data" manuals.

The drawback to comma-delimited files is that you can only report from them. You can't join them to other tables, etc. We get around this by reading the comma-delimited file in a WebFOCUS query and holding the result as a FOCUS file (ON TABLE HOLD FORMAT FOCUS . . .).

If I remember correctly (I tried the ODBC connection once), you have to set up a new ODBC connection for every Excel file that you want to access. We want to access A LOT of Excel files, so the .csv and convert it to a FOCUS file, if needed, works for us.

Thanks for your input everyone. I went back and forth with debbiej and we managed to solve the problem. I had our tech person upload the CSV file to the server and then I created the master file for it. At this point, I had to make sure I was pointing to the correct directory and then hold it as FORMAT FOCUS as debbiej states. After that, I had to change my APP PATH back to where all the other .mas files were housed. Below is an example of one I did:

APP PATH ALUMNI1
-*
FILEDEF FINANCEFUNDS DISK /opt/ibi/apps/alumni1/financefunds.csv
-RUN
-*
TABLE FILE FINANCEFUNDS
PRINT
FINANCE_NO
BOOK_VALUE
MARKET_VALUE
ON TABLE HOLD AS ENDOW1 FORMAT FOCUS
END
-RUN
-*
APP PATH MFDS

Then the rest of my report followed afterwards.

One comment, this line is case sensitive:
FILEDEF FINANCEFUNDS DISK /opt/ibi/apps/alumni1/financefunds.csv

Thanks again and I hope this helps those who are in a similar situation as I was. If you need help, let me know and I'll do the best I can to help.

Thanks,

Will

Will,

Don't forget that you can always prepend and append to the APP PATH to negate the requirement of constantly changing paths -

APP SHOWPATH
APP PREPENDPATH ibinccen
APP SHOWPATH

Will,

You can also keep data files(.csv) and master file description(.mas) files in a seperate directory and then copy them into the current webfocus session temp directory as needed for reporting.

Master file description(forum826.mas)
FILE=FORUM826 ,SUFFIX=COM
SEGNAME=FORUM826,SEGTYPE=S00
FIELDNAME =COUNTRY ,E01 ,A10 ,A10 ,$
FIELDNAME =CAR ,E02 ,A16 ,A16 ,$
FIELDNAME =MODEL ,E03 ,A24 ,A24 ,$

Data file(forum826.csv)
"ENGLAND","JAGUAR","V12XKE AUTO"
"ENGLAND","JAGUAR","XJ12L AUTO"
"ENGLAND","JENSEN","INTERCEPTOR III"
"ENGLAND","TRIUMPH","TR7"
"FRANCE","PEUGEOT","504 4 DOOR"
"ITALY","ALFA ROMEO","2000 4 DOOR BERLINA"
"ITALY","ALFA ROMEO","2000 GT VELOCE"
"ITALY","ALFA ROMEO","2000 SPIDER VELOCE"
"ITALY","MASERATI","DORA 2 DOOR"
"JAPAN","DATSUN","B210 2 DOOR AUTO"
"JAPAN","TOYOTA","COROLLA 4 DOOR DIX AUTO"
"W GERMANY","AUDI","100 LS 2 DOOR AUTO"
"W GERMANY","BMW","2002 2 DOOR"
"W GERMANY","BMW","2002 2 DOOR AUTO"
"W GERMANY","BMW","3.0 SI 4 DOOR"
"W GERMANY","BMW","3.0 SI 4 DOOR AUTO"
"W GERMANY","BMW","530I 4 DOOR"
"W GERMANY","BMW","530I 4 DOOR AUTO"


Here's the focexec to run a report from the above .csv and .mas files

-*
-* COPY THE DATA FILE(.CSV) AND MASTER FILE DESCRIPTION(.MAS)
-* FROM "YOUR_DIRECTORY" INTO THE "TEMP" DIRECTORY WEBFOCUS CREATES
-* DURING YOUR CURRENT SESSION
-*
CMD COPY \\YOUR_DIRECTORY\FORUM826.CSV
CMD COPY \\YOUR_DIRECTORY\FORUM826.MAS
-*
-* THE FILEDEF ASSOCIATES THE .CSV FILE WITH THE .MAS FILE
-*
FILEDEF FORUM826 DISK FORUM826.CSV
-RUN
-*
SET PCOMMA=ON
-*
TABLE FILE FORUM826
PRINT COUNTRY CAR MODEL
END

I don't know if you have any use for this method, but it seems to work for me.

Jim