EXT: Alat Systems SQL to CSV Exporter¶

What does it do?¶

The extension allows you to create a secure SQL query and to export the data in a CSV file. We know, that there is a lot of typo3 core tools and other extensions with the same function. But the difference to this extension is the ability of the usage from:

• joins like: LEFT OUTER JOIN or INNER JOIN
• functions like: COUNT(), SUM() or CONCAT()
• user groups: only selected backend user groups can download a CSV file, it is secure
• column titles: use the AS statement in your SQL query, to get the column titles you need

Example queries¶

You can find example queries here: http://www.alatsystems.de/service/typo3/ass ql2csv /

Screenshots¶

• Illustration 1: List for Administrators

  

• Illustration 2: Create a new query

  

• Illustration 3: View for backend users to export results as CSV file

  

Install the Extension¶

Install the assql2csv extension using the Extension Manager Module. You can get the extension file by clicking on the “Import extensions” in the Menu drop down list, and then either do a look up for assql2csv or click on Retrieve/Update to look for it in the whole list of extensions.

After the installation you can change some settings.

• The number of record on one page in the reports view. The data of the query can be shown in a table. If the query have to much records, a drop-down page browser is visible to show on every page this number of records.
• The character to separate fields.
• This character will be used instead of the field separator in data to have a compatible csv file. Further the csv file replaces all newlines with a space.
• Don't forget to click on update, to save your changes.

Generating a SQL query¶

You can only generate a SELECT query in the standard query language SQL and no INSERTs or UPDATEs. The extension uses the function exec_SELECTquery from the Typo3 core. In this way, if Typo3 is secure, this extension is also secure by using the power of Typo3.

By generating a query you have to use different fields for parts in your query. Let's have a look on this by an example. If you want to get a list of all frontend users with their name and email in two different columns you have to create following SQL query:

SELECT name, email FROM fe_users

But if you want to have other column titles just use the AS statement from SQL:

SELECT name AS thename, email AS E-Mail FROM fe_users

You have to know, that the main statements like SELECT, FROM, WHERE are not changeable. To create your query you have to divide it in different parts.

• SELECT-PART: name AS thename, email AS E-Mail
• FROM-PART: fe_users

In this way you can create a query by putting the parts in the right fields.

Further you can select backend user groups, that are able to access the query for further export in a CSV-file.

Sample SQL query¶

SELECT f.username, f.last_name, f.first_name, f.email, f.address AS street, f.zip, f.city, IFNULL(GROUP_CONCAT( g.title ORDER BY g.title ASC SEPARATOR ';' ),'') AS usergroups FROM fe_users f LEFT OUTER JOIN fe_groups g ON FIND_IN_SET(g.uid,f.usergroup) WHERE f.deleted = 0 GROUP BY f.uid ORDER BY f.last_name ASC

Export a query¶

To export a generated query just select the CSV reports menu in the user menu. If you have enough rights to export a query, you can see the query in the list. By clicking on show query you see a table with the data to export. By clicking export as CSV file or the CSV icon the data gets collected to being exported as a CSV file. The CSV file don't gets saved, it gets generated on the fly.

1.4.1¶

1.4.0¶

1.3.0¶

1.2.0¶

1.1.0¶

1.0.0¶

0.1.0¶

10