Skip to main content

SQL - Exporting

From the Embedded SQL Report window of a report, check the Allow export of data option at the bottom of the window. When selected, the Export option will appear in the menu bar when the SQL report is run.

SQL Report - Export

From this location, an Embedded SQL report can be exported one time or set to export in a specific format each time it is run.

General​

Export Type​

Select whether you would like the data to be exported As an Export File or As a Chart. When exporting the data as a chart, the chart preview appears with all the options for Displaying Data, allowing you to edit the display to your needs. When exporting as a file, the following parameters need to be completed.

Export File Parameters​

After entering a file name for the export, the drop down box gives options to Add System Date or Add Audit Date to the file name. These will add the respective dates to the file name each time it is exported. Select Do not add Date to only include a date in the file name.

By default, exported reports are saved in the Property Share directory.

If the Embedded report has been designated to allow exporting, you can export the data to four different formats:

  • CSV (Comma Separated or Excel)

  • XML (Extensive Mark-up Language or with Excel)

  • Text file (open in NotePad or Word)

  • HTML (open in a Web Browser)

To use an XML file in Microsoft Excel, simply open up Microsoft Excel and then drag and drop the XML file onto the spreadsheet and select the first open As an XML table.

Action After Exporting​

After an export mode has been selected and a name entered, you can choose what to do after exporting, including opening, emailing, or sending the file. See Internet FTP Settings for detailed information on FTP settings.

Once the file is created, it will remain on the system until it is deleted. If the file contains important or private information, you should make sure it is deleted when you done using it.

Options​

This is where you can enter the parameters applicable to the different exporting formats:

  • CSV - Enter the deliminator character to use and whether to include to the column titles. By default, all fields will be quoted using ", even when numeric. You can select Ignore Quoting Numeric fields if you wish for fields that are numeric are not quoted. By default, all numeric fields are quoted as the system does not know if a number is a phone number, a Postal Code or money.

  • XML - Enter the labels for both the Data and Row elements. Select EasyMail - Include Meta Data to format the message to use the import function in G-Lock's EasyMail.

  • Text File - Enter the Deliminator ASCII Value.

If you wish to export the file on a regular basis either as a report, fill out the fields on the Export window and then select the Create Export Specification button on the Options tab to save the Export information. Once saved, you can then manually add the Export Specification directly to the SQL statement so it is automatically loaded/used each time the query is executed.

Example: The following will execute the query and then load the Export Specification called myexport.qwe in the current folder.

SELECT * FROM ACCOUNTS

 EXPORT AS FILE USING 'MYEXPORT.QWE'

The report will automatically be exported using the parameters saved in myexport.qwe each time it is run.

To export a file on a regular basis as a chart or graph, manually add the Export Specification directly to the SQL statement so it is automatically loaded/used each time the query is executed. The format to use when exporting as a chart or graph is:

EXPORT AS GRAPH USING ‘PIE3D’

The valid chart types available are BAR, STACKEDBAR, LINE, STEPLINE, PYRAMID, FUNNEL, PIE, and PIE3D, the same as those available in the Displaying Data section on a chart. Click here for more information on writing SQL Charts.