List CSV Import/Export problems and discussion

[Bauer]

Please excuse this long-winded post ? but there is no short way to explain this. Now that my recent grumpiness has abated (sorry), I?m back to worrying about the List CSV Import/Export feature. I don?t give up easy - on myself or Fabrik.

As I?ve mentioned in other posts, my project provides surveys that collect ?compensation data? (basic numeric wage/salary data for job positions) for the healthcare industry. I find it hard to believe that no one has ever had the need to do what I want to do with Fabrik, as Hugh suggested to me last week. Or maybe they have, but soon came to realize that the Fabrik CSV Import/Export tool doesn?t allow them to do it???

For some quick background? Until I came along, my client/boss was providing these (similar) surveys to her clients (thousands of them) as Excel spreadsheets - which allowed them to complete the surveys offline, at their leisure, using the tools available with Excel and/or similar software used within their organization.

So I need to provide these ?Export? files as valid Excel files (xls xlsx). I have created some php code that uses the phpExcel Library to convert the CSV generated by Fabrik into a true Excel Workbook/Worksheet.

However, there are many problems ? or at least hurdles ? that I must overcome in order to work with the standard List CSV Export/Import feature of Fabrik ? which would make it a LOT easier than having to hard-code all of it.

Export

1. These ?Excel CSV? files that are exported as ?Excel compatible? files by Fabrik are very confusing when opened in Excel. Anyone who has ever tried this knows what I mean. That is why I need to convert them to true Excel files first.

2. The CSV export does not provide a means to format the numeric data properly. E.g. An hourly wage of $23.20 appears as 23.2 (aligned-left) rather than 23.20 (aligned-right). Nor does it allow me to freeze the header, lock cells, hide columns, format cells, etc. That?s where the magic of phpExcel comes in to play.

3. All of the survey data is stored in one ?master? file that is used to create reports/visualizations. So, although, I use the same file to collect and store the data, there are 7 different Menu configurations to set the correct pre-filters on the elements needed for the various types of surveys. However, the Export options that are setup in the Fabrik List params are not changeable on a per-page/per-menu-item basis. It would be optimal to allow the setup for the filters to be controlled by the menu params, not the List params ? although I suppose I can work around that by writing some php code to identify what menu option is being used.

4. These exported files are also being stored online, in the user?s ?personal files area? (using jsmallfib) ? so every file generated needs to have a unique file name and needs to be stored in a unique ?user? folder, depending on the user and the type of survey data it contains. Some users might have a dozen different, surveys ? for different survey types and for different facilities. E.g. they might be entering data for 3 different hospitals, 3 different Hospice facilities, etc. - and that data needs to be identifiable yet separated.

So I have written some post-processing code that converts the exported CSV into the type of Excel file the user wants, formats and re-names it appropriately, and stores it in the appropriate folder in their ?Personal files area?. The users now have an Excel Worksheet every bit as user-friendly? as the Excel surveys my client had been sending them for years.

5. However, the big problem with the CSV export is that the ?Front end options? are not configurable ? other than to show them or not show them. It?s all or nothing. The CSV export popup that is shown if ?Front end options? is set to ?Yes? allows the user to change what is already setup in the List params ? and to make matters worse, the options in that popup don?t even default to the values that have been set in the List parameters! (I just posted some code at github that fixes that and hopefully it will get added to the Fabrik core.) That way I can at least hide the options via javascript that I don't want them to change.

6. If the list includes a joined table and the Lists CSV 'Front End options' is set to 'Yes', the fields are generated in the expected column order (as set in the 'Show in list' parameter) - but if the 'Front end options' parameter is set to 'No', the fields for the joined table are always added as the last columns.

7. That 'Front end options' popup also includes options for ?Form fields? (elements not shown in the list) ? something I don?t want the user to be able to add to their exported file. In other words, I want the Export to ALWAYS include ONLY the elements that are ?Visible? (shown in the list) ? and there is really no way to do that, unless I use 'Allow front options' and give the user the options to change everything else too - something I don't want, not to mention it only confuses the end-user (like they would have any idea what the table_names and 'Form fields', etc. really are).

So the List parameters are useless for me as is. Yet I don?t see it being too difficult to fix these issues and accommodate the changes needed to make this work - for me and any other past, present or future Fabrik developers.

Import

1. The user is able to upload more data in their Import than was included in the Export. I NEVER want the user to be able to include more rows than were included in the exported file. In other words, the export and import file will always have the same number of rows with the same PK IDs (which I can hide in the spreadsheet when the file is converted via the phpExcel library). And the import must ignore any rows other than those that are already included in the list (filtered for that user/facility). It seems those rules could easily be set in the list or menu parameters.

2. Right now, the import is adding ?default values? to elements ? for those elements not included in the import (those not visible/shown in list). Just because there is no value for every element in the list does not mean that the row updated on import (when ?Overwrite? is set to yes) needs to have the values for those non-included elements reset to their ?default value?!

3. As mentioned, in my situation, the file that is Imported needs to ALWAYS be an ?Overwrite?. HOWEVER, the user interface for CSV Import always shows the ?Overwrite? option, allowing the user to change that. And again, that Overwrite option always defaults to ?No? - when for me, for this list anyhow, it should always default to ?Yes? (so I can at least hide it from the user via javascript and prevent them from changing it).

It seems to me that is can?t be too difficult to write some code that will set those Import options (on the front end) to default to whatever is set in the List parameters (like I just changed for the Export ?Front end options? popup). But as is now, those List Parameters are only used if the ?Front end option? is set to ?No?. However, in that case, the ?Overwrite? option is set to ?No? also. So there?s no way to change it without allowing the user to change all the other options that I don?t want changed! I?m stuck here between a rock and hard place.

In a follow-up post, I?m going to attach some screen captures showing the additional fields I added to the list parameters to handle all this, in the hopes that I can work with the Fabrik team to get this working the way I need it to work. There I will explain what each new parameter does and why (I feel) it wouldn?t really be that difficult to incorporate them into the Fabrik core.

If anyone sees this as something they too would like to see implemented, PLEASE speak up and respond to this thread so that Rob, Hugh, and the rest of the Fabrik team recognizes that it?s not just something that only I would ever use, or think that it?s not worth the effort to implement. Many of the things I listed are not just 'my problem' - they are things that just don't work right and should be fixed.

 

[Bauer]

As promised...
Attached are screen snips of the List CSV parameters - as well as some shots of the final 'product' - as the generated Fabrik CSV, and after conversion to an XLSX file using phpExcel. Both files were generated via the Fabrik List CSV Export feature (with a few tweaks, hehe).

I also included a window clip (asCSV.PNG) of the CSV Export file - before conversion - but as as it is generated when I set the 'Front end select' to 'No'. Note how the job description (from the joined table) is placed in the last column (which doesn't happen if Front End Select is set to Yes) - and why the number formatting is unacceptable?

This is an explanation for the parameters in the List 'CSV' tab s seen in list_csv_params1.PNG and list_csv_params2.PNG...

Import Options - Separator for Import Options
Import access - User access level required for CSV Import (No change)
Import access addendum - PHP file that overrides Import access - returns true/false if user can access CSV Import
(Changes 'Import access' if file exists and returns true/false)
Pre-process PHP - PHP file that processes the uploaded file (e.g. Excel xlsx) and converts it to CSV for Fabrik
(e.g. If an xlsx file is uploaded, phpExcel code to first convert the file to CSV)
Overwrite - Default value used for the CSV Import popup 'Overwrite' option (Default No)
(If set to Yes or No, initializes but hides the option in the popup - otherwise user can select)
Allow New - Flag to allow or prevent the import of any new rows.
Export Options - Separator for Export Options
Export access - User access level required for CSV Export (No change)
Export access addendum - PHP file that overrides 'Export access' - returns true/false if user can access CSV Export
(Changes 'Export access' if file exists and returns true/false)
Popup width - CSV Export options popup width (maybe not needed)
Heading format - Format of labels used for column headings (No change)
Export step - Number of rows processed at a time (No change)
Replace Line breaks - Replaces line breaks in CSV Export as specified (No change)
Custom QS - Additional custom export Query string (Changed from text to textarea)
File encoding - (No change) Only used if Export file format is 'CSV'?
Double quote - (No change) Only used if Export file format is 'CSV'?
CSV Delimiter override - (No change) Only used if Export file format is 'CSV'?
Export file format - Added 'Custom' option (otherwise No change)
(If 'Custom', the 'File type' options in the CSV Export options popup
are taken from 'File extensions' and 'Post-process Custom PHP' is run
to convert the exported CSV to that file format)
File name - Name of the CSV file or table name if blank. (No change, left intact for backwards compatability)
File name PHP - If selected, use the returned file name instead of 'File name'
(Always expects 'csv' extension)
File save path - If selected, php code that should return the full store path for the export
(Check if valid path, else use default temp dir)
File Extension(s) - Comma delimited list of valid export/download file extensions
(If more than one, force 'Front end select' popup, else default to extension entered)
(Assumes 'csv' if blank, else - if not csv - assumes 'Export file format' is 'Custom' and
runs the 'Post-prosess Custom PHP' code to convert the CSV export to that custom format.)
Post-process Custom PHP - PHP file that converts CSV export file to the selected 'Custom' format
(per 'File Extension(s)' set or selected)
Front end options - Show/Hide the CSV export front end options (No Change or set via PHP below)
Front end options PHP - Optional PHP code that returns True/false to determine/override showing 'Front end options'
Which Elements - (No change)
Elements - (No change)
Include filters - (For this, and the rest, initializes the Yes/No value for these CSV Export 'Front end options' or allows the User to select via 'Front end options'. Should probably also add these same 3 options (Yes/No/User) for an 'Include form fields' paramater here too, since that is shown in the 'Front end options' popup yet I see nowhere else to set whether that should even be an option.)
Include data -
Include raw data -
Include calculations -

I have this all working, if you would like me to include the code I used for any of it as an example.

All of the PHP files could of course use a textarea or php editor instead - or an option for either/or. (This was the easy way.) If files are used, a default path for holding these php files needs to be determined. Right now I'm just using the ./plugins/fabrik_list/listcsv/scripts folder (which has nothing to do with this I know).

 

[Bauer]

Here's an example of some of the PHP code used.

For example, implementing the 'Post-process Custom PHP' was just a matter of a few lines inserted in the writeCSVFile() function in ./components/com_fabrik/models/csvexport.php

/**
* Write the final csv file
*/
public function writeCSVFile()
{
    $filePath = $this->getFilePath();
    $params = $this->model->getParams();
    $csv_format = $params->get('csv_format');
    $csv_export_custom_php = $params->get('csv_export_custom_php');
    if($csv_format=='2' && file_exists(JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/'.$csv_export_custom_php)){
        require(JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/'.$csv_export_custom_php);
    }else{
        $str      = $this->getCSVContent();
        JFile::delete($filePath);
        echo $str;
    }
    exit;
}

I then used this code in the specified 'Post-process Custom PHP' file (e.g. 'list_260_csv_export.php')

<?php
$ftid =  $this->app->getUserStateFromRequest( 'ftid', 'ftid', 0 );
$incfile = '';
switch ($ftid) {
    case '1':
        $incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_hospital.php';
        break;
    case '2':
        $incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_nursing.php';
        break;
    case '3':
        $incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_practice.php';
        break;
    case '4':
        $incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_homehealth.php';
        break;
    case '5':
        $incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_management.php';
        break;
    case '6':
        $incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_physicians.php';
        break;
    case '7':
        $incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_hospice.php';
        break;
    default:
        break;
}
if(file_exists($incfile)) include($incfile);
?>

...which, depending on the active list menu as setup in the case statement, includes another PHP file that looks similar to this 80 or so lines of code ('csv_export_hospital.php') - which calls the phpExcel library, converts the CSV to XLSX, initializes the workbook and worksheet, adds the column formatting and styling as needed - including 'hiding' the lengthy 'job description' in Column 'D' (which is from the joined table) so that column D acts as a navigator to show the job description for the selected row above in the formula bar - then freezes the header, locks and password protects the uneditable cells, hides the column containing the PK ID, and saves it with the appropriate file name and extension in the appropriate user 'personal folder' (and also downloads it directly, in lueu of the standard 'csv' file) - to get the results shown in the window snip example attached in previous post (asXLSX.PNG).

<?php
// Import PHPExcel library
jimport('phpexcel.library.PHPExcel');
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '32MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings);

$user = JFactory::getUser();
$mfid =  $this->app->getUserStateFromRequest( 'mfid', 'mfid', 0 );
$db = JFactory::getDbo();
$myQuery = "SELECT f.facility_name, c.offline_filetype FROM fb_member_facilities as mf LEFT JOIN fb_facilities as f ON mf.facility_id=f.id LEFT JOIN fb_conglomerates as c ON mf.membership_id=c.id WHERE mf.id=".$mfid;
$db->setQuery($myQuery);
$ainfo = $db->loadAssoc();
$facility_name = $ainfo['facility_name'];
$this->fileExtension = $ainfo['offline_filetype'];

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader('CSV');
$objPHPExcel = $objReader->load($this->filePath.'/'.$this->fileName.'.csv');

// Set document properties
$objPHPExcel->getProperties()->setCreator("MyCompany")
    ->setLastModifiedBy($user->name." [".$user->username."]")
    ->setTitle("MyCompany Hospital Survey")
    ->setSubject("CompData [".$mfid."] ".$facility_name)
    ->setDescription($this->fileExtension." file for ".$user->username.", [".$mfid."] ".$facility_name)
    ->setKeywords("MyCompany PHPExcel php")
    ->setCategory("MyCompany survey file");

$sheet = $objPHPExcel->getActiveSheet();
$sheet->setTitle(substr($facility_name,0,31));
$highestRowCount = $sheet->getHighestRow();
$highestColumnCount = $sheet->getHighestColumn();

// Freeze header
$sheet->freezePane( "F2" );
$sheet->getStyle("A1:{$highestColumnCount}1")->applyFromArray(
    array(
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'FFFF00')
        )
    )
);

$sheet->getStyle("E1:E{$highestRowCount}")->applyFromArray(
    array(
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => '000000')
        )
    )
);

$sheet->getStyle("A2:D{$highestRowCount}")->applyFromArray(
    array(
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'F9FAEB')
        )
    )
);
$sheet->getColumnDimension("A")->setVisible(false);
$sheet->getColumnDimension("B")->setWidth(30);
$sheet->getColumnDimension("D")->setWidth(40);
$sheet->getColumnDimension("E")->setWidth(1.7);
$sheet->getColumnDimension("F")->setWidth(30);
$sheet->getColumnDimension("G")->setWidth(40);
$sheet->getStyle("H1:{$highestColumnCount}{$highestRowCount}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet->getStyle("H1:H{$highestRowCount}")->getNumberFormat()->setFormatCode('#,##0.0');
$sheet->getStyle("I1:{$highestColumnCount}{$highestRowCount}")->getNumberFormat()->setFormatCode('#,##0.00');

$sheet->getComment("E1")->getText()->createTextRun("Click column to view Job Description");
$sheet->getComment("E1")->setWidth("280px");
$sheet->getComment("E1")->setHeight("18px");

$sheet->getProtection()->setPassword('PHPExcel');
$sheet->getProtection()->setSheet(true);
$sheet->getStyle("F2:{$highestColumnCount}{$highestRowCount}")->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

if($this->fileExtension=='xlsx'){
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
}else{
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
}
$objWriter->save($this->filePath.'/'.$this->fileName.'.'.$this->fileExtension);
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
?>

As you can imagine, I have a lot of work into this project (almost 3 years now), and it's been one heck of a learning experience, with more than a few cracks at reinventing the wheel. Too bad I haven't made any money from it... yet. That's why I could really use some co-operation from my friends at Fabrik.