Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Exporting from a Database to Excel

Tips and Tricks

Exporting from a Database to Excel

by  Glowball  Posted    (Edited  )
You may want to generate an Excel spreadsheet on your Web site using data in your database. This is the easiest and most reliable way to do it.
[ol]
[li]Put together a SQL query that gets the data from your Web site database and puts it into the query results. For example:
Code: 
SELECT firstname, lastname, message FROM mytable
[/li]
[li]Using the programming language of your choice, build a CSV file (comma-separated values), putting the header row at the top. Output it to your Web browser's screen in the usual way, so you can verify the CSV. Unless you are absolutely sure that your field will be numeric you should enclose it with quotes. If you think that your field results might have quotes in it, change these to double quotes first. For example:
Code: 
"First Name","Last Name","Message"
"Bob","Smith","Hi there!"
"Jane","Van Wild","You are ""so"" cool"
[/li]
[li]Run the Web page and verify that your content looks like a CSV file (you'll have to view the page source)[/li]
[li]Above the output on your page and before any and all output, you'll need to tell the page that it needs to generate a CSV. It's also good practice to name the file that your user is downloading. This is done by sending header information. The exact function you use depends on the programming language you're using. Here is an example for PHP, with the month and year set earlier in the script:
Code: 
[navy]$filename[/navy] = "download_" . [navy]$year[/navy] . [green]sprintf[/green]("%02d", [navy]$month[/navy]) . ".csv";
[green]header[/green]("Content-Type: text/csv");
[green]header[/green]('Content-Disposition: attachment; filename="' . [navy]$filename[/navy] . '"');
[/li]
[li]Run the page again to verify that it works. You may need to hold your CTRL button down during the entire process to get it to work successfully.[/li]
[/ol]
I hope this helps someone!
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top