KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

CSVExportable #2755

Open James Schlies opened this topic on on Jul 7, 2022 - 14 comments

J
James Schlies commented on Jul 7, 2022

I switched to CSVExportable and I'm seeing the following..... See below for the config I'm using and the SQL query in question. I can provide an example of the CSV on request, results of query and the resulting Koolreports csv

  1. I cannot seem to control the column order. Sometimes it seems like it's in a random order.
  2. period and billingproviderID, even both are integers, are being split into 2 cols.

THanks Jim

$ReportObj->exportToCSV(

            [
                "dataStores"         => [
                    "PBHBDetailDownload" => [
                        "separator"      => ",", // default separator = "," i.e. comma
                        "enclosure"      => "\"", // default general enclosure = "" i.e. empty string
                        "typeEnclosures" => [
                            "string"   => "\"", // default string enclosure is general enclosure
                            "date"     => "\"", // default date enclosure is general enclosure
                            "datetime" => "\"", // default datetime enclosure is general enclosure
                            "number"   => "", // default number enclosure = "" i.e. empty string
                            "boolean"  => "", // default boolean enclosure = "" i.e. empty string
                        ],
                        'columns'   =>[
                            'modifier_two',
                            'modifier_three',
                            'modifier_four',
                            'units',
                            'charge',
                            'wRVU',
                            'payment,billing_provider_display_name',
                            'billingproviderID',
                            'performing_provider_display_name',
                            'performingproviderID',
                            'service_date',
                            'post_date',
                            'pos_name_full',
                            'trans_type',
                            'period',
                            'division_rpt_division_name',
                            'cost_center,division_rpt_dep_name',
                            'cpt_code',
                            'modifier_one',
                        ],
                    ],
                ],

                // General options for all datastores
                "useLocalTempFolder" => false,
                "autoDeleteTempFile" => true,
                "useCustomColumnEnclosure" => true,
                "useCustomColumnNullString" => false,
                "useCustomColumnEnclosureEscape" => false,
                "BOM"                => true, // default bom = false
                "buffer" => 1000, // unit: KB ~ 1000 bytes. Default buffer = 1000 KB
                //"buffer"             => PHP_INT_MAX,
            ],
        )
                  ->toBrowser("PBHBDetailDownload.csv");

My query:

select trans_type,period,division_rpt_division_name,cost_center,division_rpt_dep_name,cpt_code,CASE WHEN modifier_one = '00' THEN NULL ELSE modifier_one END AS modifier_one,CASE WHEN modifier_two = '00' THEN NULL ELSE modifier_two END AS modifier_two,CASE WHEN modifier_three = '00' THEN NULL ELSE modifier_three END AS modifier_three,CASE WHEN modifier_four = '00' THEN NULL ELSE modifier_four END AS modifier_four,FORMAT(

        CASE 1
            WHEN 1
                THEN net_eligible_units
            WHEN 2
                THEN units
        END ,
        2
    ) AS units, 
        FORMAT(
            CASE 1
                WHEN 1 THEN net_eligible_charge
                WHEN 2 THEN charge
            END ,
            2
        
    )AS charge,FORMAT(
        CASE 1
            WHEN 1 THEN net_eligible_wrvu
            WHEN 2 THEN wrvu
         END ,
         2
     )AS wRVU, 
        FORMAT(
            payment,
            2
     
    ) as payment,billing_provider_display_name,billingproviderID,performing_provider_display_name,performingproviderID,DATE_FORMAT(service_date, '%m/%d/%Y') as service_date,DATE_FORMAT(post_date, '%m/%d/%Y') as post_date,pos_name_full from `division_rpt_provider_summaries` where `period` in (202107) and `division_rpt_cost_center_id` in (82) and `billing_provider_id` in (955) and `division_rpt_hcpc_cpt_id` in (5522)
J
James Schlies commented on Jul 7, 2022

J
James Schlies commented on Jul 7, 2022

And when I do this (explicitly set the 'type') for several cols, those cols vanish from CSV

$ReportObj->exportToCSV(

            [
                "dataStores"         => [
                    "PBHBDetailDownload" => [
                        "separator"      => ",", // default separator = "," i.e. comma
                        "enclosure"      => "\"", // default general enclosure = "" i.e. empty string
                        "typeEnclosures" => [
                            "string"   => "\"", // default string enclosure is general enclosure
                            "date"     => "\"", // default date enclosure is general enclosure
                            "datetime" => "\"", // default datetime enclosure is general enclosure
                            "number"   => "", // default number enclosure = "" i.e. empty string
                            "boolean"  => "", // default boolean enclosure = "" i.e. empty string
                        ],
                        'columns'   =>[
                            'modifier_two'=> [
                                "type" => "string",
                            ],
                            'modifier_three'=> [
                                "type" => "string",
                            ],
                            'modifier_four'=> [
                                "type" => "string",
                            ],
                            'units',
                            'charge'=> [
                                "type" => "number",
                                "enclosure" => ["<", ">"], // to apply custom column enclosure "useCustomColumnEnclosure" must be true
                                "headerEnclosure" => "\"",
                                "nullEnclosure" => "",
                                "nullString" => "nULL",
                                "enclosureEscape" => "\"",
                            ],
                            'wRVU',
                            'payment,billing_provider_display_name'=> [
                                "type" => "string",
                            ],
                            'billingproviderID'=> [
                                "type" => "number",
                            ],
                            'performing_provider_display_name'=> [
                                "type" => "string",
                            ],
                            'performingproviderID'=> [
                                "type" => "number",
                            ],
                            'service_date',
                            'post_date',
                            'pos_name_full'=> [
                                "type" => "string",
                            ],
                            'trans_type'=> [
                                "type" => "string",
                            ],
                            'period'=> [
                                "type" => "string",
                            ],
                            'division_rpt_division_name'=> [
                                "type" => "string",
                            ],
                            'cost_center,division_rpt_dep_name'=> [
                                "type" => "string",
                            ],
                            'cpt_code'=> [
                                "type" => "string",
                            ],
                            'modifier_one'=> [
                                "type" => "string",
                            ],
                        ],
                    ],
                ],

                // General options for all datastores
                "useLocalTempFolder" => false,
                "autoDeleteTempFile" => true,
                "useCustomColumnEnclosure" => false,
                "useCustomColumnNullString" => false,
                "useCustomColumnEnclosureEscape" => false,
                "BOM"                => true, // default bom = false
                "buffer" => 1000, // unit: KB ~ 1000 bytes. Default buffer = 1000 KB
                //"buffer"             => PHP_INT_MAX,
            ],
        )
                  ->toBrowser("PBHBDetailDownload.csv");
J
James Schlies commented on Jul 7, 2022

)

J
James Schlies commented on Jul 7, 2022

And switching to BigSpreadsheetExportable seems to fix things. Thouhgts?

S
Sebastian Morales commented on Jul 8, 2022

Thanks, James, for your detail feedback! To solve these issues pls try:

1 . For column order issue, open the file koolreport/excel/ExportHandler.php and replace these lines:

            foreach ($colMetas as $colKey => $colMeta) {
                $label = Util::get($colMeta, 'label', $colKey);

with these ones:

            foreach ($optCols as $k => $v) {
                if (is_array($v)) $colKey = $k;
                else if (is_string($v) || is_numeric($v)) $colKey = $v;
                else continue;
                $colMeta = Util::get($colMetas, $colKey, []);
                $label = Util::get($colMeta, 'label', $colKey);

2 . For split column values, replace comma separator with another separator, for example use a semicolon:

            "separator"      => ";"

Let us know how these methods work for you. Tks,

J
James Schlies commented on Jul 8, 2022

Will do!!! Jim

J
James Schlies commented on Jul 28, 2022

I applied the changes. Using either \koolreport\excel\CSVExportable or useing \koolreport\excel\BigSpreadsheetExportable, downloads are extremely slow (> 2 minutes or never) and I can only control the column order if I do not apply labels or type to individual columns. Please advise

J
James Schlies commented on Jul 28, 2022

class PBHBDetailDownload extends KoolReport {

use Friendship;
use Engine;
use \koolreport\excel\BigSpreadsheetExportable;

/**
 * @return \array[][]
 */
function settings()
{
    return [
        "dataSources" => [
            "PBHBDetailDownload" => [
                "connectionString" => "mysql:host=" . env('DB_HOST', 'domboNext_host') . ";dbname=" . env('DB_DATABASE', 'domboNext_dbname'),
                "username"         => env('DB_USERNAME', 'domboNext_username'),
                "password"         => env('DB_PASSWORD', 'domboNext_username'),
                "charset"          => "utf8",
            ],
        ],
    ];
}

/**
 * @return void|null
 * @throws \Exception
 */
protected function setup()
{
    $this->src('PBHBDetailDownload')
         ->query($this->params['query_sql'])
         ->pipe($this->dataStore('PBHBDetailDownload'));
}

}

J
James Schlies commented on Jul 28, 2022

$ReportObj->exportToCSV(

            [
                "dataStores"         => [
                    "PBHBDetailDownload" => [
                        "separator"      => ";", // default separator = "," i.e. comma
                        "enclosure"      => "\"", // default general enclosure = "" i.e. empty string
                        "typeEnclosures" => [
                            "string"   => "\"", // default string enclosure is general enclosure
                            "date"     => "\"", // default date enclosure is general enclosure
                            "datetime" => "\"", // default datetime enclosure is general enclosure
                            "number"   => "", // default number enclosure = "" i.e. empty string
                            "boolean"  => "", // default boolean enclosure = "" i.e. empty string
                        ],
                        'columns'   =>[
                            'period',
                            'division_rpt_division_name',
                            'cost_center',
                            'division_rpt_dep_name',
                            'cpt_code',
                            'modifier_one',
                            'modifier_two',
                            'modifier_three',
                            'modifier_four'      ,
                            'units',
                            'charge',
                            'wRVU',
                            'payment',
                            'billing_provider_display_name',
                            'billingproviderID',
                            'performing_provider_display_name',
                            'performingproviderID',
                            'service_date',
                            'post_date',
                            'pos_name_full',
                            'trans_type',
                        ],
                    ],
                ],

                // General options for all datastores
                "useLocalTempFolder" => false,
                "autoDeleteTempFile" => true,
                "useCustomColumnEnclosure" => false,
                "useCustomColumnNullString" => false,
                "useCustomColumnEnclosureEscape" => false,
                "BOM"                => true, // default bom = false
                //"buffer" => 1000, // unit: KB ~ 1000 bytes. Default buffer = 1000 KB
                "buffer"             => PHP_INT_MAX,
            ],
        )
                  ->toBrowser("PBHBDetailDownload.csv");
J
James Schlies commented on Jul 28, 2022

I also tried this - copied from example. Still very slow. No where near the time of the 300,000 row example.:

        $ReportObj->exportToCSV(
            [
                "dataStores"         => [
                    "PBHBDetailDownload" => [
                        "separator" => ",", // default separator = "," i.e. comma
                        "enclosure" => "\"", // default general enclosure = "" i.e. empty string
                        "typeEnclosures" => [
                            "string" => "\"", // default string enclosure is general enclosure
                            "date" => "\"", // default date enclosure is general enclosure
                            "datetime" => "\"", // default datetime enclosure is general enclosure
                            "number" => "", // default number enclosure = "" i.e. empty string
                            "boolean" => "", // default boolean enclosure = "" i.e. empty string
                        ],
                        'nullEnclosure' => "", // default = "" i.e empty string
                        'nullString' => "NULL", // default = false i.e empty string for null value
                        'useColumnFormat' => 1, // default = 1, set = 0 to increase export speed
                        'useEnclosureEscape' => 1, // default = 1, set = 0 to increase export speed
                        'useTypeEnclosure' => 1, // default = 1, set = 0 to increase export speed
                        "escape" => "\\", // if escape is empty/undefined, double enclosures will be used
                        "eol" => "\n", // define End of line character, default eol is "\n"
                        'columns'   =>[
                            'period',
                            'division_rpt_division_name',
                            'cost_center',
                            'division_rpt_dep_name',
                            'cpt_code',
                            'modifier_one',
                            'modifier_two',
                            'modifier_three',
                            'modifier_four'      ,
                            'units',
                            'charge',
                            'wRVU',
                            'payment',
                            'billing_provider_display_name',
                            'billingproviderID',
                            'performing_provider_display_name',
                            'performingproviderID',
                            'service_date',
                            'post_date',
                            'pos_name_full',
                            'trans_type',
                        ],
                    ],
                ],

                'useCustomColumnEnclosure' => 0, // default = 0
                'useCustomColumnNullString' => 0, // default = 0
                'useCustomColumnEnclosureEscape' => 0, // default = 0   
            ],
        )
                  ->toBrowser("PBHBDetailDownload.csv");
    }
J
James Schlies commented on Jul 28, 2022

Corrected typos. Same result $ReportObj->exportToCSV(

            [
                "dataStores"         => [
                    "PBHBDetailDownload" => [
                        "separator"                      => ",", // default separator = "," i.e. comma
                        "enclosure"                      => "\"", // default general enclosure = "" i.e. empty string
                        "typeEnclosures"                 => [
                            "string"   => "\"", // default string enclosure is general enclosure
                            "date"     => "\"", // default date enclosure is general enclosure
                            "datetime" => "\"", // default datetime enclosure is general enclosure
                            "number"   => "", // default number enclosure = "" i.e. empty string
                            "boolean"  => "", // default boolean enclosure = "" i.e. empty string
                        ],
                        'nullEnclosure'                  => "", // default = "" i.e empty string
                        'nullString'                     => "NULL", // default = false i.e empty string for null value
                        'useColumnFormat'                => 1, // default = 1, set = 0 to increase export speed
                        'useEnclosureEscape'             => 1, // default = 1, set = 0 to increase export speed
                        'useTypeEnclosure'               => 1, // default = 1, set = 0 to increase export speed
                        "escape"                         => "\\", // if escape is empty/undefined, double enclosures will be used
                        "eol"                            => "\n", // define End of line character, default eol is "\n"
                        'columns'                        => [
                            'period',
                            'division_rpt_division_name',
                            'cost_center',
                            'division_rpt_dep_name',
                            'cpt_code',
                            'modifier_one',
                            'modifier_two',
                            'modifier_three',
                            'modifier_four',
                            'units',
                            'charge',
                            'wRVU',
                            'payment',
                            'billing_provider_display_name',
                            'billingproviderID',
                            'performing_provider_display_name',
                            'performingproviderID',
                            'service_date',
                            'post_date',
                            'pos_name_full',
                            'trans_type',
                        ],
                        'useCustomColumnEnclosure'       => 0, // default = 0
                        'useCustomColumnNullString'      => 0, // default = 0
                        'useCustomColumnEnclosureEscape' => 0, // default = 0
                    ],
                ],
                // General options for all datastores
                "useLocalTempFolder" => true,
                "autoDeleteTempFile" => true,
                "BOM"                => true, // default bom = false
                "buffer"             => 1000, // unit: KB ~ 1000 bytes. Default buffer = 1000 KB
                // "buffer" => PHP_INT_MAX,

            ],
        )
                  ->toBrowser("PBHBDetailDownload.csv");
J
James Schlies commented on Jul 28, 2022

Here our report base class

class KoolReport extends \koolreport\KoolReport {

use \koolreport\clients\Bootstrap;
use \koolreport\laravel\Friendship;
J
James Schlies commented on Jul 28, 2022

I would be happy to arrange a time during your work day to meet, zoom, chat or otherwise work together.

S
Sebastian Morales commented on Jul 28, 2022

Pls try to remove most of export properties to see if it increases speed for you

 $ReportObj->exportToCSV(

            [
                "dataStores"         => [
                    "PBHBDetailDownload" => [
                        'columns'                        => [
                            'period',
                            'division_rpt_division_name',
                            'cost_center',
                            'division_rpt_dep_name',
                            'cpt_code',
                            'modifier_one',
                            'modifier_two',
                            'modifier_three',
                            'modifier_four',
                            'units',
                            'charge',
                            'wRVU',
                            'payment',
                            'billing_provider_display_name',
                            'billingproviderID',
                            'performing_provider_display_name',
                            'performingproviderID',
                            'service_date',
                            'post_date',
                            'pos_name_full',
                            'trans_type',
                        ],
                    ],
                ],
                // General options for all datastores
                "useLocalTempFolder" => true,
                "autoDeleteTempFile" => true,
                "BOM"                => true, // default bom = false

            ],
        )
                  ->toBrowser("PBHBDetailDownload.csv");
 

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

Excel