CSV Import - Multiple Fields
6 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: April 17, 2014 (RSS)
By davidayates - April 15, 2014 - edited: April 15, 2014
How can we import a CSV file with empty fields?
What's currently happening is the import software takes the below sample row:
0001,Title,Description,,Item,,,Item
...and shifts the data to remove the empty fields, which places fields into the wrong column, ie:
0001,Title,Description,Item,Item
Thanks for any help
By Chris - April 16, 2014
It looks like there are at least two PHP bugs related to this issue. For future reference, they're
https://bugs.php.net/bug.php?id=46463
https://bugs.php.net/bug.php?id=55674
What version of PHP are you using? You can find this in CMS Builder in Admin > General Settings > Server Info > PHP Version.
I'll see if I can download the same version of PHP as you're running and try to come up with a fix for you.
Chris
Thanks, Chris - I can't tell you how much time that would save, as we're haveing to import data a column at a time.
PHP Version 5.4.26
By Chris - April 16, 2014
I don't have a proper solution for you as of yet, but here's something you can try in the meantime that might help:
Open the CSV file in a text editor and do a search-and-replace of
,,
with
,"",
Thus, you'll change
0001,Title,Description,,Item,,,Item
into
0001,Title,Description,"",Item,"","",Item
...which should work.
You may need to repeat the search-and-replace to catch all the double commas.
Note that if you have any double commas in legitimate strings, such as
0001,Title,"My favourite punctuation characters are the comma,, doublecomma,, and the semi-colon.",,Item,,,Item
...you'll need to manually fix that line because after you do the search-and-replace, you'll have a line that isn't valid CSV:
0001,Title,"My favourite punctuation characters are the comma,"", doublecomma,"", and the semi-colon.","",Item,"","",Item
Hope this helps!
Chris
By Chris - April 16, 2014
Another workaround you might try is opening the CSV file in a spreadsheet program (such as Excel or Calc) and then exporting it as a CSV — it's possible that the program will (a) properly import the CSV file and (b) export the CSV in a format that CSV Import can read.
Chris
Thanks for the tips. We tried both single anddouble quotation marks and just "null", ie:
,'', or ,"", or ,null,
And unfortunately, CSV used the various text as entries in the fields rather than leave them null. Maybe CSV Import needs a tweak where if it detects an empty field, it uses "null" for the MySQL insert/update?