Solved

Creating CSV file from TABLE HTML

Posted on 1998-11-12
5
366 Views
Last Modified: 2008-02-26
I want to write a PERL CGI that will:
(1) Receive data from an SQL SELECT Result Set and create an HTML Table filled with the data returned.
(2)  When a button labeled 'DOWNLOAD' is selected (will be created above the table in the HTML)I want to create
a CSV file on the users PC.

Can you help?
0
Comment
Question by:akeller
  • 3
  • 2
5 Comments
 

Expert Comment

by:JFM
ID: 1206186
just pasting the answer from the question 'Database' (a little bit modified):

assuming your platform is win32:
You need to do two things:

   1.  Install ODBC driver (for the database you're using) if you haven't already:
           should be found at http://www.microsoft.com
   2.  Download and install the Win32::ODBC module from the following website:
           http://www.roth.net/odbc/

Once you have done this, create an ODBC connection for your database and interface with it using the perl module.  Installation and usage instructions are available on the page mentioned above (along with a test/demo database in the download package.)

2nd question:
I can't see any solution for that problem. A CGI doesn't have any rigths to work on client's side.
Though, it would be possible to open a 'Save As' Window  and download the file to the desired place in the file system.
0
 

Author Comment

by:akeller
ID: 1206187
I want to read through the SQL Result set buffer creating a file which I would save to the server to allow download of the file to the pc by the customer.  

The question is was (my odbc is already setup) more explicityly, 'What would the loop code in the cgi script look like if I was looping through my SQL result set with to produce a file'.  The customer doesn't just want the data on their screen, they also want a file (CSV) for downloading, so I was looking for an example of that kind of a perl script solution.

Question points uped, please help ... I am new to cgi development.

0
 

Expert Comment

by:JFM
ID: 1206188
still not sure, if I understood the question, but i try:
Have you already installed the  Win32::ODBC module?

this code creates a html table from query results:

use Win32::ODBC;

$DSN = "MyDSN";
$UID = "akeller";
$PWD = "";

$db = new Win32::ODBC("DSN=$DSN;UID=$UID;PWD=$PWD;") || die "couldn't open connection";

$stmt = "SELECT * FROM Foo";
$rc = $db->Sql($stmt);
die qq(SQL failed "$stmt": ), $db->Error(), qq(\n) if $rc;

$table = "<TABLE>\n";
while ($db->FetchRow()) {
      $table .= "<TR>";
      # get all columns, you can fetch them by name, either: $db->Data("columnName");
      @values = $db->Data;
      foreach (@values) {
            $table .= "<TD>$_</TD>";
      }
      $table .= "</TR>\n";
}
$table .="</TABLE>\n";

$db->Close();

print $table;

 



0
 

Author Comment

by:akeller
ID: 1206189
Good Answer from JFM.  Thanks.  Please have JFM lock this question.

0
 

Accepted Solution

by:
JFM earned 80 total points
ID: 1206190
pasting the answer again from my last comment.

install the  Win32::ODBC module

     this code creates a html table from query results:

     use Win32::ODBC;

     $DSN = "MyDSN";
     $UID = "akeller";
     $PWD = "";

     $db = new Win32::ODBC("DSN=$DSN;UID=$UID;PWD=$PWD;") || die "couldn't
     open connection";

     $stmt = "SELECT * FROM Foo";
     $rc = $db->Sql($stmt);
     die qq(SQL failed "$stmt": ), $db->Error(), qq(\n) if $rc;

     $table = "<TABLE>\n";
     while ($db->FetchRow()) {
     $table .= "<TR>";
     # get all columns, you can fetch them by name, either: $db->Data("columnName");
     @values = $db->Data;
     foreach (@values) {
     $table .= "<TD>$_</TD>";
     }
     $table .= "</TR>\n";
     }
     $table .="</TABLE>\n";

     $db->Close();

     print $table;
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now