IBM Mainframe Forum

Hello,

Possibly i misunderstand, but if i do understand, you have some Excel spreadsheet(s) on the PC that the users edit.

Once they have finished editing, they want some of the data loaded into DB2.

Is there some reason a process cannot be implemented on the PC that will extract the desired content into one (or more) data-delimited text files (which if done correctly could be directly loaded into db2). The data-delimited file(s) would be ftp'd as text files (no binary to worry about and no need for some COBOL API.

The excelworkbook has mutiple spreadsheets like sheet a,sheetb,sheetc,... and so on now when I am trying top save the excel as a tab delimited or csv file I am getting message that this type of mutiple worksheet cannot be converted to a tab delimited text file, only the current can be convereted. so this is the problem.

I want to have all the mutiple spreadsheets on one tab delimited text file so that I can directly ftp tat into a mainframe sequential dataset and cobol a program which can read the specified columns and the load them into db2 tables.

Hello,

One way would be to create multiple "output" files and then combine them later - either on the PC or on the mainframe.

Possibility 1: Can the JExcelAPI export the worksheets, either to separate files or appended to one file?

Possibility 2: Manually or programmatically export each worksheet to a different file, then concatenate them into a single file for transfer to the mainframe.

Possibility 3: Manually or programmatically xport each worksheet to a different file, upload each file to the mainframe, then concatenate them into a single DD name to your COBOL program.

Hi,

The problem here is I have 2000 tabs and this is also not fixed it depends the data fetched by the cursor in the cobol program that generate this spreadsheet.

so I dont think so the steps mentioned will work here bcoz it doesn't looks feasible. Curren tly I am looking for a macro that can write all the onmutiple tabs in a single tab delimited text file. so that I can ftp this text file to sequesntial dataset on mainframe and then a cobol program can read this dataset.

Robert has already stated that you can export each sheet as a csv (or some other delimiter if your data contains commas). Each CSV file can then be ftped or NDMed, or whatever, to the mainframe. You can then use your sort product to parse the data into fixed length fields. Examples abound - I know because I did this a couple of weeks ago using the postings on this, and other, forums.

Hello,

Yes, i agree 2000+ is too many to do manually . . .

I don't "do" Excel- mostly because i was well-entrenched in databases long before there was even a VisiCalc or Lotus123. I missed the mba generatoin that all have sworn by the spreadsheet.

With the newer releases of Excel, i believe you can "program" to do what you want. You might also look at some of the open-source tools available and see if there is one that will read many tabs and generate an output file.

Hi,

I have found a window batch script which can convert the multiple sheet xls to csv file. now when i ftp the csv file to maiframe sequential I am not how to define the layout for this file. Because I have to read specific columns of this file and this has some headings also in it. The csv file looks like the attachment.

Please let me know how to define the recored layout for this. Thank you.

You can use SORT to define a fixed position output file from the CSV input file, or you write COBOL code to convert the CSV file, or you can use some other utility your site may have to convert the CSV file. How you do the conversion is entirely up to you.

Hello,

One way to handle this data on the mainframe is to have a "record layout" that is a single field of the entire record and use the UNSTRING in a bit of COBOL code. Once unstrung, the individual files can be moved to a new output record layout that you would use to load.

You need to decide which will be easier to write and maintain (Sort or COBOL) as there may be additional requirements tha need more or less data than the original process