perl code to export data to excel

perl code to export data to excel

am 04.08.2011 11:03:19 von vinorex

--001636920a59969d2d04a9aa40ba
Content-Type: text/plain; charset=ISO-8859-1

Hi
i am a beginner in Perl i have a pdb file containing the X,Y,and Z
coordinates of atoms example given below. the data extends upto 1000 atoms
and its coordinates. I need a perl program to export these data into excel
worksheet into distinct columns and ROws.

ATOM 1 N GLY A 2 1.888 -8.251 -2.511 1.00 36.63
N
ATOM 2 CA GLY A 2 2.571 -8.428 -1.248 1.00 33.02
C
ATOM 3 C GLY A 2 2.586 -7.069 -0.589 1.00 30.43
C
ATOM 4 O GLY A 2 2.833 -6.107 -1.311 1.00 33.27
O
ATOM 5 N GLY A 3 2.302 -6.984 0.693 1.00 24.67
N
ATOM 6 CA GLY A 3 2.176 -5.723 1.348 1.00 18.88
C
ATOM 7 C GLY A 3 0.700 -5.426 1.526 1.00 16.58
C
ATOM 8 O GLY A 3 -0.187 -6.142 1.010 1.00 12.47
O
ATOM 9 N LEU A 4 0.494 -4.400 2.328 1.00 15.00
N
ATOM 10 CA LEU A 4 -0.835 -3.926 2.630 1.00 12.93
C
ATOM 11 C LEU A 4 -0.868 -2.457 2.294 1.00 11.77
C
ATOM 12 O LEU A 4 0.007 -1.688 2.694 1.00 12.22
O
ATOM 13 CB LEU A 4 -1.151 -4.122 4.110 1.00 13.01
C
ATOM 14 CG LEU A 4 -2.536 -3.681 4.562 1.00 12.36
C
ATOM 15 CD1 LEU A 4 -3.577 -4.568 3.912 1.00 13.37
C
ATOM 16 CD2 LEU A 4 -2.644 -3.766 6.052 1.00 12.77
C
ATOM 17 N GLN A 5 -1.864 -2.055 1.522 1.00 10.93
N
ATOM 18 CA GLN A 5 -2.101 -0.667 1.209 1.00 11.87
C
ATOM 19 C GLN A 5 -3.075 -0.147 2.264 1.00 12.25
C
ATOM 20 O GLN A 5 -4.200 -0.648 2.394 1.00 11.71
O
ATOM 21 CB GLN A 5 -2.697 -0.574 -0.199 1.00 12.69
C
ATOM 22 CG GLN A 5 -2.977 0.839 -0.692 1.00 18.30
C
ATOM 23 CD GLN A 5 -1.725 1.720 -0.785 1.00 23.25
C
ATOM 24 OE1 GLN A 5 -1.726 2.887 -0.412 1.00 29.61
O
ATOM 25 NE2 GLN A 5 -0.612 1.212 -1.270 1.00 25.58
N
ATOM 26 N VAL A 6 -2.637 0.814 3.049 1.00 10.73
N
ATOM 27 CA VAL A 6 -3.466 1.409 4.078 1.00 10.95
C
ATOM 28 C VAL A 6 -3.829 2.773 3.519 1.00 11.49
C
ATOM 29 O VAL A 6 -3.054 3.742 3.589 1.00 9.28
O
ATOM 30 CB VAL A 6 -2.688 1.567 5.433 1.00 13.22
C
ATOM 31 CG1 VAL A 6 -3.664 2.085 6.488 1.00 13.44
C
ATOM 32 CG2 VAL A 6 -2.109 0.228 5.926 1.00 11.70
C
ATOM 33 N LYS A 7 -5.047 2.846 2.984 1.00 10.74
N
ATOM 34 CA LYS A 7 -5.515 4.029 2.280 1.00 11.66
C
ATOM 35 C LYS A 7 -5.445 5.308 3.081 1.00 11.12
C
ATOM 36 O LYS A 7 -5.045 6.377 2.623 1.00 13.12
O
ATOM 37 CB LYS A 7 -6.965 3.858 1.829 1.00 15.50
C
ATOM 38 CG LYS A 7 -7.335 4.947 0.832 1.00 19.57
C
ATOM 39 CD LYS A 7 -8.820 5.172 0.874 1.00 30.36
C
ATOM 40 CE LYS A 7 -9.206 6.372 0.017 1.00 34.59
C
ATOM 41 NZ LYS A 7 -10.630 6.633 0.157 1.00 39.48
N
ATOM 42 N ASN A 8 -5.791 5.168 4.347 1.00 12.07
N
ATOM 43 CA ASN A 8 -5.912 6.310 5.227 1.00 12.81
C
ATOM 44 C ASN A 8 -4.602 6.946 5.628 1.00 12.09
C
ATOM 45 O ASN A 8 -4.583 8.068 6.119 1.00 14.21
O
ATOM 46 CB ASN A 8 -6.621 5.928 6.515 1.00 14.50
C
ATOM 47 CG ASN A 8 -8.000 5.398 6.243 1.00 11.94
C
ATOM 48 OD1 ASN A 8 -8.128 4.266 5.782 1.00 12.17
O
ATOM 49 ND2 ASN A 8 -9.008 6.195 6.513 1.00 14.60
N
ATOM 50 N PHE A 9 -3.511 6.229 5.464 1.00 10.86
N
ATOM 51 CA PHE A 9 -2.264 6.756 5.951 1.00 12.74
C
ATOM 52 C PHE A 9 -1.522 7.551 4.909 1.00 13.14
C
ATOM 53 O PHE A 9 -0.921 7.001 4.018 1.00 15.38
O
ATOM 54 CB PHE A 9 -1.417 5.568 6.457 1.00 14.18
C
ATOM 55 CG PHE A 9 -1.761 5.002 7.835 1.00 13.51
C
ATOM 56 CD1 PHE A 9 -2.871 5.453 8.570 1.00 12.16
C
ATOM 57 CD2 PHE A 9 -0.921 4.025 8.380 1.00 13.88
C
ATOM 58 CE1 PHE A 9 -3.107 4.934 9.847 1.00 13.66
C

--001636920a59969d2d04a9aa40ba--

Re: perl code to export data to excel

am 04.08.2011 15:01:18 von Emeka

--000e0cd24036a1559304a9ad93a2
Content-Type: text/plain; charset=ISO-8859-1

Vino,

First learn how to use Excel with Perl. The below might be useful, please
check them out.

http://support.microsoft.com/kb/214797

http://www.ibm.com/developerworks/linux/library/l-pexcel/

Emeka

On Thu, Aug 4, 2011 at 10:03 AM, VinoRex.E wrote:

> Hi
> i am a beginner in Perl i have a pdb file containing the X,Y,and Z
> coordinates of atoms example given below. the data extends upto 1000 atoms
> and its coordinates. I need a perl program to export these data into excel
> worksheet into distinct columns and ROws.
>
> ATOM 1 N GLY A 2 1.888 -8.251 -2.511 1.00 36.63
> N
> ATOM 2 CA GLY A 2 2.571 -8.428 -1.248 1.00 33.02
> C
> ATOM 3 C GLY A 2 2.586 -7.069 -0.589 1.00 30.43
> C
> ATOM 4 O GLY A 2 2.833 -6.107 -1.311 1.00 33.27
> O
> ATOM 5 N GLY A 3 2.302 -6.984 0.693 1.00 24.67
> N
> ATOM 6 CA GLY A 3 2.176 -5.723 1.348 1.00 18.88
> C
> ATOM 7 C GLY A 3 0.700 -5.426 1.526 1.00 16.58
> C
> ATOM 8 O GLY A 3 -0.187 -6.142 1.010 1.00 12.47
> O
> ATOM 9 N LEU A 4 0.494 -4.400 2.328 1.00 15.00
> N
> ATOM 10 CA LEU A 4 -0.835 -3.926 2.630 1.00 12.93
> C
> ATOM 11 C LEU A 4 -0.868 -2.457 2.294 1.00 11.77
> C
> ATOM 12 O LEU A 4 0.007 -1.688 2.694 1.00 12.22
> O
> ATOM 13 CB LEU A 4 -1.151 -4.122 4.110 1.00 13.01
> C
> ATOM 14 CG LEU A 4 -2.536 -3.681 4.562 1.00 12.36
> C
> ATOM 15 CD1 LEU A 4 -3.577 -4.568 3.912 1.00 13.37
> C
> ATOM 16 CD2 LEU A 4 -2.644 -3.766 6.052 1.00 12.77
> C
> ATOM 17 N GLN A 5 -1.864 -2.055 1.522 1.00 10.93
> N
> ATOM 18 CA GLN A 5 -2.101 -0.667 1.209 1.00 11.87
> C
> ATOM 19 C GLN A 5 -3.075 -0.147 2.264 1.00 12.25
> C
> ATOM 20 O GLN A 5 -4.200 -0.648 2.394 1.00 11.71
> O
> ATOM 21 CB GLN A 5 -2.697 -0.574 -0.199 1.00 12.69
> C
> ATOM 22 CG GLN A 5 -2.977 0.839 -0.692 1.00 18.30
> C
> ATOM 23 CD GLN A 5 -1.725 1.720 -0.785 1.00 23.25
> C
> ATOM 24 OE1 GLN A 5 -1.726 2.887 -0.412 1.00 29.61
> O
> ATOM 25 NE2 GLN A 5 -0.612 1.212 -1.270 1.00 25.58
> N
> ATOM 26 N VAL A 6 -2.637 0.814 3.049 1.00 10.73
> N
> ATOM 27 CA VAL A 6 -3.466 1.409 4.078 1.00 10.95
> C
> ATOM 28 C VAL A 6 -3.829 2.773 3.519 1.00 11.49
> C
> ATOM 29 O VAL A 6 -3.054 3.742 3.589 1.00 9.28
> O
> ATOM 30 CB VAL A 6 -2.688 1.567 5.433 1.00 13.22
> C
> ATOM 31 CG1 VAL A 6 -3.664 2.085 6.488 1.00 13.44
> C
> ATOM 32 CG2 VAL A 6 -2.109 0.228 5.926 1.00 11.70
> C
> ATOM 33 N LYS A 7 -5.047 2.846 2.984 1.00 10.74
> N
> ATOM 34 CA LYS A 7 -5.515 4.029 2.280 1.00 11.66
> C
> ATOM 35 C LYS A 7 -5.445 5.308 3.081 1.00 11.12
> C
> ATOM 36 O LYS A 7 -5.045 6.377 2.623 1.00 13.12
> O
> ATOM 37 CB LYS A 7 -6.965 3.858 1.829 1.00 15.50
> C
> ATOM 38 CG LYS A 7 -7.335 4.947 0.832 1.00 19.57
> C
> ATOM 39 CD LYS A 7 -8.820 5.172 0.874 1.00 30.36
> C
> ATOM 40 CE LYS A 7 -9.206 6.372 0.017 1.00 34.59
> C
> ATOM 41 NZ LYS A 7 -10.630 6.633 0.157 1.00 39.48
> N
> ATOM 42 N ASN A 8 -5.791 5.168 4.347 1.00 12.07
> N
> ATOM 43 CA ASN A 8 -5.912 6.310 5.227 1.00 12.81
> C
> ATOM 44 C ASN A 8 -4.602 6.946 5.628 1.00 12.09
> C
> ATOM 45 O ASN A 8 -4.583 8.068 6.119 1.00 14.21
> O
> ATOM 46 CB ASN A 8 -6.621 5.928 6.515 1.00 14.50
> C
> ATOM 47 CG ASN A 8 -8.000 5.398 6.243 1.00 11.94
> C
> ATOM 48 OD1 ASN A 8 -8.128 4.266 5.782 1.00 12.17
> O
> ATOM 49 ND2 ASN A 8 -9.008 6.195 6.513 1.00 14.60
> N
> ATOM 50 N PHE A 9 -3.511 6.229 5.464 1.00 10.86
> N
> ATOM 51 CA PHE A 9 -2.264 6.756 5.951 1.00 12.74
> C
> ATOM 52 C PHE A 9 -1.522 7.551 4.909 1.00 13.14
> C
> ATOM 53 O PHE A 9 -0.921 7.001 4.018 1.00 15.38
> O
> ATOM 54 CB PHE A 9 -1.417 5.568 6.457 1.00 14.18
> C
> ATOM 55 CG PHE A 9 -1.761 5.002 7.835 1.00 13.51
> C
> ATOM 56 CD1 PHE A 9 -2.871 5.453 8.570 1.00 12.16
> C
> ATOM 57 CD2 PHE A 9 -0.921 4.025 8.380 1.00 13.88
> C
> ATOM 58 CE1 PHE A 9 -3.107 4.934 9.847 1.00 13.66
> C
>



--
*Satajanus Nig. Ltd


*

--000e0cd24036a1559304a9ad93a2--

Re: perl code to export data to excel

am 04.08.2011 16:43:03 von Mohan L

--bcaec51b1e098610af04a9aeffe9
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Aug 4, 2011 at 6:31 PM, Emeka wrote:

> Vino,
>
> First learn how to use Excel with Perl. The below might be useful, please
> check them out.
>
> http://support.microsoft.com/kb/214797
>
> http://www.ibm.com/developerworks/linux/library/l-pexcel/
>
> Emeka
>
>
You can also learn here :

1). http://homepage.eircom.net/~jmcnamara/perl/WriteExcel.html

2).
http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib /Spreadsheet/WriteExcel/Examples.pm

3).
http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib /Spreadsheet/WriteExcel.pm

If you know very basis of perl, then you can use the example code in the
above link.

Thanks & Rg
Mohan L

--bcaec51b1e098610af04a9aeffe9--

Re: perl code to export data to excel

am 04.08.2011 16:46:43 von Shawn Wilson

--005045015feaac15da04a9af0c59
Content-Type: text/plain; charset=ISO-8859-1

On Aug 4, 2011 8:02 AM, "Emeka" wrote:
>
> Vino,
>
> First learn how to use Excel with Perl. The below might be useful, please
> check them out.
>
> http://support.microsoft.com/kb/214797
>
> http://www.ibm.com/developerworks/linux/library/l-pexcel/
>

That is (of course) a decent 'getting started'. I will note that as of this
year, Spreadsheet::WriteExcel has been superseded (same author) :
http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX-0.25/lib /Excel/Writer/XLSX.pm

This module is backward comparable, so the document is relevant but I'd also
suggest looking at the actual pod (esp the examples). This module has some
of the best documentation I've seen.

You'll also want Text::CSV_XS which has tons of examples all over the web.

If you look at these and fail, post your code and let us know. These are
pretty popular modules and you should get a quick response if you have
trouble - I for one use thesm on almost a daily basis.

> Emeka
>
> On Thu, Aug 4, 2011 at 10:03 AM, VinoRex.E wrote:
>
> > Hi
> > i am a beginner in Perl i have a pdb file containing the X,Y,and Z
> > coordinates of atoms example given below. the data extends upto 1000
atoms
> > and its coordinates. I need a perl program to export these data into
excel
> > worksheet into distinct columns and ROws.
> >
> > ATOM 1 N GLY A 2 1.888 -8.251 -2.511 1.00 36.63
> > N
> > ATOM 2 CA GLY A 2 2.571 -8.428 -1.248 1.00 33.02
> > C
> > ATOM 3 C GLY A 2 2.586 -7.069 -0.589 1.00 30.43
> > C
> > ATOM 4 O GLY A 2 2.833 -6.107 -1.311 1.00 33.27
> > O
> > ATOM 5 N GLY A 3 2.302 -6.984 0.693 1.00 24.67
> > N
> > ATOM 6 CA GLY A 3 2.176 -5.723 1.348 1.00 18.88
> > C
> > ATOM 7 C GLY A 3 0.700 -5.426 1.526 1.00 16.58
> > C
> > ATOM 8 O GLY A 3 -0.187 -6.142 1.010 1.00 12.47
> > O
> > ATOM 9 N LEU A 4 0.494 -4.400 2.328 1.00 15.00
> > N
> > ATOM 10 CA LEU A 4 -0.835 -3.926 2.630 1.00 12.93
> > C
> > ATOM 11 C LEU A 4 -0.868 -2.457 2.294 1.00 11.77
> > C
> > ATOM 12 O LEU A 4 0.007 -1.688 2.694 1.00 12.22
> > O
> > ATOM 13 CB LEU A 4 -1.151 -4.122 4.110 1.00 13.01
> > C
> > ATOM 14 CG LEU A 4 -2.536 -3.681 4.562 1.00 12.36
> > C
> > ATOM 15 CD1 LEU A 4 -3.577 -4.568 3.912 1.00 13.37
> > C
> > ATOM 16 CD2 LEU A 4 -2.644 -3.766 6.052 1.00 12.77
> > C
> > ATOM 17 N GLN A 5 -1.864 -2.055 1.522 1.00 10.93
> > N
> > ATOM 18 CA GLN A 5 -2.101 -0.667 1.209 1.00 11.87
> > C
> > ATOM 19 C GLN A 5 -3.075 -0.147 2.264 1.00 12.25
> > C
> > ATOM 20 O GLN A 5 -4.200 -0.648 2.394 1.00 11.71
> > O
> > ATOM 21 CB GLN A 5 -2.697 -0.574 -0.199 1.00 12.69
> > C
> > ATOM 22 CG GLN A 5 -2.977 0.839 -0.692 1.00 18.30
> > C
> > ATOM 23 CD GLN A 5 -1.725 1.720 -0.785 1.00 23.25
> > C
> > ATOM 24 OE1 GLN A 5 -1.726 2.887 -0.412 1.00 29.61
> > O
> > ATOM 25 NE2 GLN A 5 -0.612 1.212 -1.270 1.00 25.58
> > N
> > ATOM 26 N VAL A 6 -2.637 0.814 3.049 1.00 10.73
> > N
> > ATOM 27 CA VAL A 6 -3.466 1.409 4.078 1.00 10.95
> > C
> > ATOM 28 C VAL A 6 -3.829 2.773 3.519 1.00 11.49
> > C
> > ATOM 29 O VAL A 6 -3.054 3.742 3.589 1.00 9.28
> > O
> > ATOM 30 CB VAL A 6 -2.688 1.567 5.433 1.00 13.22
> > C
> > ATOM 31 CG1 VAL A 6 -3.664 2.085 6.488 1.00 13.44
> > C
> > ATOM 32 CG2 VAL A 6 -2.109 0.228 5.926 1.00 11.70
> > C
> > ATOM 33 N LYS A 7 -5.047 2.846 2.984 1.00 10.74
> > N
> > ATOM 34 CA LYS A 7 -5.515 4.029 2.280 1.00 11.66
> > C
> > ATOM 35 C LYS A 7 -5.445 5.308 3.081 1.00 11.12
> > C
> > ATOM 36 O LYS A 7 -5.045 6.377 2.623 1.00 13.12
> > O
> > ATOM 37 CB LYS A 7 -6.965 3.858 1.829 1.00 15.50
> > C
> > ATOM 38 CG LYS A 7 -7.335 4.947 0.832 1.00 19.57
> > C
> > ATOM 39 CD LYS A 7 -8.820 5.172 0.874 1.00 30.36
> > C
> > ATOM 40 CE LYS A 7 -9.206 6.372 0.017 1.00 34.59
> > C
> > ATOM 41 NZ LYS A 7 -10.630 6.633 0.157 1.00 39.48
> > N
> > ATOM 42 N ASN A 8 -5.791 5.168 4.347 1.00 12.07
> > N
> > ATOM 43 CA ASN A 8 -5.912 6.310 5.227 1.00 12.81
> > C
> > ATOM 44 C ASN A 8 -4.602 6.946 5.628 1.00 12.09
> > C
> > ATOM 45 O ASN A 8 -4.583 8.068 6.119 1.00 14.21
> > O
> > ATOM 46 CB ASN A 8 -6.621 5.928 6.515 1.00 14.50
> > C
> > ATOM 47 CG ASN A 8 -8.000 5.398 6.243 1.00 11.94
> > C
> > ATOM 48 OD1 ASN A 8 -8.128 4.266 5.782 1.00 12.17
> > O
> > ATOM 49 ND2 ASN A 8 -9.008 6.195 6.513 1.00 14.60
> > N
> > ATOM 50 N PHE A 9 -3.511 6.229 5.464 1.00 10.86
> > N
> > ATOM 51 CA PHE A 9 -2.264 6.756 5.951 1.00 12.74
> > C
> > ATOM 52 C PHE A 9 -1.522 7.551 4.909 1.00 13.14
> > C
> > ATOM 53 O PHE A 9 -0.921 7.001 4.018 1.00 15.38
> > O
> > ATOM 54 CB PHE A 9 -1.417 5.568 6.457 1.00 14.18
> > C
> > ATOM 55 CG PHE A 9 -1.761 5.002 7.835 1.00 13.51
> > C
> > ATOM 56 CD1 PHE A 9 -2.871 5.453 8.570 1.00 12.16
> > C
> > ATOM 57 CD2 PHE A 9 -0.921 4.025 8.380 1.00 13.88
> > C
> > ATOM 58 CE1 PHE A 9 -3.107 4.934 9.847 1.00 13.66
> > C
> >
>
>
>
> --
> *Satajanus Nig. Ltd
>
>
> *

--005045015feaac15da04a9af0c59--

Re: perl code to export data to excel

am 04.08.2011 17:51:21 von Mike McClain

On Thu, Aug 04, 2011 at 02:33:19PM +0530, VinoRex.E wrote:
> Hi
> i am a beginner in Perl i have a pdb file containing the X,Y,and Z
> coordinates of atoms example given below. the data extends upto 1000 atoms
> and its coordinates. I need a perl program to export these data into excel
> worksheet into distinct columns and ROws.
>
> ATOM 1 N GLY A 2 1.888 -8.251 -2.511 1.00 36.63
> N
> ATOM 2 CA GLY A 2 2.571 -8.428 -1.248 1.00 33.02
> C
> ATOM 3 C GLY A 2 2.586 -7.069 -0.589 1.00 30.43
> C

Here's one way:

#!/usr/bin/perl
# convert raw data file to *.csv which can be loaded by Excel

use strict; # this can be removed when all bugs are fixed
use warnings; # more control than -w

my ($str, @fields);

open my $fh, '<', 'data.pdb' or die "Unable to open DATA: $!";
while( my $line = <$fh> )
{ chomp $line;
@fields = split /\s+/, $line;
if( @fields == 1 )
{ $str .= "," . $line;
print $str, "\n";
}
else
{ $str = join ",", @fields;
}
}
close $fh;

Mike
--
Satisfied user of Linux since 1997.
O< ascii ribbon campaign - stop html mail - www.asciiribbon.org

--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/

Re: perl code to export data to excel

am 04.08.2011 17:57:59 von Uri Guttman

>>>>> "MM" == Mike McClain writes:

MM> use strict; # this can be removed when all bugs are fixed

that is not a good comment. you should keep strict on as it isn't about
finding bugs but making sure all variables are properly declared and
spelled correctly.

uri

--
Uri Guttman -- uri AT perlhunter DOT com --- http://www.perlhunter.com --
------------ Perl Developer Recruiting and Placement Services -------------
----- Perl Code Review, Architecture, Development, Training, Support -------

--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/