Question about CSV export in CC6

Hi,

I use paging in read_data (enabled / page limit: 50).
So my table is displaying 50 rows out of a total of more than 6,000 rows per table paging.

I want to export all records of table to csv file.
However, only the data records displayed by table paging are exported.

How can I export all records of the table?

:-) F.

Hi Fredolino,

W hat do you want to do with the csv file? If you want to use it in Excel then there is an Excel extension available from the MySQL site that will let you do this (provided that your database is remotely accessible).

Otherwise there is some PHP code in this forum post that should do it from a simple form.

Bob

there is an easy way to export to xlsx a read_data

IN THIS WAY

i can pass the name of the read data i want export to xlsx so i can use this function with every read_data i want
i can pass the name of xlsx file i want to export so i can choose my file name every time
need only a file to be downloaded

download from https://github.com/mk-j/PHP_XLSXWriter : you need only this file xlsxwriter.class.php
put this file in a folder you want (eg. /myscript/XLSX_WRITER/xlsxwriter.class.php')

in CC6
1) with databuilder2 (or php or other mehod you need):
save the name of read_data you want to export to xls
eg. name_recordset="read_data" (no quotes)
save the name of xlsx file in "filexls"
eg. filexlsx="myfile.xlsx" (no quotes)
(if you change data variable names name_recordset or filexlsx you must change accordingly in script php below)

2)add a function->custom code
name it eg. export_on_xlsx
and paste this code

<?php
require(JPATH_SITE.'/myscript/XLSX_WRITER/xlsxwriter.class.php');
// recordset name passed in data("name_recordset")
$nome_rs=$this->data("name_recordset");
// associative array key->value
$rs=$this->get($nome_rs, "default");
$nomefile=JPATH_SITE."/media/uploads/".$this->data("filexlsx");
//rows in array (title and data)
$heading = false;
$data=array();
if(!empty($rs)) {
foreach($rs as $row) {
if(!$heading) {
$data[]=array_keys($row);
$heading = true;
}
$data[]= array_values($row);
}
$writer = new XLSXWriter();
$writer->writeSheet($data);
$writer->writeToFile($nomefile);
} else {
echo "<br><b>NO RECORD TO SHOW</b>";
}
?>

3) create an event called by a button (or watehever you want)
in event you should create read_data, export on xlsx, crete a link to download file xlsx (view:download_file for me is a form with a button link)
{fn:read_data}
{fn:export_su_xlsx}
{view:download_file}

in this way :
you pass a name of a read_data
you pass a filename
call xlsx export (creating a file on server)
link file to download

errata corrige (sorry i can't modify with this forum version)


3) create an event called by a button (or watehever you want)
...
...
{fn:export_on_xlsx} <<<<-------
...

hi, but..
I actually wanted to create a CSV file. It also works with the CC6 features:

1. I create a Read Data with Paging (Enabled, Page Limit: 20).
This is necessary because I can't display over 6,000 records on one page.

2. I create a PHP file that runs through Read Data and prepares the export data:
name: prepareData
Code:
$read = $this->get('read_beobachtungen');
foreach ($read as $key => $record)
{
$r['BASIS_artgruppe'] = 'Schmetterlinge (Lepidoptera)';
$r['BASIS_methode'] = $record['Nachweis']['nachweismethode'];
$r['BASIS_datum1'] = $record['Beobs']['datum'];
$r['datum2'] = $record['Beobs']['datum'];
$r['zeit'] = '';
$r['BASIS_beobachter'] = $record['Beobachter']['name'];
$r['nachweisquelle'] = $record['Beobs']['datenquelle'];
$r['beobachtung_bemerkungen'] = $record['Marker']['description'];
$r['BASIS_ort'] = $record['Marker']['title'];
$r['BASIS_ortslage'] =$record['Marker']['lage'];
$r['name_staat'] = 'Deutschland';
$r['name_provinz']=$record['BL']['bundeslaender'];
$r['name_kreis'] = $record['Lkr']['landkreise'];
$r['plz'] = '';
$r['mtb'] = $record['BeoOrt']['mtb_id'];
$r['geokoordlat1'] = $record['Marker']['latitude'];
$r['geokoordlon1'] = $record['Marker']['longitude'];
$r['geokoordlat2'] = '';
$r['geokoordlon2'] = '';
$r['fo_bemerkung'] = $record['Beobs']['beo_bemerkung'];
$r['fo_aufnahmedatum'] = '';
$r['fo_bearbeitungsdatum'] = '';
$r['GATTUNG'] = $record['Haupt']['gattung'];
$r['ART'] = $record['Haupt']['art'];
$r['BASIS_stadium'] =$record['BeoArt']['stadium'];
$r['BASIS_anzahl gesamt'] = $record['Hauf']['hauf_name'];
$ret[$key] = $r;
}
$this->set('exportData', $ret);

3. I create a CSV export file:
name: csv_export
data provider:{var:exportData}
Delimeter: ;
File name: backup.csv
Storage path: {path:root}/csv/backup.csv

4. I create an event:
name: export

{fn:read_beobachtungen}
{fn:prepareData}
{fn:csv_export}

5. I create a view table and display all the data
include Form
Data Provider: {var:read_beobachtungen}
Column list:
...
Nachweis.nachweismethode:Methode
BeoArt.stadium:Stadium
BeoOrt.mtb_id:MTB
Marker.title:Fundort
Marker.lage:Lage
Gem.gemeinde:Gemeinde
...
6. I create an export form
name: exportformular
data provider: {var:read_beobachtungen}
event: export
content: {view:export_csv_button}

7. I create an export button
name: export_csv_button
event: export

8. I create an paginator
Display: Navigation & Length switcher

When I click the export button, all records should be exported.
And here's the problem!
However, only the data records that are displayed on the current page of the table are exported. And that's 20 records.
On the 2nd page of the table then again only 20 data records etc. And in the saved backup.csv ({path:root}/csv/backup.csv)
only the 20 data records are in there.

I've now tried various external scripts (e.g. JQuery plugins) and it didn't work.
Maybe someone knows a solution?

:-) F.

hi Fredolino
i don't know how in your case
in generale, as i know, to export in csv you must re-read data, if you can, don't use limit

p.s.
i use this synhtax in CC6 {var:read_data_action_name.[n].model_name}
eg. {var:read_beobachtungen_nolimit.[n].mymodel}

Hi :-)

The problem I have:
if I don't set a limit in Read Data, I only get a white page because all the datasets are not displayed to me on one page at once. There are too many records being loaded.
Without read data in the event, however, there is no export.

Maybe, i don't understand
you wrote: "I use paging in read_data (enabled / page limit: 50). So my table is displaying 50 rows out of a total of more than 6,000 rows per table paging."

read_beobachtungen is the source for your list view, right?

you should create anoothjer read_data with same sql of read_beobachtungen except for LIMIT 50 (disabled limit and 0) and your php code should work on it
read_beobachtungen FOR YOUR LIST VIEW
read_beobachtungen_csv FOR YOUR CSV

p.s.
your php code is needed or you can use a view in database to create data to csv?

the read data has 11 relations to other database tables, not just one model.

the read data has more than 6,000 data records that are not displayed without a page limit because only a white page is displayed (timeout).

the read data is for the view of the tablelist in the view
and is run through with a foreach loop:
PHP function named: prepareData
$read = $this->get('read_observations');
foreach ($read as $key => $record)
....
$this->set('exportData', $ret);

and the result of "exportData" is Data provider of the CSV function.

Fredolino, I understand you use the same DBread to put data in list view and in csv but i think is not correct
cause when you have a LIMIT (paginator) you catch the record you ask for with button
if you clic 2nd page or 3nd page or 4rd page, CC6 execute a query that take records from offset->to page limit (in your case only 50 per query)
if you use {debug:} you can see it, you have only 50 record.


In my previous answwer I sayed that you must use 2 different read data (for my experience)
1 for list view
1 for your csv
different, with different name and different SQL only in the LIMIT PART (1 with and 1 without LIMIT!!)

to simplify, you can:

1) create a view in MYSQ (called for example my_eleven_table_view)
so you have only 1 SQL that you can manipulate as you want and the two read data (in CC6) are based on this view

2) inCC6 create 2 read_data that are both based on the table my_eleven_table_view
- 1st with LIMIT 50 for your list view
- 2nd without LIMIT for your csv.
when you want to export to csv your event should be something like this
{fn:read_data_csv}
{fn:extract_csv}


with view you can compose data and record fields from various table so, i think, you don't need to struggle with php, don't need to maange join in CC6 and you can reuse the SQL.

if i understand correctly i use 2 read data. E.g.
read_data_no_limit
read_data_limit

I had already tried something like that.
But the problem comes back here, because the two read_data must be displayed under the event tab for export and view. However, as soon as the read_data_no_limit has to be loaded for the export, it is not displayed due to the large number of data records in the database table and a white page is displayed due to the long loading time.

mmmh, i think that 6000 record are a very few set to manage for mysql.

resume
- when you view list on screen you load only the limited read data, don't load read data for csv!
- whern you clic button to export csv you call an avent that read all record and create csv

if you call a read data and have long loading time, maybe your table are not well indexed,
verify indexes in all tables involved and test your sql in mysql, 6000 record should be fast.
Take a look of what happen during blank screen loading (in firefox pressing F12 )

The problem is not the complex data query, because according to phpMyAdmin it takes: "6058 in total, the query took 0.0087 seconds".

The problem is the "read_data" function of CC/CF6, which simply cannot display large amounts of data without table paging or a filter.
For example, if I only want to display 4,000 or 3,000 records at once, that's usually possible.

:-) F.