how to read an excel file and load the only selected columns



Support for OS/VS COBOL, VS COBOL II, COBOL for OS/390 & VM and Enterprise COBOL for z/OS

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

Postby dick scherrer » Mon Jun 25, 2012 11:42 pm

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

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

 

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

Postby chandra 2185 » Tue Jun 26, 2012 12:01 am

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.
chandra 2185
 
Posts: 17
Joined: Mon Jun 18, 2012 2:30 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby dick scherrer » Tue Jun 26, 2012 12:23 am

Hello,

One way would be to create multiple "output" files and then combine them later - either on the PC or on the mainframe.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

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

Postby Robert Sample » Tue Jun 26, 2012 12:25 am

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.
Robert Sample
Global moderator
 
Posts: 3367
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 222 times

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

Postby chandra 2185 » Tue Jun 26, 2012 12:32 am

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.
chandra 2185
 
Posts: 17
Joined: Mon Jun 18, 2012 2:30 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby NicC » Tue Jun 26, 2012 1:34 am

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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

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

Postby dick scherrer » Tue Jun 26, 2012 8:27 am

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

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

Postby chandra 2185 » Wed Jun 27, 2012 3:30 am

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 do not have the required permissions to view the files attached to this post.
chandra 2185
 
Posts: 17
Joined: Mon Jun 18, 2012 2:30 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby Robert Sample » Fri Jun 29, 2012 5:35 pm

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.
Robert Sample
Global moderator
 
Posts: 3367
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 222 times

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

Postby dick scherrer » Fri Jun 29, 2012 7:47 pm

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
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

PreviousNext

Return to IBM Cobol

 


  • Related topics
    Replies
    Views
    Last post