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
 

 

Formatting for Excel data
Goto page 1, 2  Next
 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> FAQ & Off Topics
View previous topic :: :: View next topic  
Author Message
srilakshmi_addala

New User


Joined: 31 May 2007
Posts: 9
Location: Chennai

PostPosted: Wed Jun 20, 2007 12:24 pm    Post subject: Formatting for Excel data
Reply with quote

Hi ,

I have to upload the XL data to mainframe.

I tried saving the XL as Tab delimiter, and using FTP option i uploaded into mainframe.
After that with help of unstring comment i have almost done with it.

But my input file contain special character like ' (Apostrophe) , which is not getting uploaded properly

For Eg :

Input file value : Don't
Uploaded value : Don t


Thanks in Advance
Sujatha
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Jun 20, 2007 2:28 pm    Post subject:
Reply with quote

View the uploaded "Don t" in hex, it probably will not be a space (X'40').....
Back to top
View user's profile Send private message
srilakshmi_addala

New User


Joined: 31 May 2007
Posts: 9
Location: Chennai

PostPosted: Wed Jun 20, 2007 2:55 pm    Post subject: Uploading for XL data to Mainframe
Reply with quote

Yes it is not spaces its Hex value is X'12'.

Actual requirement is we will get the input file in the XL format.
Have to upload that into mainframe
Have to check one column from the file with the table value
But in this case the file value and table value will be different, so how to handle this?
Same problem i face with # also

XL Value :NO# 19, LOTUS STREET
But in file the value after uploading is "NO# 19, LOTUS STREET". i.e it is displayed inside the double quotes
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


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

PostPosted: Wed Jun 20, 2007 5:51 pm    Post subject:
Reply with quote

Your double quotes are due to the comma in that cell. If you have other cells in that column without special delimiters, they will not have double quotes.

My test uploaded the apostrophe in Don't properly, as x'7D'.

I tried Don't and 'Hi there' in Word with "straight to smart quotes" turned on, then pasted into Excel, then saved tab-delim and uploaded. In this case, the quote in don't and the end quote in 'hi there' uploaded as x'1A' (end quote), and the 1st quote in 'hi there' as x'31' (start quote). I still could not get your x'12'.

Note here the danger in uploading text to mainframe without turning smart quotes off.
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: Wed Jun 20, 2007 7:31 pm    Post subject:
Reply with quote

Hello and welcome to the forums,

When you "save" the file in XL, what do you specify as the delimiter?

If you use the backslash "\" or the tilde "~", you may have better luck - they are not likely to appear in your data.

If the entire address is one field in XL, you will possibly have to break it into components on the mainframe.
Back to top
View user's profile Send private message
srilakshmi_addala

New User


Joined: 31 May 2007
Posts: 9
Location: Chennai

PostPosted: Fri Jun 22, 2007 11:02 am    Post subject: Uploading for XL data to Mainframe
Reply with quote

Thanks a lot for the replies

Actually my input file contain some junk value which appears as Apostrophe (') but not the same.
So there is no problem in uploading fields with Apostrophe.

Can you advice how to handle the comma while uploading from excel to notepad.
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: Fri Jun 22, 2007 6:15 pm    Post subject:
Reply with quote

Hello,

We can probably help, but you need to provide some information that we can use to help.

What does this
Quote:
Can you advice how to handle the comma while uploading from excel to notepad.
mean? There is no upload to notepad that i an aware of - you simply save the file on the pc. I also do not understand what "handle the comma" means.

If you have transferred some data to the mainframe and it does not appear to be what you need, you need to browse the mainframe dataset, view the questionable data in hex, and post what you see here in a reply (as well as what you want it to be). I know of no situation that will convert junk to apostrophe - i've seen many unexpected results when people xfer data, but that has not yet been one of them.
Back to top
View user's profile Send private message
srilakshmi_addala

New User


Joined: 31 May 2007
Posts: 9
Location: Chennai

PostPosted: Mon Jun 25, 2007 10:11 am    Post subject: Uploading for XL data to Notepad
Reply with quote

Hi Dick

Let me tell you my requirement properly

Actually I will receive the file from client in a XL format.
Have to upload that XL data to mainframe and use that file as input to my program.

Steps I followed are
1) Saved XL file as Tab delimiter
2) Uploaded that notepad content to mainframe using FTP command.

But my XL sheet contains address field, which has comma in that data.
While saving the XL data as tab delimiter that address field is enclosed by double quotes.
So this may create a problem while comparing that field with table value in the program.

So please advice me how to avoid the double quotes in the notepad if the field contain comma.

Hope now I am clear to you.
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: Mon Jun 25, 2007 12:43 pm    Post subject:
Reply with quote

Hello,

Just write a bit of code on the mainframe to use the tab-delimiter to separate the input into indivudual fields (UNSTRING). Then parse the fields removing the double-quotes where they might be, left-justifying the result so there will not be a leading space.
Back to top
View user's profile Send private message
srilakshmi_addala

New User


Joined: 31 May 2007
Posts: 9
Location: Chennai

PostPosted: Mon Jun 25, 2007 2:29 pm    Post subject: Uploading for XL data to Notepad
Reply with quote

Thanks Dick,

I will try and let you know the status.
Back to top
View user's profile Send private message
cvadlamudi

New User


Joined: 19 Apr 2007
Posts: 68
Location: India

PostPosted: Mon Jun 25, 2007 3:31 pm    Post subject:
Reply with quote

Hi,

you have to save the XL file in format : Formatted Text (Space delimited)(*.prn). so that you wont get Quotes...
it will be saved in .prn format and change it to .txt
while creating the notepad take care of field lenth & format u need.

Thanks,
Mouli
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: Mon Jun 25, 2007 6:19 pm    Post subject:
Reply with quote

Hello,

That (*.prn) sounds cool. I'm usually stuck with "whatever shows up" icon_confused.gif

Thanx for the tip - it will surely help anyone who has some influence of how the files are created before transfer icon_smile.gif
Back to top
View user's profile Send private message
srilakshmi_addala

New User


Joined: 31 May 2007
Posts: 9
Location: Chennai

PostPosted: Mon Jun 25, 2007 6:53 pm    Post subject: Uploading for XL data to Notepad
Reply with quote

Hi Mouli,

Thanks for that.
I tried saving that as (.prn)
My file size is huge( nearly 500bytes). A single record is divided into two lines.

For ex :

Address1,address2,phone no ------------ in one line
Email ID, website ------------ in another line.

but it should display as
address1,address2,phone no,Email id,website

So this is again a problem while uploading it into mainframe.

Please advice.
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: Mon Jun 25, 2007 7:05 pm    Post subject:
Reply with quote

Hello,

Did you change the attributes of the file on the mainframe to accomodate the new data format?

If not, what you are seeing may be a "wrap" when the input record is longer than the mainframe file is defined to handle. You might try deleting and re-allocating the mainframe file with a much longer record length and see if this eliminates the multi-line problem.
Back to top
View user's profile Send private message
cvadlamudi

New User


Joined: 19 Apr 2007
Posts: 68
Location: India

PostPosted: Mon Jun 25, 2007 7:16 pm    Post subject:
Reply with quote

Hi Lakshmi,

Yes if the file length is huge (>255) then the file will wrap up you cannot use *.prn.

There is a software to convert .XLS to .txt if u give ur mail id i will send u.
you can also check in link http://www.softinterface.com/FAQ/FAQ-General.htm

Regards,
Mouli
Back to top
View user's profile Send private message
srilakshmi_addala

New User


Joined: 31 May 2007
Posts: 9
Location: Chennai

PostPosted: Mon Jun 25, 2007 7:18 pm    Post subject:
Reply with quote

Hi Dick

It happens in the notepad itself.

After saving the XL as .prn , the record is divided into two line

For example
--------------
It should be like this

Address1 phone no email id website
No,20 lotus street 044-234456 sssss@yhaoo.com www.yahoo.com
No,15,white house 044-23456

but in notepad it is appearing like

Address1 phone no
No,20 lotus street 044-234456
No,15,white house 044-23456
email id website
sssss@yhaoo.com www.yahoo.com

Hope i am clear
Back to top
View user's profile Send private message
srilakshmi_addala

New User


Joined: 31 May 2007
Posts: 9
Location: Chennai

PostPosted: Mon Jun 25, 2007 7:28 pm    Post subject:
Reply with quote

Hi Mouli,

Installing s/w is not so ease in my company.
Please tell me some other opinion to overcome this problem
Back to top
View user's profile Send private message
srilakshmi_addala

New User


Joined: 31 May 2007
Posts: 9
Location: Chennai

PostPosted: Mon Jun 25, 2007 7:34 pm    Post subject: Uploading for XL data to Notepad
Reply with quote

Hi Mouli,

Installing s/w is not so ease in my company.
Please tell me some other way to overcome this problem.
I hope i have to write a Cobol program as said by Dick
Back to top
View user's profile Send private message
cvadlamudi

New User


Joined: 19 Apr 2007
Posts: 68
Location: India

PostPosted: Mon Jun 25, 2007 7:37 pm    Post subject:
Reply with quote

Hi Lakshmi,

It is a mini software.. it wont harm anything... u can install & uninstall the software after the work is done.. I hope u r not much aware of installation.

If u use software it is a 1 min work to convert & upload else...

u have to do a number of process

1) upload the file to mainframe.
2) Using online fileaid split the file to as any splits u had in ur notepad.

aaaa1
bbbb1
cccc1
aaaa2
bbbb2
cccc2

move 1st split in 1st file & 2nd split in 2nd file & so on if u have many

and write cobol program to write file or use any JCL Utility.

Regards,
Mouli
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: Mon Jun 25, 2007 7:39 pm    Post subject:
Reply with quote

Hello,

In notepad, click on "Format" and see if "Word Wrap" is checked. If it has a check-mark, click it off and then look at your data.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> FAQ & Off Topics 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
This topic is locked: you cannot edit posts or make replies. Unwrap the data based on delimiter X'25' bhavana yalavarthi DFSORT/ICETOOL 30 Fri Dec 09, 2016 10:25 am
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm


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