Formatting for Excel data

srilakshmi_addala · New User Joined: 31 May 2007 Posts: 9 Location: Chennai

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

William Thompson · Posted: Wed Jun 20, 2007 2:28 pm

View the uploaded "Don t" in hex, it probably will not be a space (X'40').....

srilakshmi_addala · New User Joined: 31 May 2007 Posts: 9 Location: Chennai

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

Phrzby Phil · Posted: Wed Jun 20, 2007 5:51 pm

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.

dick scherrer · Posted: Wed Jun 20, 2007 7:31 pm

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.

srilakshmi_addala · New User Joined: 31 May 2007 Posts: 9 Location: Chennai

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.

dick scherrer · Posted: Fri Jun 22, 2007 6:15 pm

Hello,

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

What does this

srilakshmi_addala · New User Joined: 31 May 2007 Posts: 9 Location: Chennai

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.

dick scherrer · Posted: Mon Jun 25, 2007 12:43 pm

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.

srilakshmi_addala · New User Joined: 31 May 2007 Posts: 9 Location: Chennai

Thanks Dick,

I will try and let you know the status.

cvadlamudi · New User Joined: 19 Apr 2007 Posts: 68 Location: India

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

dick scherrer · Posted: Mon Jun 25, 2007 6:19 pm

Hello,

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

Thanx for the tip - it will surely help anyone who has some influence of how the files are created before transfer

srilakshmi_addala · New User Joined: 31 May 2007 Posts: 9 Location: Chennai

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.

dick scherrer · Posted: Mon Jun 25, 2007 7:05 pm

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.

cvadlamudi · New User Joined: 19 Apr 2007 Posts: 68 Location: India

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

srilakshmi_addala · New User Joined: 31 May 2007 Posts: 9 Location: Chennai

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

srilakshmi_addala · New User Joined: 31 May 2007 Posts: 9 Location: Chennai

Hi Mouli,

Installing s/w is not so ease in my company.
Please tell me some other opinion to overcome this problem

srilakshmi_addala · New User Joined: 31 May 2007 Posts: 9 Location: Chennai

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

cvadlamudi · New User Joined: 19 Apr 2007 Posts: 68 Location: India

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

dick scherrer · Posted: Mon Jun 25, 2007 7:39 pm

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.

cvadlamudi · New User Joined: 19 Apr 2007 Posts: 68 Location: India

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

Regards,
Mouli

cvadlamudi · New User Joined: 19 Apr 2007 Posts: 68 Location: India

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

cvadlamudi · New User Joined: 19 Apr 2007 Posts: 68 Location: India

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

cvadlamudi · New User Joined: 19 Apr 2007 Posts: 68 Location: India

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

dick scherrer · Posted: Mon Jun 25, 2007 8:22 pm

Hello,

It may be simpler to just use the original file and "edit" the content as needed once it reaches the mainframe.