Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Import excel into mainframe file.
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> PC Guides & IT News
View previous topic :: :: View next topic  
Author Message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sat Feb 12, 2011 8:10 pm    Post subject: Import excel into mainframe file.
Reply with quote

Pls anyone give me the job to import an excel file from server/desktop into a mainframe flat file. I don't want to use the manual option START 6- option. I am looking to automate one of the processes in my project.

pls advise
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Sat Feb 12, 2011 8:13 pm    Post subject:
Reply with quote

Unless you have software that handles the Excel format on your mainframe, you cannot directly transfer an Excel file to the mainframe and expect it to be usable. This is usually done by converting the Excel file to .CSV (comma delimited) or tab delimited format and transferring that.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sat Feb 12, 2011 8:49 pm    Post subject:
Reply with quote

Hii Robert! Ok is the conversion of xcel to CSV a simple process? it may be a very basic question but I really don't know how to convert pls help me.

My basic requirement is I have a row of tabular data with 5 fields. I need to import that into a mainframe file and it should be done automatically if I run a job. Other things I will take care in the job.
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 961
Location: Richmond, Virginia

PostPosted: Sat Feb 12, 2011 10:49 pm    Post subject:
Reply with quote

Try typing CSV into the Excel help window.

Learning the HELP feature of any software is mandatory.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sat Feb 12, 2011 11:03 pm    Post subject: Reply to: Import excel into mainframe file.
Reply with quote

Quote:
it should be done automatically if I run a job

forget about it!
since the excel data is supposedly created by humans hands ( I said hands not brain )
the hands that created the data should start the whole shebang!

all the <file transfer> activity which involves a desktop should be started by a <desktop> action

open a FTP session with Your host, and transfer the file
schedule the job to be run by submitting it, or using the trigger facilities of Your job scheduling system
You could minimize the typing by scripting the whole process.

any other way of proceeding is against any sound IT practices!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Feb 12, 2011 11:35 pm    Post subject:
Reply with quote

Hello,

Unless your organization is willing to pay $ for an integrated solution (they may already have done so - suggest you find out) you will need 2 "pieces".

One "piece" to unload the spreadsheet into a text file and ftp this to the mainframe.

The other to process the uploaded text fle on the mainframe.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sun Feb 13, 2011 1:14 am    Post subject: Re: Reply to: Import excel into mainframe file.
Reply with quote

enrico-sorichetti wrote:
Quote:
it should be done automatically if I run a job

forget about it!
since the excel data is supposedly created by humans hands ( I said hands not brain )
the hands that created the data should start the whole shebang!

all the <file transfer> activity which involves a desktop should be started by a <desktop> action

open a FTP session with Your host, and transfer the file
schedule the job to be run by submitting it, or using the trigger facilities of Your job scheduling system
You could minimize the typing by scripting the whole process.

any other way of proceeding is against any sound IT practices!


You are 100% right excel is created by human hands..You are reading between the lines icon_smile.gif

I appreciate the attention you give to our posts.But why are you emphasizing on opening a FTP session on the host..Are you speaking about the start 6 option what we generally do?
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sun Feb 13, 2011 1:23 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Unless your organization is willing to pay $ for an integrated solution (they may already have done so - suggest you find out) you will need 2 "pieces".

One "piece" to unload the spreadsheet into a text file and ftp this to the mainframe.

The other to process the uploaded text fle on the mainframe.


Yes, Dick my organization will be happy to pay $ if I am able to optimize their process by automation icon_biggrin.gif Definately it will save manual work and reduce the chances of error.

But why you wanna split the work into 2 pieces? Can't we do it in one shot if we have the formatted CSV file in right plce in the server/desktop. What I am thinking is to frame only 1 job which will automatically pick the CSV feed from the specified server location and convert it into a mainframe file and then start processing it once it gets the data in a PS. And yes Excel to CSV conversion will be done manually.

I hope I am able to state my thoughts clearly. Is this approach feasible?

Thanks.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sun Feb 13, 2011 1:28 am    Post subject: Reply to: Import excel into mainframe file.
Reply with quote

NO, TSO is NOT needed, TCP IP connectivity is enough!

everything is done on the PC
search the forums for FTP file transfer and You will find lots of examples

speak to Your support, they are the ones who have the final word on the process
so getting into too much detail is a waste of time if they do not know/understand
and the burden of explanation is on You!
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sun Feb 13, 2011 1:36 am    Post subject: Reply to: Import excel into mainframe file.
Reply with quote

as usual there is a language barrier here....

step0 ==> fill the EXCEL worksheet
step1 ==> save it as a CSV
step2 ==> from a command prompt use FTP to do a PUT of the local file to the mainframe dataset
step3 ==> option 1 ( the better one ) use Your scheduler dataset trigger on the just uploaded file to automagically schedule the job to propcess the uploaded file
step3 ==> option 2 , logon to TSO/use an existing TSO session to submit the job
step3 ==> option 2a , use the same FTP session to submit the job
option 1 is better because the Job will not be stored on Your PC

in any case if Your organization is willing to spend $ on such an issue...
well they are idiots icon_biggrin.gif

in any case running everything from a job is not reasonable,
how are You going to make the JOB aware that the file is ready to be processed ?

I do not see anything to automate!

Quote:
server/desktop

a desktop is not supposed to play a server role!
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sun Feb 13, 2011 3:09 am    Post subject:
Reply with quote

Thanks a lot for the detailed explanation. Now the picture is clear to me..Step 3==> option 1 & 2 both looks good. I will enquire with the support guys and select the best approach!

In my case actually currently they are doing everything manually like counting the no of rows in excel etc. seting the breakpoint to commit and lot of tededious processes. and finally arrive at the update query..So my duty is to cut short all the manual work and design job to automate the process so that the users will just give the input file and we will convert it into CSV in the server location and run the job. And everything elese will be taken care by the job..This small automation if tested properly and implemented shall reduce the room for any errors which seems to be frequently happening as humans are bound to commit errors..

Thank you again

icon_smile.gif
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sun Feb 13, 2011 3:14 am    Post subject:
Reply with quote

May I ask why do you feel so as you stated "in any case if Your organization is willing to spend $ on such an issue...
well they are idiots" ?? Is there any better approach to such such issues...I would be happy if you give a candid feedback icon_lol.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Feb 13, 2011 3:26 am    Post subject:
Reply with quote

Hello,

Quote:
Yes, Dick my organization will be happy to pay $ if I am able to optimize their process by automation
The $ would be to purchase some existing solution. . . It would not be for you to develop.

Quote:
But why you wanna split the work into 2 pieces?
It has nothing to do with what "i wanna". It has to do with the simple fact that spreadsheet creation/update is most often pc work while mainframe jobs run on the mainframe. While on the pc, the data needs to be saved in a "mainframe compatible" format (usually a [delimited] text file).

If the users working on these spreadsheets save the files to some server(s) that happen to also be ftp servers, a job on the mainframe could "pull" the file from the "file server" where the spreadsheets are stored and then continue with the mainframe process.

Even with this there is still the co-ordination of when to run the mainframe process to pull the files as it may be difficult to know when all of the fles are ready to be uploaded.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Feb 13, 2011 3:28 am    Post subject: Reply to: Import excel into mainframe file.
Reply with quote

You were submitting while is was typing. . . icon_smile.gif

Quote:
would be happy if you give a candid feedback
Have no fear - many here are quite candid icon_wink.gif

d
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sun Feb 13, 2011 3:29 am    Post subject: Reply to: Import excel into mainframe file.
Reply with quote

because if Your organization does not have inside the skills for such a simple task ...
well, meditate about it!

this is an issue that anybody with average skills should be able to solve in about 10 minues!
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sun Feb 13, 2011 3:43 am    Post subject:
Reply with quote

This is an adhoc request mostly a weekly activity.And we get an email from the users that the excel is in place in server to initiate the process..

But manually it takes around 1-2 hour to do all the stuff in excel..check the database & frame the queries.. and lot of errors happening.. Tats why my manager asked me to automate..I don't have say here but to complete the task..Now I know it is not a very big issue technicaly but the business impact due to errors is significant so the issue has been highlighted..


icon_lol.gif

Thanks everyone for their inputs!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Feb 13, 2011 4:22 am    Post subject:
Reply with quote

Hello,

Why does the database or the query code need any work after once being implemented?

If the database needs work every iteration, i'm not sure what might be automated. . . Same with the queries.

It sounds like there may be much more to the situation than simply uploading data frm a spreadsheet.

And yes, many of us have experience with "things" that become elevated/highlighted like this icon_smile.gif
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sun Feb 13, 2011 12:56 pm    Post subject:
Reply with quote

Hi Dick,

Let me explain, in current scenario the starting feed (spreadsheet1 - supplied by user) has only one row.. We manually evaluate the spreadsheet data and accordingly run around 5 to 6 queries using the data from that spreadheet to get more information and construct a new spreadsheet2 which may have lakhs of rows. Again some manual work like counting is required on the consructed spreadsheet2 to find the rows where to commit. After thsi finally we arrive at the database update query and hard cord the commit points.. And the adhoc batch SPUFI job is submitted to update the database for spreadsheet2. This process looks very messy amd cumbersome and prone to errors.

So I am trying to create a job which will have 2 steps:-

Step 1: import that starting CSV feed excel file into mainfrme file
Step 2: a program which will do all the operations (logic for all the manual work + update)

So we will ask the users to put the input feed in fixed CSV format at the FTP server and send an email notification. Then we will run the job. And the process completed.

Thanks,
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Sun Feb 13, 2011 1:00 pm    Post subject:
Reply with quote

I hope this gives some insight. Any suggestions are most welcome!
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Sun Feb 13, 2011 1:55 pm    Post subject:
Reply with quote

Create an Excel macro in VBA to :

1. convert the sheet to a csv file
2. ftp this file to the host

At the host let a scheduler trigger a job when the data has arrived
to process it.

For the Excel macro do some googling, enough samples to find
on the internets. I used for example : ftp from excel as search argument
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> PC Guides & IT News All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
This topic is locked: you cannot edit posts or make replies. Urgent Mainframe Requirments (2-10 yr... Shankar779 Mainframe Jobs 0 Sun Nov 27, 2016 1:48 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us