| View previous topic :: View next topic |
| Author |
Message |
mfuser
Banned
Joined: 01 Mar 2005
Posts: 105
Topics: 58
|
 Posted: Tue Jul 04, 2006 9:38 am Post subject: what is a CSV File |
 |
|
Members,
I would like to know what is a CSV File and is there any difference in using UNSTRING for a normal string and a string from a CSV File.Can any body help me out with the link to CSV File ? |
|
| Back to top |
|
 |
Vishwakiran
Beginner
Joined: 29 Jun 2004
Posts: 11
Topics: 8
Location: Bangalore
|
| Posted: Tue Jul 04, 2006 12:57 pm Post subject: |
|
|
CSV file is a comma separated file. Each field is separated by comma. This format is useful for processing the variable length fields.
In MS excel you have the option to save the file as CSV file.h
_________________
Regards,
Vishwakiran |
|
| Back to top |
|
|
mfuser
Banned
Joined: 01 Mar 2005
Posts: 105
Topics: 58
|
| Posted: Tue Jul 04, 2006 2:19 pm Post subject: |
|
|
Hai,
Can you post a sample of the CSV File (Dataset) i mean how do the records look like and can u please let me know any real time use of CSV File ? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Wed Jul 05, 2006 8:15 am Post subject: |
|
|
mfuser,
Open an excel file and put 10 rows with values in 3 or 4 columns and click on FILE and then select "Save as" and in the pop up screen open the drop down box under " save as type" and select CSV (Comma delimited *.csv) .
Now open this file using NOTEPAD and you will see how the data looks like.
As for the use Some of the applications receive data from a third party in excel file and they use the csv or tab delimited format. You upload these files to the mainframe which will be easier to read and format it.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
neilxt
Beginner
Joined: 01 Mar 2004
Posts: 23
Topics: 1
|
| Posted: Tue Sep 19, 2006 3:20 pm Post subject: Re: what is a CSV File |
|
|
| mfuser wrote: | Members,
I would like to know what is a CSV File and is there any difference in using UNSTRING for a normal string and a string from a CSV File.Can any body help me out with the link to CSV File ? |
It's an old post so probably far too late to help but for future ref...
Comma Separated Values - Might look like ...
1,123, 123.45, 'qbc','last, first',,,2
UNSTRING MIGHT work, providing there are no character fields that might contain an internal comma and even then you're going to have to strip the extraneous quotes off the character fields.
There's no simple function for unstringing a CSV OTOH it's not that hard a problem to code around especially using string slicing. |
|
| Back to top |
|
|
Jamylady
Beginner
Joined: 04 Nov 2004
Posts: 68
Topics: 22
|
| Posted: Wed Oct 18, 2006 7:43 am Post subject: |
|
|
I did a file transfer from Mainframe to PC and it was done successfully. (as a .TXT tab delimitted). But when I opened that in Excel, I am losing the first zeros.
For example, first column in my file is TIN number and after opening that file to in excel, TIN '000123456' become '123456' and become numeric. Is there any way not to miss the zeros ?
Thanks
Jimmy |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Wed Oct 18, 2006 7:56 am Post subject: |
|
|
| Select the properties for the column, then select custom from the number tab and enter a type of 000000000. |
|
| Back to top |
|
|
Jamylady
Beginner
Joined: 04 Nov 2004
Posts: 68
Topics: 22
|
| Posted: Mon Jun 18, 2007 5:36 am Post subject: |
|
|
Hi,
I have the following data in a mainframe file.
| Code: |
RO07.20070108162110000001.001
RO07.20070108162110000002.001
RO07.20070108162110000003.001
RO07.20070108162110000004.001
RO07.20070108162110000005.001
RO07.20070108162110000006.001
RO07.20070108162110000007.001
RO07.20070108162110000008.001
RO07.20070108162110000009.001
RO07.20070108162110000010.001
RO07.20070108162110000011.001
RO07.20070108162110000012.001
RO07.20070108162110000013.001
RO07.20070108162110000014.001
RO07.20070108162110000015.001
RO07.20070108162110000016.001
RO07.20070108162110000017.001
|
It is a tab delimted file (x'05) and that is denoted by the '.' in the above example.
I did a file transfer to PC and opend it in excel. Every thing is working fine except that the 2nd column in the above example is showing as 2.00701E+19 in the excel sheet. I tried formating the cell to number with no 0 decimal places and then it convert the column to '20070108162110000000' which is not correct. It should have been '20070108162110000001'. So no matter what value is in colum 2, it converts it in to '20070108162110000000' always. But I noticed that if I keyed in all the value directly in the column, Excel is displaying it normally.
Again I tried to format it as text, but it didnt change the format. It displayed as '2.00701E+19 '.
Now my question is
1) Is there any way to specify how to format a column while creating the mainframe file(Just like I am inserting a x'05' char in the file so that it become a tab deleimited file.) Similary is there any way to instruct excel to treat it as a text filed?. This is an automation process and I would like not to have any manual interaction to do this formating.
2) If that is not possible, how can I get rid of the above formatting issue?
Much appreciated
Thanks
JA |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Mon Jun 18, 2007 7:01 am Post subject: |
|
|
Jamylady,
CSV files are really just plain text files. Try opening the CSV file with Notepad or wordpad or some other plain text editor, then you will see that the data is there with the right format without scientific notation. But if you open with EXCEL then it is converted to scientific format. You have 2 options.
1. Enclose the columns you want the leading zeroes in double quotes with an equal sign infront of it and it will treat the column as text column and retain the leading zeroes
| Code: |
RO07.="20070108162110000001".="001"
|
will make the excel show it as
| Code: |
RO07 20070108162110000001 001
|
2. Do an import of this CSV file rather than directly opening it. Make the "Column data format" for the fields with leading zeroes as "TEXT". This will retain the leading zeroes.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Jamylady
Beginner
Joined: 04 Nov 2004
Posts: 68
Topics: 22
|
| Posted: Mon Jun 18, 2007 7:45 am Post subject: |
|
|
Hi kolusu,
| Quote: |
1. Enclose the columns you want the leading zeroes in double quotes with an equal sign infront of it and it will treat the column as text column and retain the leading zeroes
|
My problem is to keep the value as text. I tried the first method and looks like I am still getting the same isse. Am I missing some thing?
Following is the record in mainframe file which I am converting to .xls
| Code: | | RO07."=20070108162110000001"."=001"."=080". |
after conversion, it still the loosing the leading zeros and converting the second column to scientific notation
| Code: | | RO07 2.00701E+19 1 80 |
Also my file is not comma delimited, it is a tab delimitted file.
The second method is working. Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Mon Jun 18, 2007 7:48 am Post subject: |
|
|
| Quote: |
My problem is to keep the value as text. I tried the first method and looks like I am still getting the same isse. Am I missing some thing?
Following is the record in mainframe file which I am converting to .xls
Code:
RO07."=20070108162110000001"."=001"."=080".
|
Jamylady,
Pay attention to my post. Look clearly as to where the = sign is . The = sign should be before the quotes.
| Quote: |
Also my file is not comma delimited, it is a tab delimitted file.
|
It doesn't matter if it is a tab delimited or comma delimited.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Jamylady
Beginner
Joined: 04 Nov 2004
Posts: 68
Topics: 22
|
| Posted: Mon Jun 18, 2007 7:55 am Post subject: |
|
|
Sorry, I guess I did a mistake.
It should have been ="20070108162110000001"
Now it is working. Thank you very much
JA |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
