IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Formatting for Excel data


IBM Mainframe Forums -> FAQ & Basics
Post new topic   This topic is locked: you cannot edit posts or make replies.
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
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: 3156
Location: Tucson AZ

PostPosted: Wed Jun 20, 2007 2:28 pm
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
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

Senior Member


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

PostPosted: Wed Jun 20, 2007 5:51 pm
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

Moderator Emeritus


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

PostPosted: Wed Jun 20, 2007 7:31 pm
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
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

Moderator Emeritus


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

PostPosted: Fri Jun 22, 2007 6:15 pm
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
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

Moderator Emeritus


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

PostPosted: Mon Jun 25, 2007 12:43 pm
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
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
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

Moderator Emeritus


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

PostPosted: Mon Jun 25, 2007 6:19 pm
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
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

Moderator Emeritus


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

PostPosted: Mon Jun 25, 2007 7:05 pm
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
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 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
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 Use [URL] BBCode for External Links
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 Use [URL] BBCode for External Links

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
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
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
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

Moderator Emeritus


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

PostPosted: Mon Jun 25, 2007 7:39 pm
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
cvadlamudi

New User


Joined: 19 Apr 2007
Posts: 68
Location: India

PostPosted: Mon Jun 25, 2007 7:40 pm
Reply with quote

no dick, .prn will support only to write 255 this is not due to word wrap concept in notepad.

Regards,
Mouli
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:51 pm
Reply with quote

Dick,

We can also upload the file and using TSO commands we can merge the data in the file.

===== aaaa1
===== bbbb1
===== dddd1
=))5== AAAA2
===== BBBB2
=))5== DDDD2

so that the 2nd split will be moved to 6th po

===== aaaa1
===== bbbb1
===== dddd1
===== AAAA2
===== BBBB2
===== DDDD2

and override the above 3 lines with the below then the file will contain

===== aaaa1AAAA2
===== bbbb1BBBB2
===== dddd1DDDD2

this is do without a cobol program.
First test with a small file with ur test data and check the process.

Regards,
Mouli
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:53 pm
Reply with quote

Dick,

We can also upload the file and using TSO commands we can merge the data in the file.

===== aaaa1
===== bbbb1
===== dddd1
=))5== AAAA2
===== BBBB2
=))5== DDDD2

so that the 2nd split will be moved to 6th po

===== aaaa1
===== bbbb1
===== dddd1
===== AAAA2
===== BBBB2
===== DDDD2

and override the above 3 lines with the below then the file will contain

===== aaaa1AAAA2
===== bbbb1BBBB2
===== dddd1DDDD2

this is do without a cobol program.
First test with a small file with ur test data and check the process.

Regards,
Mouli
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:54 pm
Reply with quote

Dick,

We can also upload the file and using TSO commands we can merge the data in the file.

===== aaaa1
===== bbbb1
===== dddd1
=))5== AAAA2
===== BBBB2
=))5== DDDD2

so that the 2nd split will be moved to 6th po

===== aaaa1
===== bbbb1
===== dddd1
===== -------AAAA2
===== -------BBBB2
===== -------DDDD2

and override the above 3 lines with the below then the file will contain

===== aaaa1AAAA2
===== bbbb1BBBB2
===== dddd1DDDD2

this is do without a cobol program.
First test with a small file with ur test data and check the process.

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

Moderator Emeritus


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

PostPosted: Mon Jun 25, 2007 8:22 pm
Reply with quote

Hello,

It may be simpler to just use the original file and "edit" the content as needed once it reaches the mainframe.
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. View Bookmarks
All times are GMT + 6 Hours
Forum Index -> FAQ & Basics Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
Search our Forums:

Back to Top