Export data to Excel

Here's a handy way to export your data to a text or Excel file

numero = 174
interpreted = N
texte = How often are you asked to pull data out of your website? It only takes a small bit of code to make this happen. There are two modules here; one to export the entire database, and another to export a custom set of fields and records. Note: Usually things like this belong behind an admin password. Basically, we do a search and display the [founditems] loop inside a [writefile]. By telling [writefile] to name the file with an .xls extension, the file will open into an Excel worksheet. It's really just a tab delimited text file with the .xls extension. Feel free to change the extension to .txt instead, but I find that clients understand an Excel file more often a text file. Note: Replace tab with an actual tab, and return with an actual return. In Writefile, returns and tabs matter. Line breaks shown here for readabilty, but in your actual code, tighten things up, leaving only the one return necessary to give each record its line break. Full Database Export You will set a variable with the database name/location. Then the rest of the code works automatically with the aid of [listfields].
[text]db=YourDatabaseName.db[/text][showif [exportfulldata]=t][writefile secure=f&file=FullData_[date %m%d%y].xls][listfields db=[db]][fieldname]tab[showif [index]=1][text]firstfield=[fieldname][/text][/showif][/listfields]return[search db=[db]&NE[firstfield]datarq=[blank]&Rank=off][founditems][listfields db=[db]][interpret][[fieldname]][/interpret]tab[/listfields]return[/founditems][/search][/writefile]Here is your export:<a href="FullData_[date %m%d%y].xls">FullData_[date %m%d%y].xls</a> | [/showif]<a href="[thisurl]?exportfulldata=t">Export Full Data</a>
Custom Database Export Often, you'll want to provide an export for the client, and your database may contain fields that are not relevant, or have fieldnames that are not user-friendly, or contain passwords. You will also probably want to sort in a specific manner, and extract only a subset of records. This is an example using a membership database. Instead of exporting the MemberID field, I leave a blank header for the first column, and simply let [index] provide a running count.

Edit this to match whatever records, sorting, fields, and output name you wish.

[showif [exportdata]=t][writefile secure=f&file=YourData_[date %m%d%y].xls][search db=YourDatabase.db&neMemberIDdatarq=[blank]&lnamesort=1&fnamesort=2]tabFirst NametabLast NametabEmailtabPhonetabCelltabFaxtabAddress1tabAddress2tabCitytabStatetabZipreturn[founditems][index]tab[fname]tab[lname]tab[email]tab[Phone]tab[Cell]tab[Fax]tab[addr1]tab[addr2]tab[City]tab[State]tab[Zip]return[/founditems][/search][/writefile]Here is your export:<a href="YourData_[date %m%d%y].xls">YourData_[date %m%d%y].xls</a> | [/showif]<a href="[thisurl]?exportdata=t">Export Custom Data</a>
How often are you asked to pull data out of your website? It only takes a small bit of code to make this happen. There are two modules here; one to export the entire database, and another to export a custom set of fields and records. Note: Usually things like this belong behind an admin password.

Basically, we do a search and display the [founditems] loop inside a [writefile]. By telling [writefile] to name the file with an .xls extension, the file will open into an Excel worksheet. It's really just a tab delimited text file with the .xls extension. Feel free to change the extension to .txt instead, but I find that clients understand an Excel file more often a text file. Note: Replace tab with an actual tab, and return with an actual return. In Writefile, returns and tabs matter. Line breaks shown here for readabilty, but in your actual code, tighten things up, leaving only the one return necessary to give each record its line break.

Full Database Export
You will set a variable with the database name/location. Then the rest of the code works automatically with the aid of [listfields].

[text]db=YourDatabaseName.db[/text]
[showif [exportfulldata]=t]
[writefile secure=f&file=FullData_[date %m%d%y].xls]
[listfields db=[db]][fieldname]tab[showif [index]=1][text]firstfield=[fieldname][/text][/showif][/listfields]return
[search db=[db]&NE[firstfield]datarq=[blank]&Rank=off]
[founditems]
[listfields db=[db]][interpret][[fieldname]][/interpret]tab[/listfields]return
[/founditems]
[/search]
[/writefile]
Here is your export:

<a href="FullData_[date %m%d%y].xls">FullData_[date %m%d%y].xls</a> | [/showif]<br /><a href="<a target="_parent" href="page.dna?numero=194">[thisurl]</a>?exportfulldata=t">Export Full Data</a>

Custom Database Export
Often, you'll want to provide an export for the client, and your database may contain fields that are not relevant, or have fieldnames that are not user-friendly, or contain passwords. You will also probably want to sort in a specific manner, and extract only a subset of records. This is an example using a membership database. Instead of exporting the MemberID field, I leave a blank header for the first column, and simply let [index] provide a running count.

Edit this to match whatever records, sorting, fields, and output name you wish.


[showif [exportdata]=t]
[writefile secure=f&file=YourData_[date %m%d%y].xls]
[search db=YourDatabase.db&neMemberIDdatarq=[blank]&lnamesort=1&fnamesort=2]
tabFirst NametabLast NametabEmailtabPhonetabCelltabFaxtabAddress1tabAddress2tabCitytabStatetabZipreturn
[founditems]
[index]tab[fname]tab[lname]tab[email]tab[Phone]tab[Cell]tab[Fax]tab[addr1]tab[addr2]tab[City]tab[State]tab[Zip]return
[/founditems]
[/search]
[/writefile]
Here is your export:

<a href="YourData_[date %m%d%y].xls">YourData_[date %m%d%y].xls</a> | [/showif]<br /><a href="<a target="_parent" href="page.dna?numero=194">[thisurl]</a>?exportdata=t">Export Custom Data</a>

Terry Wilson

DOWNLOAD WEBDNA NOW!

Top Articles:

Tips and Tricks

A list of user-submitted tips ...

Download WebDNA Applications

WebDNA applications...

AWS Raw WebDNA LAMP-Plus WebServer

Amazon Web Services (AWS) README for Machine Image ID...

WebDNA Modules

A list of the currently available modules...

Technical Change History

This Technical Change History provides a reverse chronological list of WebDNA changes...

[biotype]

BioType is a behavioral biometrics WebDNA function based on ADGS research and development (from version 8...

Related Readings:

How many working days?

This small script finds the number of working days between two dates...

random password-generator code

Generate a random alpha-numeric string...

How old am I?

You want to express an age in years given the dob (mm/dd/yyyy) and today's date...

Export data to Excel

Here's a handy way to export your data to a text or Excel file...

Database backup

How to make a backup of your databases...

Setting a 30-minute Cookie

Configuring the expires time for a short-term cookie is tricky...