Mysql -> Perl - MS-Excel ?

Discussion in 'Perl Misc' started by me, Nov 25, 2008.

  1. me

    me Guest

    A customer of mine wants to create an excel spreadsheet directly form
    a MYSQL database. From the poking around I did, it seems like I would
    need to have the package Spreadsheet::WriteExcel installed.

    Are there any other alternative packages/options, or is that the only
    choice?

    Thanks,
    me, Nov 25, 2008
    #1
    1. Advertising

  2. me

    Ted Zlatanov Guest

    On Tue, 25 Nov 2008 13:31:04 -0500 me <> wrote:

    m> A customer of mine wants to create an excel spreadsheet directly form
    m> a MYSQL database. From the poking around I did, it seems like I would
    m> need to have the package Spreadsheet::WriteExcel installed.

    m> Are there any other alternative packages/options, or is that the only
    m> choice?

    It's even easier to export to CSV from a query, you never have to touch
    Perl or any other glue. Of course, if you have to massage the data
    that's another issue.

    Ted
    Ted Zlatanov, Nov 25, 2008
    #2
    1. Advertising

  3. me

    me Guest

    On Tue, 25 Nov 2008 15:08:09 -0600, Ted Zlatanov <>
    wrote:

    >
    >It's even easier to export to CSV from a query, you never have to touch
    >Perl or any other glue. Of course, if you have to massage the data
    >that's another issue.
    >
    >Ted


    Unfortunately this client wants a pure Excel file - no csv import...
    so I'm stuck with a specific MS-Excel output. I agree, csv is the
    better way to go.
    me, Nov 26, 2008
    #3
  4. me

    Dr.Ruud Guest

    me schreef:

    > Unfortunately this client wants a pure Excel file - no csv import...
    > so I'm stuck with a specific MS-Excel output. I agree, csv is the
    > better way to go.


    If you don't need any special formatting,
    and you output dates as "yyyy-mm-dd HH:MM:SS" strings,
    then just save the resulting CSV (with proper headers) under an xls-name
    like data.xls,
    and most users won't even notice that the file isn't a native Excel
    file.

    --
    Affijn, Ruud

    "Gewoon is een tijger."
    Dr.Ruud, Nov 26, 2008
    #4
  5. me

    Dr.Ruud Guest

    Dr.Ruud schreef:
    > me schreef:


    >> Unfortunately this client wants a pure Excel file - no csv import...
    >> so I'm stuck with a specific MS-Excel output. I agree, csv is the
    >> better way to go.

    >
    > If you don't need any special formatting,
    > and you output dates as "yyyy-mm-dd HH:MM:SS" strings,
    > then just save the resulting CSV (with proper headers) under an
    > xls-name like data.xls,
    > and most users won't even notice that the file isn't a native Excel
    > file.


    More tips:

    Make the CSV tab separated (and without any tabs inside the data of
    course).


    If you need a bit more formating (like bold etc.), then create a very
    basic html-file (and save it as data.xls again).

    <html>
    <body>
    <tr><td>date</td><td>value</td><td>location</td></tr>
    <tr><td>2008-12-25</td><td>XXXL-mas</td><td><u><b>Texas!!!<b></u></td></
    tr>
    </body>
    </html>


    The mysql-client can create a simple html-file from any query:

    mysql database -He'SELECT * FROM Table LIMIT 5' >data.xls

    --
    Affijn, Ruud

    "Gewoon is een tijger."
    Dr.Ruud, Nov 26, 2008
    #5
  6. On 2008-11-26 18:54, Dr.Ruud <> wrote:
    > me schreef:
    >> Unfortunately this client wants a pure Excel file - no csv import...
    >> so I'm stuck with a specific MS-Excel output. I agree, csv is the
    >> better way to go.

    >
    > If you don't need any special formatting,
    > and you output dates as "yyyy-mm-dd HH:MM:SS" strings,
    > then just save the resulting CSV (with proper headers) under an xls-name
    > like data.xls,
    > and most users won't even notice that the file isn't a native Excel
    > file.


    Be careful, though:

    * CSV import depends on the locale. You have to get the decimal
    character ("." or ",") right.

    * If something looks like a number, it is treated as a number. So
    if you have strings like "012.890" or "123E5", they will be converted
    to "12.89" and "12300000" respectively.

    * And sometimes the results are just weird, like a number being
    recognized as a date ...

    CSV is nice and simple, but Excel just isn't very good at importing it.
    It the goal is to get the data into Excel, it is much preferable to
    create an Excel file in the first place.

    hp
    Peter J. Holzer, Nov 29, 2008
    #6
  7. me

    Guest

    Actually, there is (yet) another path. Ready for the big secret.. ?
    Excel can
    read from SQL directly ! Yes, you can actually do this directly with a
    few lines of VBA scripting,
    some ODBC connection and a button press.

    This has the added bonus that Excel can (somewhat itelligently) read
    the data format from the database,
    so it will get the dates, numbers, decimals etc right.

    And, of course, that you can make it dynamic and make an online
    selection of what you need, to create different
    data each time.

    This should guide you:
    http://www.dicks-clicks.com/excel/ExternalData.htm
    , Dec 4, 2008
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JL
    Replies:
    0
    Views:
    1,132
  2. Ravi
    Replies:
    6
    Views:
    1,393
    Suchandra Thapa
    Jul 21, 2003
  3. Replies:
    2
    Views:
    6,170
  4. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,481
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  5. Replies:
    1
    Views:
    281
    Mirco Wahab
    Apr 17, 2007
Loading...

Share This Page