There are numerous issues with CSV Imports on the front end using the current importcsv.php code. Upon reviewing the code it is obvious that it was primarily designed for use with the CSV Import Cron plugin. And while that might be working fine, there are many parts of this code that do not work well when used for a front end CSV Import.
For example...
The most glaring issue is the lack of an 'Overwrite' option. There may be a setting for that in the Cron Plugin, but there is no setting for it when used from a frontend menu item. The same is true for the 'DropData' option. So, on the front end, both options use the default value of 0 (No).
The workaround for lack of a 'Drop Data' option, I suppose, would be to empty the table before importing. But, as is, there remains the inability to set the 'Overwrite' option to Yes - which prevents the upload (Importing) of updated data from a changed list without causing errors or without creating duplicate records as each row is imported.
Another big problem with importcsv.php is that it was not honoring any 'Show in list' settings and so it was trying to update every column in the table - even those not included in the list; and, to make matters worse, it was inserting the 'default value' for any columns that were not included in the import - which would overwrite valid data in non-included elements with their default values!
There are also a few instances where variables are referenced (like $task) which, when called from the front end, have a different value than the code expects (if used for the cron).
And finally, while I realize their purpose with cron jobs, the use of 'exception errors' on the front end just seems unacceptable. If anything fails during the import process, all the user sees is a poorly formatted Exception error page sent from the server - when a simple system warning message and return to the list page would suffice and is far less confusing or intimidating to the user.
When I first encountered the problems mentioned, when I tried using the Fabrik CSV Import feature a few years ago, I just decided to write my own code to handle that - but I always thought in the back of my mind that this was something that could and should be fixed in Fabrik, as it would make things so much easier for me in this project.
So I have been working for quite a while on deciphering the code and making it work as I always expected it to work, and taking care of the issues mentioned above - and am now using the proposed code changes that I have also posted as a PR at github.
To get this all working as you (I) would expect it to work, I added 3 new options to the fabrik menu tab... Overwrite, Drop Data, and Valid extensions.
While the need for Overwrite and Drop Data are explained above, the 'Valid extensions' option was added to allow the upload of files that are not the standard Fabrik CSV Import file types (txt,csv,tsv). And in that case would require the inclusion of a custom PHP file to 'pre-process' the uploaded file and convert it to Fabrik-recognized CSV format before importing (or that custom PHP file could also include the code that does that). This concept is intended to be used with the new post-processing CSV Export custom php file that was introduced a few weeks ago.
To explain (and as I have explained before), I use 7 different menu options all sharing the same list, but set to use different filters and different 'Show in list' settings. Using a custom list template, those lists contain survey data laid out to collect simple numeric data in a 'spreadsheet-like' manner; and I always assumed the Fabrik CSV Import/Export features would allow users to export/download their surveys to 'work offline'.
But in order to make the surveys much more user-friendly, the work-offline files are first converted to Excel spreadsheets (using the new CSV Export post-processing feature) - so that the user can edit their surveys at their leisure in Excel and then upload them (also in Excel format) back to the server. So there was a needed for both post-processing of the 'CSV Export' file (to convert to xlsx) and, upon import, a pre-processing of that Excel xlsx file (using the PHPExcel library) in order to get the user's updated survey data back into the Fabrik table.
Until someone can come up with a cheap and effective online spreadsheet that works as fast and has all the features of Excel, this seems to be the best solution; and I feel it makes this sensitive data more secure than if I used some cloud service like Google Documents. And so I am thankful I have Fabrik for that. It's taken me almost 4 years now to iron out all the issues - but I'd like to think I've got what I need to make the main 'engine' of my project work. So I'm keeping my fingers crossed that these latest PRs (#1730, #1731, #1732) are merged, as I think this would be a welcome fix/addition to Fabrik.
For example...
The most glaring issue is the lack of an 'Overwrite' option. There may be a setting for that in the Cron Plugin, but there is no setting for it when used from a frontend menu item. The same is true for the 'DropData' option. So, on the front end, both options use the default value of 0 (No).
The workaround for lack of a 'Drop Data' option, I suppose, would be to empty the table before importing. But, as is, there remains the inability to set the 'Overwrite' option to Yes - which prevents the upload (Importing) of updated data from a changed list without causing errors or without creating duplicate records as each row is imported.
Another big problem with importcsv.php is that it was not honoring any 'Show in list' settings and so it was trying to update every column in the table - even those not included in the list; and, to make matters worse, it was inserting the 'default value' for any columns that were not included in the import - which would overwrite valid data in non-included elements with their default values!
There are also a few instances where variables are referenced (like $task) which, when called from the front end, have a different value than the code expects (if used for the cron).
And finally, while I realize their purpose with cron jobs, the use of 'exception errors' on the front end just seems unacceptable. If anything fails during the import process, all the user sees is a poorly formatted Exception error page sent from the server - when a simple system warning message and return to the list page would suffice and is far less confusing or intimidating to the user.
When I first encountered the problems mentioned, when I tried using the Fabrik CSV Import feature a few years ago, I just decided to write my own code to handle that - but I always thought in the back of my mind that this was something that could and should be fixed in Fabrik, as it would make things so much easier for me in this project.
So I have been working for quite a while on deciphering the code and making it work as I always expected it to work, and taking care of the issues mentioned above - and am now using the proposed code changes that I have also posted as a PR at github.
To get this all working as you (I) would expect it to work, I added 3 new options to the fabrik menu tab... Overwrite, Drop Data, and Valid extensions.
While the need for Overwrite and Drop Data are explained above, the 'Valid extensions' option was added to allow the upload of files that are not the standard Fabrik CSV Import file types (txt,csv,tsv). And in that case would require the inclusion of a custom PHP file to 'pre-process' the uploaded file and convert it to Fabrik-recognized CSV format before importing (or that custom PHP file could also include the code that does that). This concept is intended to be used with the new post-processing CSV Export custom php file that was introduced a few weeks ago.
To explain (and as I have explained before), I use 7 different menu options all sharing the same list, but set to use different filters and different 'Show in list' settings. Using a custom list template, those lists contain survey data laid out to collect simple numeric data in a 'spreadsheet-like' manner; and I always assumed the Fabrik CSV Import/Export features would allow users to export/download their surveys to 'work offline'.
But in order to make the surveys much more user-friendly, the work-offline files are first converted to Excel spreadsheets (using the new CSV Export post-processing feature) - so that the user can edit their surveys at their leisure in Excel and then upload them (also in Excel format) back to the server. So there was a needed for both post-processing of the 'CSV Export' file (to convert to xlsx) and, upon import, a pre-processing of that Excel xlsx file (using the PHPExcel library) in order to get the user's updated survey data back into the Fabrik table.
Until someone can come up with a cheap and effective online spreadsheet that works as fast and has all the features of Excel, this seems to be the best solution; and I feel it makes this sensitive data more secure than if I used some cloud service like Google Documents. And so I am thankful I have Fabrik for that. It's taken me almost 4 years now to iron out all the issues - but I'd like to think I've got what I need to make the main 'engine' of my project work. So I'm keeping my fingers crossed that these latest PRs (#1730, #1731, #1732) are merged, as I think this would be a welcome fix/addition to Fabrik.
Last edited:
