.. You may want to use the usual include line. Uncomment and adjust the path. .. include:: ../Includes.txt ==================================== EXT: Readable name of your extension ==================================== :Author: Kasper Skårhøj :Created: 2002-11-01T00:32:00 :Changed by: w x :Changed: 2018-06-27T18:29:09.950000000 :Classification: extensionkey :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: keywords comma-separated :Author: Author Name :Email: your@email.com :Info 4: :Language: en |img-1| |img-2| Dump SQL table/query to CSV -w\_query2csvDump SQL table/query to CSV .. _Dump-SQL-table-query-to-CSV: Dump SQL table/query to CSV =========================== Extension Key: w\_query2csv Language: en Keywords: csv, sql, dump, export, table, query, spreadsheet, excel, calc Copyright 2009-2018, wolo.pl '.' studio, `https://github.com/w010/w\_query2csv/ `_ 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 ----------------- `Dump SQL table/query to CSV 1 <#__RefHeading__756_431020924>`_ **`Introduction 3 <#__RefHeading__758_431020924>`_** `What is this? 3 <#__RefHeading__760_431020924>`_ `For what do I need it? 3 <#__RefHeading__762_431020924>`_ `Screenshots 3 <#__RefHeading__764_431020924>`_ **`How to use it? 4 <#__RefHeading__766_431020924>`_** `Basic 4 <#__RefHeading__768_431020924>`_ `Using in own plugins 5 <#__RefHeading__770_431020924>`_ **`Administration / configuration 6 <#__RefHeading__772_431020924>`_** `Reference / options description 6 <#__RefHeading__774_431020924>`_ **`Security 9 <#__RefHeading__1013_431020924>`_** `PLEASE REMEMBER 9 <#__RefHeading__1024_431020924>`_ **`FAQ 10 <#__RefHeading__782_431020924>`_** **`Migrating 11 <#__RefHeading__1034_431020924>`_** `Migrate from version 0.1 11 <#__RefHeading__1036_431020924>`_ **`Known problems 12 <#__RefHeading__784_431020924>`_** **`To-Do list 13 <#__RefHeading__786_431020924>`_** **`ChangeLog 14 <#__RefHeading__788_431020924>`_** .. _Introduction: Introduction ------------ .. _What-is-this: What is this? ^^^^^^^^^^^^^ This is extension for quick export SQL table or query/ies to CSV file/s. You may configure number of files which will be available, for every file using their own settings, select different table, fields which will be exported, standard parts of MySQL query, file charset, csv fields separator and more. All that is fast and easy, using included example TypoScript. You may also parse selected fields with own methods, like tstamp to human-readable date. .. _Please-see-Security-section-before-use: Please see “Security” section before use """""""""""""""""""""""""""""""""""""""" .. _For-what-do-I-need-it: For what do I need it? ^^^^^^^^^^^^^^^^^^^^^^ If you have data set in db, eg. some orders or contest answers, that must be quick sent to someone by email, or periodically downloaded by someone, or something like that, this extension is very useful. Just make new page, insert plugin and set up the file, that will be downloaded by other or specified users, which you may specify by standard TYPO3 access settings. It's simple and easy to configure, additionally you may process selected fields using some functions, eg. convert timestamps to human- readable strings. Want something more functional? It is a good base to develop something bigger. .. _Screenshots: Screenshots ^^^^^^^^^^^ |img-3| .. _How-to-use-it: How to use it? -------------- .. _Basic: Basic ^^^^^ Simply insert plugin on new page named eg. 'CSV export' and configure input and output using TypoScript. A Backend Section type page is good idea, you might control download access for backend non-admin users. To download file, you have to access url using ?f=[configuration\_key], as here: http://example.com/csv\_export.html *?f=somestuff\_orders* Note, that this plugin is not basing on standard TYPO3 page type handling, but is changing headers for whole page containing it, so do not insert it on standard frontend pages with normal content. But it's possible to make CSV output of any page uid using page type, if you use a snippet from Configuration/TypoScript/Setup/setup.ts Caution where do you insert this plugin and what is exported! |img-4| .. _Using-in-own-plugins: Using in own plugins ^^^^^^^^^^^^^^^^^^^^ One time I needed to display link to csv in my other plugin context. This needs to make query2csv object, prepare and pass config to it, and call when requested. This can be done like: .. _Example: Example: """""""" :: $content .= 'download file'; if ($_GET['action'] == 'getfile') { require_once(ExtensionManagementUtility::extPath('w_query2csv').'Classes/Plugins/Export.php'); $conf = [ 'debug_allowed' => 0, 'files.' => [ 'my_file.' => [ 'input.' => [ 'table' => 'tx_myext_table', 'fields' => 'uid, crdate, name, value', 'where' => 'AND type = 2', 'order' => 'uid DESC', 'limit' => 100, ], 'output.' => [ 'filename' => 'output.csv', 'process_fields.' => [ 'crdate' => 'WoloPl\WQuery2csv\Process->parseDate', ]]]]]; $Q2csv = GeneralUtility::makeInstance('tx_wquery2csv_export'); $Q2csv->main('', $conf); // note, that the script now throw output and exits! } .. _Administration-configuration: Administration / configuration ------------------------------ For selected page containing plugin, create new TypoScript template and edit Setup field. .. _generated: ((generated)) ^^^^^^^^^^^^^ .. _Example-config: Example config: """"""""""""""" :: plugin.tx_wquery2csv_export { files { my_file { input { table = tx_somestuff_orders fields = tstamp,name,email,phone,stuff_uid where = category = 2 group = order = tstamp DESC limit = enable_columns = 1 } output { filename = somestuff_orders.csv separator = , encoding = process_fields { tstamp = WoloPl\WQuery2csv\Process->parseDate } } } .... (some other keys with input and output options to access file using ?f=some_other_key) } debug_allowed = 1 } .. _Shortest-working-example: Shortest working example: """"""""""""""""""""""""" When you take a look at reference below, you'll see, that only db table is really required in configuration, so shortest config would look like this: :: plugin.tx_wquery2csv_export.files.my_file.input.table = tx_sometable .. _Explaining: Explaining: ~~~~~~~~~~~ file key “ **my\_file** ” has set input table to “ **tx\_sometable** ” so: the file will be accessed like: [download\_csv.html]?f= **my\_file** and: prompted do download as **my\_file.csv** .. _Reference-options-description: Reference / options description ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. _generated: ((generated)) """"""""""""" .. _plugin-tx-wquery2csv-export: plugin.tx\_wquery2csv\_export ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. ### BEGIN~OF~TABLE ### .. _files: files ''''' .. container:: table-row Property files Data type List of TypoScript properties Description (Required) Set of file keys to separate configs for misc files. Keynames are up to you. if you want only one file, the key may be whatever. If you don't set output.filename option, the filename will be like your key named here. Every key must have configured at least “ **input** ” section. “ **output** ” is not required, but has some useful options. Default .. _debug-allowed: debug\_allowed '''''''''''''' .. container:: table-row Property debug\_allowed Data type Boolean Description Allow to use &debug=1 url param. Default 0 .. _default-config-if-missed: default\_config\_if\_missed ''''''''''''''''''''''''''' .. container:: table-row Property default\_config\_if\_missed Data type Boolean Description Load '\_default' ts key config when config not found. Default 0 .. ###### END~OF~TABLE ###### .. _file-options: file options ~~~~~~~~~~~~ .. ### BEGIN~OF~TABLE ### .. _input: input ''''' .. container:: table-row Property input Data type List of TypoScript properties ->input Description (Required) Options for input data to render file from @see table below Default .. _output: output '''''' .. container:: table-row Property output Data type List of TypoScript properties ->output Description Miscellaneous options for output file @see table below Default .. _disable: disable ''''''' .. container:: table-row Property disable Data type Boolean Description Use to quick disable access to configured file without deleting or commenting ts file config setup Default 0 .. ###### END~OF~TABLE ###### .. _input: input ~~~~~ .. ### BEGIN~OF~TABLE ### .. _table: table ''''' .. container:: table-row Property table Data type List of TypoScript properties Description (Required) Options for input data to render file from Default .. _fields: fields '''''' .. container:: table-row Property fields Data type List of TypoScript properties Description Miscellaneous options for output file Default \* .. _where-group-order-limit: where, group, order, limit '''''''''''''''''''''''''' .. container:: table-row Property where, group, order, limit Data type String Description Standard parts of database query Default .. _where-wrap: where\_wrap ''''''''''' .. container:: table-row Property where\_wrap Data type TypoScript object Description Builds where clause using stdWrap and replaces 'where' if set. Use: where\_wrap = TEXT where\_wrap.value = pid = {page:uid} where\_wrap.stdWrap.insertData = 1 Default .. _enableFields: enableFields '''''''''''' .. container:: table-row Property enableFields Data type Boolean Description Use standard enableFields call to filter unavailable fields. Works only for TCA configured tables DEFAULT IS ENABLED (so if you has given empty csv, check that selected table has these fields and set this option to 0 if not) Default 1 .. _default-enableColumns: default\_enableColumns '''''''''''''''''''''' .. container:: table-row Property default\_enableColumns Data type Description deleted=0 AND hidden=0 in WHERE clause. Default .. ###### END~OF~TABLE ###### .. _output: output ~~~~~~ .. ### BEGIN~OF~TABLE ### .. _filename: filename '''''''' .. container:: table-row Property filename Data type String Description Output filename Default same as config file key with csv extension added .. _separator: separator ''''''''' .. container:: table-row Property separator Data type String Description CSV separator used in file Default , .. _charset: charset ''''''' .. container:: table-row Property charset Data type String Description Set alternative charset encoding Default UTF-8 .. _hsc: hsc ''' .. container:: table-row Property hsc Data type Boolean Description Htmlspecialchars every value Default 0 .. _no-header-row: no\_header\_row ''''''''''''''' .. container:: table-row Property no\_header\_row Data type Boolean Description Don't make first line header with fieldnames Default 0 .. _process-fields: process\_fields ''''''''''''''' .. container:: table-row Property process\_fields Data type List of TypoScript properties Description Set of TS properties like: field = process\_method field.[setting] = ... Parses selected db fields with plugin class method named as value. Example: :: tstamp = WoloPl\WQuery2csv\Process->parseDate tstamp.format = d.m.Y H:i Built-in processors reference: :: ->parseDate Parses value with date() function. Config: :: .format = [string] ->valueMap Maps input values, like some uid, to output values, like nice labels: :: .map { [array] source value = Export Value [in val = out val pairs] } ->staticValue Replaces original value with given – may be handy in some cases: :: .value = [string] ->unserialize .delimiter = [string] - may be anything, also you can use special keywords: -LINEBREAK- and -SPACE-. defaults to linebreak .lineBreakType = [string] - if use linebreak, may be configured to use CR, LF or CRLF. defaults to LF :: ->tableLabelsFromRecordsCommalist .table = [string] - table name to read referenced items from .field = [string] - use value from this field .delimiter = [string] - may be anything, also you can use special keywords: -LINEBREAK- and -SPACE-. defaults to linebreak .lineBreakType = [string] - may be CR, LF or CRLF. defaults to LF You can easily make own processors the same way Default .. _add-fields: add\_fields ''''''''''' .. container:: table-row Property add\_fields Data type String - commalist Description Create additional columns in output, that are not present in database. Set values for them in process\_fields. Default .. _remove-fields: remove\_fields '''''''''''''' .. container:: table-row Property remove\_fields Data type String - commalist Description Remove columns from output (values of which might be used for some processing, but they are not needed in csv) Default .. ###### END~OF~TABLE ###### The easiest way to quick configure is to just copy example config from above and edit options to your own. .. _Security: Security -------- .. _PLEASE-REMEMBER: PLEASE REMEMBER ^^^^^^^^^^^^^^^ This is a low-level database exporter, which was intended to use by admins to easy download customized csv with product orders. Such things are always potentially dangerous if used in wrong way. Downloading full database tables by people who are not permitted may be a disaster - passwords leak, session hijack, etc... - so better check twice where do you put this plugin and what have you configured there, to not allow downloading any sensitive data by mistake! I recommend always embeding it on pages with BE-user or admin access only, unless you are sure what are you doing. .. _FAQ: FAQ --- **Q: How to add others functions to process fields values?** A: Just make a class like w\_query2csv/Classes/Process.php and put it in your extension Classes directory. Define valid namespace and use as described above in process\_fields section (this is standard TYPO3's userfunc reference, instantiated by GeneralUtility). In this class you add public method with the name you chose, which expects 2 parameters: $params, $pObj. $params - array of keys:value (string)conf (array) – contains config for field, like format or maprow (array) – full row datafieldname (string)$pObj - reference to Core object, which sometimes may be useful. **Q: My output file is empty!** A: - try to set debug\_allowed = 1 in config and access file with &debug=1 to check if the config is passed properly. \- try to set "fields" to \*, comment other parts of db query \- check another table \- set "enableFields" to 0 (not just delete line!), maybe the table is not loaded in TCA **Q: What if BE-user has template edit privileges and exports something that he is not allowed?** **Why I cannot export my fe\_users?** A: Some tables would never be accessed even by other backend users. To prevent situation when a user configure plugin to see ie. users passwords, or allow a table which is originally blocked, set selected tables as comma separated list on "not\_allowed\_tables", in LocalConfiguration or AdditionalConfiguration :: $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['w_query2csv'] = [ 'not_allowed_tables' => 'be_users,be_groups,be_sessions,fe_users,fe_groups,fe_sessions,fe_session_data', ]; .. _Migrating: Migrating --------- .. _Migrate-from-version-0-1: Migrate from version 0.1 ^^^^^^^^^^^^^^^^^^^^^^^^ \- typoscript setup key is now: plugin.tx\_wquery2csv\_export instead of plugin.tx\_wquery2csv\_pi1 \- so the plugin content element embeded on page must be selected again and setup must be adjusted \- process\_fields now expects full userfunc reference \- so, \_process\_parseDate option is now: WoloPl\WQuery2csv\Process->parseDate \- process\_fields\_user is now removed, use process\_fields instead, just like the rest \- output.where now need to be full, not starting from "AND" .. _Known-problems: Known problems -------------- If you see any, let me know. .. _To-Do-list: To-Do list ---------- If you would like to see some functionality in my extension, let me know. .. _ChangeLog: ChangeLog --------- - 0.1.1 First release with full documentation. - 0.1.5 Last built query is saved and available on debug, Quotes in input are escaped to csv-compatible double quotes (was messing whole csv) Additional tables added to not\_allowed\_tables - 0.2.0 Code reworked and compatible with TYPO3 version 7.x and 8.x Userfunc field processing Many minor improvements – some of them breaks compatibility. Please refer to Migration section, if updating - 0.3.0 Charset converting - iconv changed to mbstring (thanks to Henri Nathanson for suggestion) Inactive records filtering changed to native - now use enableFields = 1 for this. use default\_enableColumns only when table isn't in TCA Option add\_fields fixed - works again Debugging now works automatically when TYPO3\_CONTEXT == Development, no need to set debug\_allowed = 1 Process->unserialize now can use custom delimiter New processors: ->tableLabelsFromRecordsCommalist, ->tableLabelsFromMmRelations 14 .. ######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: 492 .. :id: grafika1 .. :name: grafika1 .. :width: 556 .. |img-4| image:: img-4.jpeg .. :align: left .. :border: 0 .. :height: 553 .. :id: grafika2 .. :name: grafika2 .. :width: 669