IBM Mainframe Forum

I have a situation where I have to read an excel spreadsheet and load the data into db2 tables.

I have read only selected columns in the spreadsheet and then verify whether the data is in correct format and then load them into db2 tables.

Can anyone please tell me how can do tjis in an efficient way. Thank you.

1. Excel is a PC software product. Are you running on a mainframe? If not, this forum is for mainframe questions and hence you are very unlikely to get any kind of help here.
2. If you ARE running on a mainframe, how are you converting the Excel data to something a mainframe can process? And how are you moving the data to the mainframe?
3. Without knowing answers to these questions, attempting to provide you any further assistance is futile.

Hello,

A method i've used on many platforms is to write the selected data to a data-delimited file (i prefer the tilde "`" as this is quite uncommon in business data).

As this is Excel, i believe the Excel Export will do what you want.

Using the delimited file as input to a LOAD, you should be able to get the rows into the db2 table (on any of the db2 platforms). Depending on the target, you may need to ensure there is a cr/lf or whatever so all of the data does not "roll-up" into one big record.

Hi,

Actually my situation is little different. I wrote a cobol pgm which will generate a spreadsheet as the output,using the JEXCEL APIand now the user will edit some this data and then i wanted to know: do a cobol program will be able to read the excel file once the file is ftped to a particular location, if not then is then as mentioned by d.sch the export can be done automatically or again it has to be manually converted to data-delimited file.

Hello,

Your options will depend on the actual content of the file created by the JEXCEL API.

How will the user edit a file in a proprietary format? Is this part of the JEXCEL product? Will the editing be done on the mainframe or on the pc?

Once the file is ftp'd using COBOL will depend on what is available on the target system.

If you look at the "Excel formatted" file, it will not look like a text file (unless it is a simple data-delimied file.

I am currently generating the muiltiple worksheet excel file using JEXCEL API and this will be saved in a particular location where user can go and edit the the data on the file. The data on the spreadsheet will be like this for eg: each sheet gives information about a store so I will have as many sheets as the stores fetched by my cursor in the COBOl pgm.
Now the user will edit the data on the spreadsheet and then i have to find out a way to read this excel file using a cobol pgm and then populate the data into db2 tables. how can this be done. Please guide with the solution.

Thanks.

1. Unless the Excel spreadsheet resides on a server, you will have to figure out how to transfer it to the mainframe. If it does reside on a server, you can use FTP (among other products) to directly transfer the Excel spreadsheet to the mainframe.

2. There is software available that can be purchased and installed on the mainframe to allow an Excel spreadsheet to be read. Whether or not that software has a COBOL API, however, I do not know -- but you need to find out.

3. If your site does not currently have such software installed, you will either need to get your site to purchase the software or develop an internal way to read the Excel spreadsheet. The last time I looked, a couple of years ago, the Microsoft Excel file specification document was well over 1000 pages so developing an internal way most likely will take 3 or 4 people a year (or two) to complete.

4. Mainframe COBOL does NOT deal with PC software directly -- the usual way to handle Excel is to convert each worksheet to a .CSV file (so if your Excel file has 7 worksheets, you'll need to create 7 .CSV files), upload each separately, and then process each as a sequential file on the mainframe -- unless a software product is installed on the mainframe to handle the data, as mentioned above.

It sounds very much like you've got a soution and now you want to warp your problem to fit the solution. This rarely works out well.

I have my file sitting on a server and the i have the ftp too. So now my question is can I ftp the mutiple sheet xls file to a single sequentail dataset to mainframe? can this be done ?

Of course you can FTP the Excel file to the maifnrame as a sequential file -- however, the Excel file will NOT be usable on the mainframe without a software product to read the data file and extract the data, or customized code that implements the Microsoft Excel file specification standard to read the data from the Excel spreadsheet.

The mere fact that you can create a sequential file from PC data does not mean that the data in that file can be used in any way on the mainframe. It merely means the data can be transferred from the PC. And since Excel uses non-display characters, you have to do the transfer as a binary, not text, file -- hence it will not have a record structure and most likely you'll have to read and interpret every byte in the file (unless your site has software to do that as I mentioned in my last post).

Yeah. tats what I figured out, though the excel file can be ftped the dat in it is in binary format and I dont think so I can interpret tat. Moreover I dont think so our shop has the kond of software that you hav mentioned. So i think I have to come up with some other alternative to achieve this.