Page 2 of 3

Re: how to read an excel file and load the only selected col

PostPosted: Mon Jun 25, 2012 11:42 pm
by dick scherrer
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.

Re: how to read an excel file and load the only selected col

PostPosted: Tue Jun 26, 2012 12:01 am
by chandra 2185
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.

Re: how to read an excel file and load the only selected col

PostPosted: Tue Jun 26, 2012 12:23 am
by dick scherrer
Hello,

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

Re: how to read an excel file and load the only selected col

PostPosted: Tue Jun 26, 2012 12:25 am
by Robert Sample
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.

Re: how to read an excel file and load the only selected col

PostPosted: Tue Jun 26, 2012 12:32 am
by chandra 2185
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.

Re: how to read an excel file and load the only selected col

PostPosted: Tue Jun 26, 2012 1:34 am
by NicC
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.

Re: how to read an excel file and load the only selected col

PostPosted: Tue Jun 26, 2012 8:27 am
by dick scherrer
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.

Re: how to read an excel file and load the only selected col

PostPosted: Wed Jun 27, 2012 3:30 am
by chandra 2185
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.

Re: how to read an excel file and load the only selected col

PostPosted: Fri Jun 29, 2012 5:35 pm
by Robert Sample
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.

Re: how to read an excel file and load the only selected col

PostPosted: Fri Jun 29, 2012 7:47 pm
by dick scherrer
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