Importing a database of products from a CSV file

Hi fsardoni,

The CSV Export plugin wasn't really designed to be used this way. The plugin adds extra psuedo-fields for readability (e.g. ":label" for list fields). By default, CSV Export also removes the "dragsortorder" field from its output which isn't very human-readable.

It is possible to manually massage a CSV file into the format that phpmyadmin expects, but this can be very tricky.

Firstly, you can disable the skipping of fields by changing this line in the csvExport.php file:

$GLOBALS['CSVEXPORT_SKIP_FIELDS'] = array('dragSortOrder','createdBy._link','_filename','_link'); // These fields won't be exported from any table. Example: ('_filename','_link','lastUpdated','etc')

...to this:

$GLOBALS['CSVEXPORT_SKIP_FIELDS'] = array('createdBy._link','_filename','_link'); // These fields won't be exported from any table. Example: ('_filename','_link','lastUpdated','etc')

Next, you can manually strip out the extra fields that phpmyadmin isn't expecting. To see which fields it's expecting, do a CSV export from phpmyadmin.

Finally, when uploading your CSV file to phpmyadmin, you'll need to tell it to use commas (,) instead of semi-colons (;) for "Fields terminated by". You'd think that commas would be the default for CSV files. Go figure.

---

Alternately, CMS Builder also has a backup feature available under Admin > General Settings > Database Settings; however, it backs up and restores all the tables in its database. What exactly is your workflow here? I might be able to suggest an alternate approach that could be simpler for you if I knew what it was that you were trying to accomplish.

I hope this helps! Please let me know if you have any questions.

Hi Chris, thanks again for your help.
What I am trying to achieve here is an import of all my products from an existing CSV.

Step 1: From PHPmyAdmin I exported the cms_products table (as a CSV file) to see what columns CMS Builder creates when I created my first product manually.

Step 2: I copied the content from my original "products" CSV into the export CSV file from CMS Builder and then tried to upload it to CMS builder DB.

In short I want to do a quick upload of all my products instead of creating each record (for my products) manually.

Hi fsardoni,

That sounds reasonable. :)

You'll want to do a CSV export from phpmyadmin to see the format it expects. Then you can munge your CSV file to look like what phpmyadmin expects and import that.

OK. It works with your suggestion of changing the default ";" to ","...

As a note for others, it only works when changing the import from
"CSV" to "CSV using LOAD DATA" and the default "Fields Terminated By" = ";" to =","

Thank you again Chris.

Hi Fsardoni,

If you do a lot of database massaging, you might want to give Navicat for MySQL a look.

http://navicat.com/en/products/navicat_mysql/mysql_overview.html

They offer a free 30 day full version trial and also a free "lite" version of the program.

Best,

Jerry Kornbluth