Advanced: Exporting a Recordset

Introduction

In this tutorial we will show you how to export your recordset to multiple formats using the Universal Data Exporter PHP.

How to do it

1.       Create a Recordset
Create a new page and go to Server Behaviors and choose Recordset (Query) from the + icon.

2.       Setting the Recordset options
You should already have an existing connection to choose from. Next, choose your table and which columns you want to include. We advise you to use an empty page with just the recordset and the server behaviour as other data won't be exported. You can link to this page to get your recordset as a download.

3.       Applying the Universal Data Exporter
Press the + in the Server Behaviors tab and select DMXzone -> Universal Data Exporter.



The Universal Data Exporter interface appears.



Leave the default Name that is used to identify the behavior.



Select the Recordset you want to use as your source.





Choose the Output format and file name. Enter the filename in the entry field or create a dynamic filename by pressing the  icon. If you select  icon, a new popup will appear. Select Data Sources and choose the field you want to link to.



You also have an option to Save Copy to the server and/or to Allow Download. Another field, under those two, asks you for the Save Location and you have the ability to choose where to save your copy to.

4.       Markup for CSV
Each output format has its own markup options. If you've chosen CSV as the Output format you'll get the following markup options;



Line Separator defines which characters are used for a line break (an enter), we'll leave it to \n. Value Separator sets the way the values are separated. We won't change this either. We will enable the Include Header checkbox to include the column names. We leave the Quote field checked to enable quotes (for example: "Richard"). We leave the Strip HTML Tags option enabled to remove HTML codes from the content (if there are any).


This results in the following output (after pressing ok, saving and previewing in the browser).

5.       Markup for Excel
If you've chosen Excel as the output format, you'll get the following markup options:



We check the Table Header checkbox to include the column names. We leave the Font at Helvetica, and the Size to 12, set the Color to white (#FFFFFF) and enable the Bold and Italic icons. We set the BGColor to blue (#0086be). We leave the Strip HTML Tags option enabled to remove HTML codes from the content (if there are any).


We leave the Font of the Table Cell at Helvetica, set the Size to 10, leave the Color at black (#000000) and leave the Bold and Italic icons disabled. We also check Show On All Pages to show the header on all pages.



We set the Row BGColor to light grey (#DEDEDE), this way each odd row is colored grey. We leave the Row Height at 10; this value sets the height of each row. Tip: if your row heights are higher than the value set at Row Height then you need to increase the Column width for the columns that have been wrapped because they contain too much data. We leave the Line Color to black (#000000); this sets the color of the lines around your cells.

We set the Line Width to 0.3; this is the thickness of the lines around your cells. We leave the Strip HTML Tags option enabled to remove HTML codes from the content (if there are any).




This results in the following output (after pressing ok, saving and previewing in the browser).

6.       Markup for PDF
If you've chosen PDF as the output format, you'll get the following markup options:



We check the Table Header checkbox to include the column names. We leave the Font at Helvetica, leave the Size to 12, set the Color to white (#FFFFFF) and enable the Bold and Italic icons. We set the BGColor to blue (#0086be). We also check Show On All Pages to show the header on all pages.




We leave the Font of the Table Cell at Helvetica, set the Size to 10, leave the Color at black (#000000) and leave the Bold and Italic icons disabled.



We set the Row BGColor to light grey (#DEDEDE); this way each odd row is colored grey. We leave the Row Height at 10; this value sets the height of each row. We leave the Strip HTML Tags option enabled to remove HTML codes from the content (if there are any). We leave the Line Color to black (#000000); this sets the color of the lines around your cells. We set the Line Width to 0.3; this is the thickness of the lines around your cells. We enable Page Numbers to display the numbers of the pages. Alignment of the page numbers to right. And we set the Orientation to Landscape to enable a layout that is suited for wide tables.



We enable the Title checkbox to give our PDF a name; we name our table "Car" in the Text entry field. We press the bold icon and align it to the centre. We leave the Font at Helvetica and set the Size to 16, we leave the Color to black (#000000). We leave the Strip HTML Tags option enabled to remove HTML codes from the content (if there are any).



This results in the following output (after pressing ok, saving and previewing in the browser).

7.       Markup for XML
If you've chosen XML as the output format, you'll get the following markup options:



We leave the Root name to xml and the Row name to row to apply the default xml formatting. We leave the Strip HTML Tags option enabled to remove HTML codes from the content (if there are any).



This results in the following output (after pressing ok, saving and previewing in the browser, opening the file in Dreamweaver and using CommandS -> Apply Source Formatting).

8. Advanced options - Column Markup
   You can remove table rows with the  icon and add them with  the icon
   
   
   
   We leave the Label name's to their default values, change them to alter the names of your columns. We set the Column width of profile to 85 as it contains large amounts of data that will result in very high rows. By increasing the width, the row height will be as specified in the Row Height field of the Main tab.
   
   
   
   
   You can align the column titles with the Header Align option, we leave it at center align. We set the Cell Align to center align to centre the value names.
   
   
   
   
   In PDF only, Cell VAlign pertains to the vertical alignment of the content in the cell. We set it to Top align.
   
   
   In PDF, you can also choose from 3 Column Types which are Text, Link, and Image. 'Text' exports all your data in that column in text format only; 'Link' exports the column and keeps its links; 'Image' exports the column as images (supports .jpg files only).
    
   
   Image Path, if specified,  is used to prefix the image filenames so the full path can be retrieved.
   
   Press OK to apply your settings.

9.       Save and done
Save your page and upload it to your server, and you are done! Go to your website and test it - you should be able to upload any file.