 |
|||||
|
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. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Read Excel into a mainframe FOCEXEC
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Read Excel into a mainframe FOCEXEC Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Read Excel into a mainframe FOCEXEC | Login/Join |
| Guru |
I'm running Excel 2007 and have an Excel file I'd like to be able to have MAINFRAME FOCUS RELEASE 7.1.1 read in. What format should I save my Excel file in to have MAINFRAME FOCUS easily read, being fully aware I will have to creat a Master file for the MAINFRAME FOCUS program to work? For whatever reason, my old MAINFRAME FOCUS does not seem to like CSV-type files, and so I'm wondering what might be a good file format to use. Anyone who can drop a hint or idea is most welcome, and thank you in advance.This message has been edited. Last edited by: webmeister, Mainframe FOCUS 7.0 VM/CMS and MVS/TSO | ||
|
| Expert |
Web, SEARCH on EXCEL ODBC Tom Tom Flynn WebFOCUS 8.1.05 - PROD/QA DB2 - AS400 - Mainframe | |||
|
| Expert |
A csv file is a comma-delimited file. What happens if you create a master with SUFFIX=COM. You could also export the spreadsheet as a tab-delimited file. Check to see if the mainframe supports either of these file types. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Guru |
Hi, Ginny and thank you... I'll try your suggestion. I had tried a master with SUFFIX-COMT, but that had not worked, so I'll try your thoughts. Tom, thank you also.... not sure I understood your reply; I'm hoping my plight can be resolved with using a proper Master. Mainframe FOCUS 7.0 VM/CMS and MVS/TSO | |||
|
| Virtuoso |
For a one-shot deal, fixed format is simplest. To make sure no columns get truncated, I change all cells to a fixed font (e.g., Courier new), and then autosize all columns (control-A, ALT+O C A). Then Save As..., selecting "Formatted Text (space delimited)" as the format. That creates a local .TXT file. Determine the column-widths and overall record-length of the .TXT file, and upload it to a dataset with corresponding LRECL. Build your MFD and you should be all set. | |||
|
| Expert |
Web, SORRY!! My BAD!! "Mainframe" - it's been since last century!! j.gross example is the play; we use to SAVE AS prn and FTP to the mainframe, IEBGENER to a GDG file, for history, write a MASTER for the file and away we go. Tom Tom Flynn WebFOCUS 8.1.05 - PROD/QA DB2 - AS400 - Mainframe | |||
|
| Guru |
To all who have replied, thank you! Tom, not a problem; prior to this job, it had been 11 years since I played with mainframe FOCUS, which is why I now ask a lot of questions on this forum - I'm RUSTY!!! In any case, I appreciate your willingness to offer help. Mainframe FOCUS 7.0 VM/CMS and MVS/TSO | |||
|
| Master |
A word of warning, EXCEL files uploaded to the mainframe are a pain to work with. Your master may need to get "tweaked" every time you run this. If it is a file created or modified by a user, you will need to watch out for additional columns, resized columns and rearranged columns. If the data can be saved in any other readable format I would recommend it. Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes | |||
|
| Master |
In FOCUS for z/OS 7.1.1 I would consider this process: 1. Create the file in .csv format with Excel on the PC: Gern ,Blanston,50.2 Steve,Martin,25.4 2. Upload .csv file to z/OS. 3. Read uploaded file with the comma delimiter defined in the Master File: FILENAME=TESTIT, SUFFIX=DFIX,$ SEGNAME=ONLYONE, SEGTYPE=S0,$ FIELDNAME=FIRSTNAME , ALIAS=FN , FORMAT=A20 , ACTUAL=A20 FIELDNAME=LASTNAME , ALIAS=LN , FORMAT=A20 , ACTUAL=A20 FIELDNAME=SALARY , ALIAS=SAL , FORMAT=A10 , ACTUAL=A10 FIELDNAME=DELIMITER , ALIAS=',' , FORMAT=A01 , ACTUAL=A01 Your report, from a TABLE using this MFD, against the uploaded file would look something like this: DYNAM ALLOC DD TESTIT DS TLA.TESTIT.CSV SHR REUSE -RUN TABLE FILE TESTIT PRINT * END FIRSTNAME LASTNAME SALARY DELIMITER --------- -------- ------ --------- Gern Blanston 50.2 , Steve Martin 25.4 ,
| ||||
|
| Member |
Hey... I had the exact same thing. The problem is that when you go to read the csv file on the mainframe, it does not honor the csv format as the server does. The way that I got around this was to make sure that you define the column length long enough to include the column titles in the csv file. Once you pull through to the mainframe, you have to use a WHERE clause to exclude the column titles from the data. You then also have to use DEFINE's to get the length/format of the data you want. It is very round about and not very pretty, but it works. I had a case on it out there if you do a search. Karen Williams NCFB 1. IWAY Servers accessing DB2, MS SQL Server, Flat files, release 5.3.3 running on MVS/USS and Windows 2. ETL Data Migrator 5.3.3 | |||
|
| Expert |
If you use the DFIX suffix, if it is supported on the mainframe, it uses an access file where you can specify HEADER=NO and delete the header line from the file. It you want to automate that, you could use Dialogue Manager. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Guru |
Hello and one more thank you to all who responded. ctually, I saved my file as a space-delimited text file, checked the length of all data fields, then built a master, and was able to coninue my efforts with this project. My master had a file type of FIX, and using WS-FTP, I uploaded te file to my mainframe. In other words, a straight-forward text file that is space delimited worked nicely. The only thing I did to my data file was to remove the first column from the data. Just wanted let you know. Mainframe FOCUS 7.0 VM/CMS and MVS/TSO | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Read Excel into a mainframe FOCEXEC
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Read Excel into a mainframe FOCEXECCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |