.. You may want to use the usual include line. Uncomment and adjust the path. .. include:: ../Includes.txt ===================================== EXT: Alat Systems SQL to CSV Exporter ===================================== :Created: 2010-02-18T17:33:18 :Changed by: Muhammed Alat :Changed: 2012-09-30T19:18:43 :Classification: assql2csv :Description: The keywords help with categorizing and tagging of the manuals. You can combine two or more keywords and add additional keywords yourself. Please use at least one keyword from both lists. If your manual is NOT in english, see next tab "language" ---- forEditors (use this for editors / german "Redakteure") forAdmins (use this for Administrators) forDevelopers (use this for Developers) forBeginners (manuals covering TYPO3 basics) forIntermediates (manuals going into more depth) forAdvanced (covering the most advanced TYPO3 topics) see more: http://wiki.typo3.org/doc_template#tags ---- :Keywords: sql csv exporter table database :Author: Muhammed Alat :Email: info@alatsystems.de :Info 4: :Language: en |img-1| |img-2| EXT: Alat Systems SQL to CSV Exporter - assql2csv .. _EXT-Alat-Systems-SQL-to-CSV-Exporter: EXT: Alat Systems SQL to CSV Exporter ===================================== Extension Key: assql2csv Language: en Keywords: sql csv exporter table database Copyright 2000-2011, Muhammed Alat, This document is published under the Open Content License available from http://www.opencontent.org/opl.shtml The content of this document is related to TYPO3 \- a GNU/GPL CMS/Framework available from www.typo3.org .. _Table-of-Contents: Table of Contents ----------------- `EXT: Alat Systems SQL to CSV Exporter 1 <#__RefHeading__2508_1269216588>`_ **`Introduction 3 <#__RefHeading__2510_1269216588>`_** `What does it do? 3 <#__RefHeading__2512_1269216588>`_ `Example queries 3 <#__RefHeading__618_319701970>`_ `Screenshots 3 <#__RefHeading__2514_1269216588>`_ **`Users manual and Installation 6 <#__RefHeading__875_612033961>`_** `Install the Extension 6 <#__RefHeading__1854_1453161689>`_ **`Usage 7 <#__RefHeading__1625_1453161689>`_** `Generating a SQL query 7 <#__RefHeading__2526_1269216588>`_ `Sample SQL query 7 <#__RefHeading__748_1921696900>`_ `Export a query 7 <#__RefHeading__2066_1453161689>`_ **`Known problems 8 <#__RefHeading__2532_1269216588>`_** **`To-Do list 9 <#__RefHeading__2534_1269216588>`_** **`ChangeLog 10 <#__RefHeading__2536_1269216588>`_** .. _Introduction: Introduction ------------ .. _What-does-it-do: 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: Example queries ^^^^^^^^^^^^^^^ You can find example queries here: `http://www.alatsystems.de/service/typo3/ass `_ `ql2csv `_ `/ `_ .. _Screenshots: Screenshots ^^^^^^^^^^^ - Illustration 1: List for Administrators |img-3| - Illustration 2: Create a new query |img-4| - Illustration 3: View for backend users to export results as CSV file |img-5| .. _Users-manual-and-Installation: Users manual and Installation ----------------------------- To install the assql2csv extension, you will need to do the following: Install the Extension like any other extension .. _Install-the-Extension: 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. |img-6| 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. .. _Usage: Usage ----- There are two different backend modules. The backend module in the tools menu can only be used from a backend administrator. This is the module to generate SQL queries for further export. The second backend module is in the user menu. You have to give a backend user the rights to access the module. To do so, you need to give a backend user or a backend user group the rights to access the user menu and the CSV export module in the user menu. Don't forget to select both rights for access for other backend users. .. _Generating-a-SQL-query: 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: 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 .. ### BEGIN~OF~TABLE ### .. _SELECT: SELECT """""" .. container:: table-row SQL Part SELECT Input 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: FROM """" .. container:: table-row SQL Part FROM Input fe\_users f LEFT OUTER JOIN fe\_groups g ON FIND\_IN\_SET(g.uid,f.usergroup) .. _WHERE: WHERE """"" .. container:: table-row SQL Part WHERE Input f.deleted = 0 .. _GROUP-BY: GROUP BY """""""" .. container:: table-row SQL Part GROUP BY Input f.uid .. _ORDER-BY: ORDER BY """""""" .. container:: table-row SQL Part ORDER BY Input f.username ASC .. _LIMIT: LIMIT """"" .. container:: table-row SQL Part LIMIT Input .. ###### END~OF~TABLE ###### The query above result to: .. ### BEGIN~OF~TABLE ### .. _user1: user1 """"" .. container:: table-row username user1 last\_name Soyad first\_name Ad email ist@typo street Street 1 zip 34000 city Istanbul usergroups Group1 .. _user2: user2 """"" .. container:: table-row username user2 last\_name Nachname first\_name Vorname email ber@typo street Street 2 zip 10000 city Berlin usergroups Group1, G2 .. _user3: user3 """"" .. container:: table-row username user3 last\_name Lastname first\_name Firstname email lon@typo street Street 3 zip WC city London usergroups G4,G6,G9 .. _: ... """ .. container:: table-row username ... last\_name ... first\_name ... email ... street ... zip ... city ... usergroups ... .. ###### END~OF~TABLE ###### .. _Export-a-query: 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. .. _Known-problems: Known problems -------------- Not so far. If you find any, just report them. We answer on Emails. Contact us: `http://www.alatsystems.de/kontakt/ `_ .. _To-Do-list: To-Do list ---------- More usability .. _ChangeLog: ChangeLog --------- .. ### BEGIN~OF~TABLE ### .. _1-4-1: 1.4.1 ^^^^^ .. container:: table-row Version 1.4.1 Changes Replacing deprecated TYPO3-API functions. Thanks to Gernot Brandner. Released on 30/09/2012 .. _1-4-0: 1.4.0 ^^^^^ .. container:: table-row Version 1.4.0 Changes Now supports correct Encoding for MS Excel (UTF-8 bug) Released on 16/11/2011 .. _1-3-0: 1.3.0 ^^^^^ .. container:: table-row Version 1.3.0 Changes Availability to choose the field separator. Replacing of newlines in CSV file. Released on 01/03/2011 .. _1-2-0: 1.2.0 ^^^^^ .. container:: table-row Version 1.2.0 Changes More usability. Released on 27/02/2011 .. _1-1-0: 1.1.0 ^^^^^ .. container:: table-row Version 1.1.0 Changes Show number of records. Check SQL queries. More Usability. Released on 24/02/2011 .. _1-0-0: 1.0.0 ^^^^^ .. container:: table-row Version 1.0.0 Changes The extension is stable. Released on 22/02/2011 .. _0-1-0: 0.1.0 ^^^^^ .. container:: table-row Version 0.1.0 Changes First usable version. Released on 03/02/2011 .. ###### END~OF~TABLE ###### |img-2| 10 .. ######CUTTER_MARK_IMAGES###### .. |img-1| image:: img-1.png .. :align: left .. |img-2| image:: img-2.png .. :border: 0 .. :height: 21 .. :hspace: 9 .. :id: Grafik2 .. :name: Grafik2 .. :width: 87 .. |img-3| image:: img-3.jpeg .. :align: left .. :border: 0 .. :height: 293 .. :id: Grafik1 .. :name: Grafik1 .. :width: 640 .. |img-4| image:: img-4.jpeg .. :align: left .. :border: 0 .. :height: 800 .. :id: Grafik3 .. :name: Grafik3 .. :width: 467 .. |img-5| image:: img-5.jpeg .. :align: left .. :border: 0 .. :height: 732 .. :id: Grafik4 .. :name: Grafik4 .. :width: 416 .. |img-6| image:: img-6.jpeg .. :align: left .. :border: 0 .. :height: 328 .. :id: Grafik5 .. :name: Grafik5 .. :width: 416