Database I/O Manager (DbIo) Plugin [Support Thread]

The Database I/O Manager (DbIO) enables you to input (or import) and output (or export) information to and from your database using a .csv file as the "transport method".

It seems like I spent a significant portion of time this year providing clients with "import/export" scripts and DbIo is the culmination of my effort to create an extendable I/O script. Yes, there are other I/O scripts (like EZ-Populate and Apsona) but I wanted a script that holds to these principles:

1. Use Zen Cart's built-in processing, especially the database interfaces.
2. Use a class-based approach, enabling a higher degree of code reuse.
3. Let the database drive the data; use the database settings to guide "proper" input processing.
4. Keep the data-pool consistent; broadly define the character-encoding of the .CSV repositories.
5. Keep to, where possible, the EP-4 interface described in its wiki.
6. Multi-language awareness.
7. Be data-driven. If you've installed a plugin that simply adds a field to an existing table (like products or orders), that field is a candidate for any I/O operation on that database table without additional coding.

Like any process that changes information in your database: Make a backup of your database prior to performing a "full-import" (which actually changes your database)!

Check out the readme for operational details, but my goal is to make the on-screen instructions "good enough" for most users.

I'll post the plugin's download link once I receive it.

Once reviewed, the plugin can be downloaded from https://www.zen-cart.com/downloads.php?do=file&id=2091.

Sounds great. In EP it will list all products in every instance of it when it's been linked to multiple categories which can be inconvenient when making changes. Will this only list the product in the master category or have a setting for that?

Quote:

---

Originally Posted by lankeeyankee

Sounds great. In EP it will list all products in every instance of it when it's been linked to multiple categories which can be inconvenient when making changes. Will this only list the product in the master category or have a setting for that?

---

For a products' import/export, each product is listed one time ... and associated to its master-category-id by a caret (^) separated list of category names (specified in the site's default language).

I'm excited the work might be able to help me with this.
https://www.zen-cart.com/showthread....54#post1312654

Quote:

---

Originally Posted by Zean

I'm excited the work might be able to help me with this.
https://www.zen-cart.com/showthread....54#post1312654

---

You've got three (3) requests in that post:

1. Include, and process, the categories' sort-order in a products' export. DbIo doesn't export that value either for its products' report (it would actually be a set of values, right, since each category has its own sort-order). What bearing does a categories' sort-order have on a product's definition? I can see including the categories' sort-order with a categories I/O handler, but for products?
2. Include sort-orders in the basic attributes' export. DbIo's "basic" products-attributes I/O report does not include those fields, either. For this report, the idea is to keep the processed fields to a minimum.
3. Include sort-orders in the detailed attributes' export. DbIo, like EP-4, includes only the attribute-specific sort order (the v_products_options_sort_order) since that report is dealing with the products_attributes table. The option-specific sort order is included in the ProductsOptions handler's processing and the option-value-specific sort order is included in the ProductsOptionsValues handler.

Quote:

---

Originally Posted by lat9

Once reviewed, the plugin can be downloaded from https://www.zen-cart.com/downloads.php?do=file&id=2091.

---

v1.0.0 is now available for download.

Not sure which way to go to correct my issue. I have this error when I navigate to the plug in.

Quote:

---

The DbIo Manager does not support your installation, due to a mismatch between your DB_CHARSET (utf-8) and CHARSET (utf-8) value.

---

What would be the best way to resolve my mismatch issue to allow me to use the plugin?

Quote:

---

Originally Posted by GearStudios

Not sure which way to go to correct my issue. I have this error when I navigate to the plug in.

Quote:

---

The DbIo Manager does not support your installation, due to a mismatch between your DB_CHARSET (utf-8) and CHARSET (utf-8) value.

---

What would be the best way to resolve my mismatch issue to allow me to use the plugin?

---

You can correct the setting (in your configure.php files) for DB_CHARSET to read

Code:

---

define ('DB_CHARSET', 'utf8');

---

instead of

Code:

---

define ('DB_CHARSET', 'utf-8');

---

There's no intervening minus-sign (-) in that definition.

That's awesome! Fixed the issue! Thank you! +10

Great plugin however when I exported my products it added the source code of my Admin page at the end! Weird ...

Quote:

---

Originally Posted by icecold

Great plugin however when I exported my products it added the source code of my Admin page at the end! Weird ...

---

Not weird, but a bug. If you edit your copy of /YOUR_ADMIN/dbio_manager.php, starting at line 224, find:

Code:

---

                        case 'download':
                            $fp = fopen ($action_filename, 'r');
                            if ($fp === false) {
                                $_SESSION['dbio_message'] = array ( 'error', sprintf (DBIO_CANT_OPEN_FILE, $action_filename) );
                            } else {
                                if (strpos ($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== false) {
                                    header('Content-Type: "application/octet-stream"');
                                    header('Content-Disposition: attachment; filename="' . $dbio_files[$_POST['filename_hash']]['filename_only'] . '"');
                                    header('Expires: 0');
                                    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
                                    header("Content-Transfer-Encoding: binary");
                                    header('Pragma: public');
                                    header("Content-Length: " . $dbio_files[$_POST['filename_hash']]['bytes']);
                                } else {
                                    header('Content-Type: "application/octet-stream"');
                                    header('Content-Disposition: attachment; filename="' . $dbio_files[$_POST['filename_hash']]['filename_only'] . '"');
                                    header("Content-Transfer-Encoding: binary");
                                    header('Expires: 0');
                                    header('Pragma: no-cache');
                                    header("Content-Length: " . $dbio_files[$_POST['filename_hash']]['bytes']);
                                }
                                fpassthru ($fp);
                                fclose ($fp);
                            }
                            break;

---

and change to:

Code:

---

                        case 'download':
                            $fp = fopen ($action_filename, 'r');
                            if ($fp === false) {
                                $_SESSION['dbio_message'] = array ( 'error', sprintf (DBIO_CANT_OPEN_FILE, $action_filename) );
                            } else {
                                if (strpos ($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== false) {
                                    header('Content-Type: "application/octet-stream"');
                                    header('Content-Disposition: attachment; filename="' . $dbio_files[$_POST['filename_hash']]['filename_only'] . '"');
                                    header('Expires: 0');
                                    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
                                    header("Content-Transfer-Encoding: binary");
                                    header('Pragma: public');
                                    header("Content-Length: " . $dbio_files[$_POST['filename_hash']]['bytes']);
                                } else {
                                    header('Content-Type: "application/octet-stream"');
                                    header('Content-Disposition: attachment; filename="' . $dbio_files[$_POST['filename_hash']]['filename_only'] . '"');
                                    header("Content-Transfer-Encoding: binary");
                                    header('Expires: 0');
                                    header('Pragma: no-cache');
                                    header("Content-Length: " . $dbio_files[$_POST['filename_hash']]['bytes']);
                                }
                                fpassthru ($fp);
                                fclose ($fp);
                                exit ();
                            }
                            break;

---

I'll get this queued for the next version's release.

I've just submitted v1.0.1 to the Plugins for review, containing the following changes (identified by their GitHub issue number):

#69: Enable a handler to perform some post-processing for the record-set just imported.
#63: Clarify some of the handlers' instructions.
#68: Missing function importGetFieldValue.
#67: Unwanted HTML included in CSV-download in some configurations.
#60: Missing "Customers" handler's language file.
#62: Only default language exported by "Products" handler on multi-lingual stores.
#64: v_categories_name field not properly encoded by the "Products" export.
#65: Empty dates should be allowed when a date/datetime field is nullable.
#53: Allow a handler to be conditionally enabled.

v1.0.1 is now available for download from the plugins.

I've just submitted v1.1.0 to the Plugins for review. This version of DbIo adds the capability to REMOVE a product's database record and to use either the product's ID or its model-number as a "key" on import.

Changes for the following issues (identified by GitHub issue numbers) are included:

#7: Enable handlers to support "commands" (like REMOVE); the "Products" import now recognizes the REMOVE command.
#70: Fixed-header import misses "special import" fields.
#71: An incomplete import record was not being flagged.
#72: Don't post-process imports that have previously failed.
#73: Enable a handler to specify an alternate key; supports the "Products" import to key to either the product's ID or model-number.
#74: Enable a handler to specify a minimum base-class version.
#75: Don't gather export filters until needed; a performance enhancement for store's with a large number of categories and/or manufacturers.
#78: Correct PHP Notice issues.

What a great module. Very well made. I am having difficulties doing the 'ordersproducts' as I get the blank screen with generic error message (WARNING: An Error occurred.........). Unfotunately nothing is in log files. I do have modified products table, namely extra fields. Perhaps it is. Orders export runs perfect, so the customer details are exporting okay.

@HeathenMagic, I don't understand why no log was generated for that error. That said, you could try enabling the DbIo debug, rerunning that OrdersProducts report and then sending me (it'll be a big file) the resultant log (it'll be the newest file with a name similar to /YOUR_ADMIN/dbio/logs/dbio-OrdersProducts-*.log).

Quote:

---

Originally Posted by lat9

I've just submitted v1.1.0 to the Plugins for review. This version of DbIo adds the capability to REMOVE a product's database record and to use either the product's ID or its model-number as a "key" on import.

Changes for the following issues (identified by GitHub issue numbers) are included:

#7: Enable handlers to support "commands" (like REMOVE); the "Products" import now recognizes the REMOVE command.
#70: Fixed-header import misses "special import" fields.
#71: An incomplete import record was not being flagged.
#72: Don't post-process imports that have previously failed.
#73: Enable a handler to specify an alternate key; supports the "Products" import to key to either the product's ID or model-number.
#74: Enable a handler to specify a minimum base-class version.
#75: Don't gather export filters until needed; a performance enhancement for store's with a large number of categories and/or manufacturers.
#78: Correct PHP Notice issues.

---

v1.1.0 is now available for download.

Quote:

---

Originally Posted by lat9

@HeathenMagic, I don't understand why no log was generated for that error. That said, you could try enabling the DbIo debug, rerunning that OrdersProducts report and then sending me (it'll be a big file) the resultant log (it'll be the newest file with a name similar to /YOUR_ADMIN/dbio/logs/dbio-OrdersProducts-*.log).

---

Slight update: The OrdersProducts report was being run with filters on the "Orders ID" and, unfortunately, that resulted in the error:

Code:

---

PHP Fatal error:  1052:Column 'orders_id' in where clause is ambiguous

---

The correction is to properly "alias" that orders_id field. Along the way, I also found that the OrdersProducts report wasn't passing its output information through the encoding process (so that non-ASCII characters could get "mangled" in the output file), so that's been corrected as well.

I've got the update staged for DbIo's next version (most likely in early January): https://raw.githubusercontent.com/la...ctsHandler.php

Quote:

---

Originally Posted by lat9

Slight update: The OrdersProducts report was being run with filters on the "Orders ID" and, unfortunately, that resulted in the error:

Code:

---

PHP Fatal error:  1052:Column 'orders_id' in where clause is ambiguous

---

The correction is to properly "alias" that orders_id field. Along the way, I also found that the OrdersProducts report wasn't passing its output information through the encoding process (so that non-ASCII characters could get "mangled" in the output file), so that's been corrected as well.

I've got the update staged for DbIo's next version (most likely in early January): https://raw.githubusercontent.com/la...ctsHandler.php

---

I've just submitted v1.1.1 of the DbIo to the Zen Cart plugins for review; I'll post back when it's available.

Quote:

---

Originally Posted by lat9

I've just submitted v1.1.1 of the DbIo to the Zen Cart plugins for review; I'll post back when it's available.

---

It's now available for download.

I've just submitted v1.2.0 of the DbIo to the plugins' area and will post back here when it's available for download.

This version contains a major restructuring of the Tools->Database I/O Manager layout to reduce clutter and make currently-selected handler the "focus" of all actions. It also includes a new concept: export customization. The export-customization, currently available for the Orders and Products handlers, allows you to select only the fields that you "want to see" and the order in which those fields are exported.

The following changes are included, identified by their GitHub repository issue number:

#59: Enable custom exports
#76: Restructure Database I/O Manager layout
#83: Maintain last-selected file's location
#84: Don't automatically render a handler's filters (performance)
#85: Enable download or removal of DbIo-generated logs
#86: Add a handler to support manufacturers' import/export
#87: Ensure that character fields' defined length is enforced
#88: Use a text-link instead of an icon to identify that import results are available
#89: Reduce "nuisance" DbIo logs

v1.2.0 is now available for download from the Zen Cart plugins.

I've just submitted v1.2.1 to the plugins for review. This version contains the changes associated with the following issues (identified by their GitHub issue number):
#91: Back-ticks required around database field names.
#92: Enable special handling for handlers with fixed-field headers.
#93: Check for the php-mbstring extension; message if not installed.

Once approved, you can download the update here: https://www.zen-cart.com/downloads.php?do=file&id=2091

The update is now available for download.

I guess I've just missed the "Install Instructions" link and or txt file.
There IS a "README.MD" but I'm not sure how to open that or What program to use to open the ".MD" readme file.

help?

Within the zip-file, there's a /docs/dbio directory that has a readme.html ... that's what you're looking for!

You've positioned this as a better option to easy populate. I've installed this as part of an upgrade of a clients site because of that. One feature of easy populate that appears to be missing is the ability to add products. Did I miss this? I see the REMOVE function. Is this only planned to update existing products?

Oh and I really like the templates and the ability to create new ones. A suggestion is to add v_dbio_command to the pull down so it can be added to a template

Quote:

---

Originally Posted by badarac

You've positioned this as a better option to easy populate. I've installed this as part of an upgrade of a clients site because of that. One feature of easy populate that appears to be missing is the ability to add products. Did I miss this? I see the REMOVE function. Is this only planned to update existing products?

---

Huh? Not sure which version of EP to which you are referring, but if there is a version of EP that *doesn't* support adding a product, then it is definitely defunct. EP4 does this by providing a new line of data that has a new primary key (default is products_model, but also has the ability to not identify a products_model or use a duplicate products_model and instead use the products_id or whatever is decided to be the primary key).

Quote:

---

Originally Posted by mc12345678

Huh? Not sure which version of EP to which you are referring, but if there is a version of EP that *doesn't* support adding a product, then it is definitely defunct. EP4 does this by providing a new line of data that has a new primary key (default is products_model, but also has the ability to not identify a products_model or use a duplicate products_model and instead use the products_id or whatever is decided to be the primary key).

---

You misunderstood. What I'm saying is that this function is IN EP but not DBIO. I'm asking if that was intentionally left out, if I missed the function in DBIO that's already there, or if it's planned and not yet implemented.

@badarac, there's a Products handler that enables you to import/export any fields present in the products/products_descriptions tables.

Quote:

---

Originally Posted by badarac

Oh and I really like the templates and the ability to create new ones. A suggestion is to add v_dbio_command to the pull down so it can be added to a template

---

Good suggestion; let me see what's up with that.

Quote:

---

Originally Posted by lat9

@badarac, there's a Products handler that enables you to import/export any fields present in the products/products_descriptions tables.

---

The way it seems to work though is it needs an existing product id and/or model. That would seem to indicate that it can only update information for existing products. Am I wrong?

If the product is not found (by id or model), it's added during any import processing. You just need a unique model number to identify the product.

Quote:

---

Originally Posted by lat9

If the product is not found (by id or model), it's added during any import processing. You just need a unique model number to identify the product.

---

I exported the products, deleted all but one row (and the headers), deleted the product id, and made a unique model number. When I import the csv file with the single record it gives an error[*] products.products_id, line #2: Value () is not an integer. Is there a unique integer value that must be entered in the products_id column? Bug?

Installed on 1.5.5e btw. Site is installed in a subdirectory for testing so I can make changes without harming anything if you want me to test a change.

Quote:

---

Originally Posted by badarac

You misunderstood. What I'm saying is that this function is IN EP but not DBIO. I'm asking if that was intentionally left out, if I missed the function in DBIO that's already there, or if it's planned and not yet implemented.

---

Yes, I did misread the statement placing the wrong emPHAsis on something missing from EP instead of something in EP considered missing from DbIO as a variant of EP.

Quote:

---

Originally Posted by badarac

Installed on 1.5.5e btw. Site is installed in a subdirectory for testing so I can make changes without harming anything if you want me to test a change.

---

My guess would be that the products_id column would need to be removed to support adding such a new product or a value of 0 maybe. Otherwise it identifies that there is nothing present as reported.

Quote:

---

Originally Posted by mc12345678

My guess would be that the products_id column would need to be removed to support adding such a new product or a value of 0 maybe. Otherwise it identifies that there is nothing present as reported.

---

One of those was a good guess!:D

To insert a product via import, you'll need to have both the v_products_id and v_products_model fields present in the to-be-imported CSV. Set the products_id value to 0 (not blank) and the products_model to the new product's unique model number.

Quote:

---

Originally Posted by lat9

One of those was a good guess!:D

To insert a product via import, you'll need to have both the v_products_id and v_products_model fields present in the to-be-imported CSV. Set the products_id value to 0 (not blank) and the products_model to the new product's unique model number.

---

And the guess I didn't make. ;-)

Works perfectly when you do that!

Just a quick question before I go much deeper. Is there an option to add/update categories or deos the category structure need to be in place before the file is imported?

There's no separate handler for categories at this time. When you import a product, the category structure will be created if it doesn't already exist.

Quote:

---

Originally Posted by lat9

There's no separate handler for categories at this time. When you import a product, the category structure will be created if it doesn't already exist.

---

That doesn't seem to be working. I tried adding a new product to a category that didn't exist and it gave an error.
[*] Product not inserted at line number 2, no match found for categories_name (Rich).

Hmm, I'll need to investigate further ...

Quote:

---

Originally Posted by lat9

Hmm, I'll need to investigate further ...

---

... v1.3.0-beta1 is available for download from the DbIo's GitHub repository. This version (to be released as v1.3.0) contains the changes necessary for the automatic generation of products' categories on a Products import.

Note that there's a new configuration value available: Configuration->Database I/O Manager Settings->Products: Auto-Create Categories?. The setting defaults to No; set the value to Yes for the auto-generation.
.

Installing it now. I'll let you know if I find any issues. Thanks!!

Still getting the error. I downloaded, copied the admin files, and set the config setting. Same message:

Code:

---

Product not inserted at line number 2, no match found for categories_name (Rich).

---

From the debug log:

Code:

---

2017-04-21 09:34:49: importProcessField (--special--, categories_name, 0, Media^Aqueous^Inkjet Paper^Innova Paper^Rich)
2017-04-21 09:34:49: Products::importAddField (--special--, categories_name, Media^Aqueous^Inkjet Paper^Innova Paper^Rich)
2017-04-21 09:34:49:[*] Product not inserted at line number 2, no match found for categories_name (Rich).

---

I can email you the full debug log if you PM me with your email. Anything I can do just let me know.

Did you change that new configuration setting? It defaults to "No" (i.e. categories not auto-created).

Quote:

---

Originally Posted by lat9

Did you change that new configuration setting? It defaults to "No" (i.e. categories not auto-created).

---

Yes, I set it to "yes"

Quote:

---

Originally Posted by badarac

Yes, I set it to "yes"

---

PM sent.

Quote:

---

Originally Posted by lat9

PM sent.

---

Reply email sent

Code:

---

Creating a category named Rich, with parent_category_id 34

---

Amazing what a difference saying "Yes" makes :cool:

Quote:

---

Originally Posted by badarac

Code:

---

Creating a category named Rich, with parent_category_id 34

---

Amazing what a difference saying "Yes" makes :cool:

---

... Yes!:P

The issue that @badarac found was that I had the wrong value in the conditional checking to see whether (Yes) or not (elsewise) the categories are to be auto-created. Its correction has been posted to the GitHub repository but is not included in the -beta1 version.

In the DbIo exported dB header..... Where would I put "dealer Pricing"? the MSRP retail price is going into the "v_products_price" column.
Do I need to or better yet, CAN I make another column for the Dealer pricing?

I have TWO picture FOLDERS to FTP/upload to the site. (/large_/001-00122.jpg) and (/small_/001-00122.jpg)

What SHOULD I do for the two different size photos for each product...? How do I set that up ?

Thanks,
MrRogue
)

Quote:

---

Originally Posted by MrRogue

In the DbIo exported dB header..... Where would I put "dealer Pricing"? the MSRP retail price is going into the "v_products_price" column.
Do I need to or better yet, CAN I make another column for the Dealer pricing?

I have TWO picture FOLDERS to FTP/upload to the site. (/large_/001-00122.jpg) and (/small_/001-00122.jpg)

What SHOULD I do for the two different size photos for each product...? How do I set that up ?

Thanks,
MrRogue
)

---

The DbIo gives you the opportunity to import and export all database fields for a product. If you've installed the MSRP plugin, for example, any additional fields defined by that plugin will automatically be available for export (and import).

If you create a database field for the dealer pricing, you can manipulate the value using the DbIo but you'll need other code modifications to actually use that value for any display or processing.

Regarding the images, you'll need Image Handler 4 to manage the various sizing of those images. Zen Cart uses a base image name (recorded in the database) and then looks for that name in the various folders; IH4 extends that processing.

I've just submitted v1.3.0 to the Plugins for review, providing changes for the following issues (identified by their GitHub issue number):

#94: Enable a handler to totally control an import
#98: Add (optional) category-creation during a "Products" import
#99: Correct the plugin's initialization for "early adopters".
#100: Manage date_added/last_modified fields during a "Products" import
#102: Display all DbIo option settings on the Database I/O Manager page

I'll post back when it's available for download there.

Quote:

---

Originally Posted by lat9

I've just submitted v1.3.0 to the Plugins for review, providing changes for the following issues (identified by their GitHub issue number):

#94: Enable a handler to totally control an import
#98: Add (optional) category-creation during a "Products" import
#99: Correct the plugin's initialization for "early adopters".
#100: Manage date_added/last_modified fields during a "Products" import
#102: Display all DbIo option settings on the Database I/O Manager page

I'll post back when it's available for download there.

---

DbIo v1.3.0 is now available for download: https://www.zen-cart.com/downloads.php?do=file&id=2091

And yet another great plugin by lat9 :cheers: !!

Found one little snag when testing on a local dev install (1.5.5e): I omitted to make the YOUR_ADMIN/dbio folder writable and of course my test export file was not created.

In case the YOUR_ADMIN/dbio folder is not writable then DbIo should give a warning in the admin when attempting to export to that folder, would avoid some raising of the eyebrows.

I guess that test could be done during the install process of DbIo.

Quote:

---

Originally Posted by frank18

And yet another great plugin by lat9 :cheers: !!

Found one little snag when testing on a local dev install (1.5.5e): I omitted to make the YOUR_ADMIN/dbio folder writable and of course my test export file was not created.

In case the YOUR_ADMIN/dbio folder is not writable then DbIo should give a warning in the admin when attempting to export to that folder, would avoid some raising of the eyebrows.

I guess that test could be done during the install process of DbIo.

---

:oops:Thanks for the report, @frank18, I'll get that logged and included in the next release.

Dear lat9,

I have the same issue as one of the first topics. I receive the following message:
The DbIo Manager does not support your installation, due to a mismatch between your DB_CHARSET (DB_CHARSET) and CHARSET (utf-8) values.

You have given a solution in the beginning, to change the configure.php file (define ('DB_CHARSET', 'utf-8'); into define ('DB_CHARSET', 'utf8');)

But.. I do not have that line into my configure file, only in the dis-configure.php file, but there is already the following line:

define('DB_CHARSET', 'utf8');

, is there an another way to solve this?

Best Regards,

@LadyoftheCave, I just posted on the thread that brought you here ... what version of Zen Cart are you running?

Ohh thank you, I will check :) I have 1.5.4

Hmm, that's not right; for ZC 1.5.4, you should have a DB_CHARSET definition within both /includes/configure.php and /YOUR_ADMIN/includes/configure.php, towards the bottom, with the other database definitions.

You should check your database via phpMyAdmin to make sure that the "collation" setting for the tables is something on the order of utf8_general_ci. If that's the case, then just edit those two files to add the line

Code:

---

define('DB_CHARSET', 'utf8');

---

to each. Remember that the /includes/configure.php file is normally marked read-only, so you'll need to change those permissions before editing!

If the collations show latin1_{something}, then there are more corrections needed.

Converting from iso-8859-1 to utf8

Quote:

---

Originally Posted by LadyoftheCave

Ohh thank you, I will check :) I have 1.5.4

---

I am guessing that you still use an old configure.php set, one maybe from ZC 1.3.9x, which has never been updated. If you do have a configure.php file with the definition as per above, then, yes, it is the correct one and only needs the definition updated.

Quote:

---

Originally Posted by frank18

I am guessing that you still use an old configure.php set, one maybe from ZC 1.3.9x, which has never been updated. If you do have a configure.php file with the definition as per above, then, yes, it is the correct one and only needs the definition updated.

---

It could be, I have updated once from 1.3.9, I will check this, thank you :)

Quote:

---

Originally Posted by lat9

Hmm, that's not right; for ZC 1.5.4, you should have a DB_CHARSET definition within both /includes/configure.php and /YOUR_ADMIN/includes/configure.php, towards the bottom, with the other database definitions.

You should check your database via phpMyAdmin to make sure that the "collation" setting for the tables is something on the order of utf8_general_ci. If that's the case, then just edit those two files to add the line

Code:

---

define('DB_CHARSET', 'utf8');

---

to each. Remember that the /includes/configure.php file is normally marked read-only, so you'll need to change those permissions before editing!

If the collations show latin1_{something}, then there are more corrections needed.

Converting from iso-8859-1 to utf8

---

Hi lat9,

I have added the lines to the configure.php files and now it works like a charm :)

Thank you :smile:

Hi lat9,

Sorry, I have still one more question. You told me that it was possible to select only order files from a certain period, but I cannot find it, could you tell me where I can select this?

Best Regards,

Quote:

---

Originally Posted by LadyoftheCave

Hi lat9,

Sorry, I have still one more question. You told me that it was possible to select only order files from a certain period, but I cannot find it, could you tell me where I can select this?

Best Regards,

---

To export orders based on status, date- or id-range:

• Click Tools->Database I/O Manager
• Choose the Orders handler from the top-most dropdown
• Tick the Show/Hide filters box
• Make your selections from the form fields now exposed.
• Click the Export button

Quote:

---

Originally Posted by lat9

To export orders based on status, date- or id-range:

• Click Tools->Database I/O Manager
• Choose the Orders handler from the top-most dropdown
• Tick the Show/Hide filters box
• Make your selections from the form fields now exposed.
• Click the Export button

---

ahhh that is great, I totally missed that one, thank you :)

Sorry, I am almost there, but still one question :blush: I am trying to upload the excel file into my bookkeeping program.

The bookkeeping program has problems with the date and time. I am trying to change the date_purchased. I thought that it would be in the dbio settings, I changed the dates into the dbio settings but that didn't work. It now shows first the year, then the month and then the day and then the time that the order was placed.
Attachment 17098
Attachment 17099
Is there a way to remove the time that the order has been placed, and show the date as d-m-y instead of y-m-d

In excel, you should be able to select all the cells with the dates, right click and select format cells, choose date, select the date format you prefer, and click ok. And that will get rid of the time. You can create a macro in excel to do this function for you so it's just a matter of a custom keystroke combination. This way you are not overwriting the module's file which will then be overwritten during updates. Lat9 might know a way that you can write an extra_function to do this in zen cart, I have never used this mod so I cannot say.

Hi lankeeyankee, thank you for your reply.

The problem is that if I import de dbio-file into excel, I do not see cells, only lines, so it is unfortunatelly not possible to select cell-columns. Only when I try to import the file into my bookkeepingsoftware, I see cells instead of lines, but these cannot be adjust, and because they cannot be adjust, the bookkeepingsoftware says sorry, cannot import this, the date is wrong.

Quote:

---

Originally Posted by LadyoftheCave

Hi lankeeyankee, thank you for your reply.

The problem is that if I import de dbio-file into excel, I do not see cells, only lines, so it is unfortunatelly not possible to select cell-columns. Only when I try to import the file into my bookkeepingsoftware, I see cells instead of lines, but these cannot be adjust, and because they cannot be adjust, the bookkeepingsoftware says sorry, cannot import this, the date is wrong.

---

I noticed that you're importing the exported CSV into Excel, but you've got the DbIo's "CSV Encoding" set to utf8. Note that using Excel to view/modify the exported CSV normally requires that the DbIo's "CSV Encoding" be set to latin1 (since Excel doesn't understand utf8).

I don't understand your comment about not seeing cells, only lines. What version of Excel are you using?

Quote:

---

...(since Excel doesn't understand utf8)

---

I only use Libreoffice Calc precisely for this reason. Excel's 1970's "handling" (joke) of CSVs, extended character sets, (non) control of import and export formats leaves me speechless that it still has any user base in any country that has accents in its language. It is utter rubbish.

Using Calc will remove some of the unnecessary problems you may come across with Excel.

Off topic-ish
If you want to automate conversions from anything to anything in any format you like (including any db source or destination) whether a known format or your own, I recommend Pentaho Data Integration community.

Thank you all for the info :)

Do I understand it right that there is no way to export the dbio-file with the right date? (d-m-y and without the time), that the only way is to change it manually into a excel-alike program?

Best Regards,

Quote:

---

Originally Posted by LadyoftheCave

Thank you all for the info :)

Do I understand it right that there is no way to export the dbio-file with the right date? (d-m-y and without the time), that the only way is to change it manually into a excel-alike program?

Best Regards,

---

Currently, that is correct. I'll create a change-request (so I remember!) on the plugin's GitHub repository, adding an output-date format selection to the Orders handler.

Thank you :)

Just wanted to tell you that, with a few adaptations in OpenOffice, it works great to import the file into my bookkeeping software :)

I am very happy, :D :D it saves a lot of time

Can anyone offer any advise - im trying to import products from zencart 1.3.9h to zencart v1.5.5e

2 different webhosts also


1.3.9h
Maximum Upload File Size: 32M
Internal Character Encoding: iso-8859-1
Database Character Encoding: DB_CHARSET
Default Language: en

------------------------------------------------------------


v1.5.5e
System Settings
Maximum Upload File Size: 24M
Internal Character Encoding: utf-8
Database Character Encoding: utf8
Default Language:

------------------------------------------

ive managed to upload it but i get the following -

483 errors and 0 warnings. 0 records were inserted or updated.

what can i do to make it work ..is there way? any help greatly appreciated

Double post ..sorry

The following warnings/errors were generated by the above action:


Data record at line #2 not imported. Column count (42) less than header column count (43)

That means your database tables don't match. One has 42 columns and the other has 43. So you need to compare the tables to see what the missing column is. I believe your new database has the extra column based on the message.

@boomy, you don't mention your PHP or DbIo version, but ...

... after an import is completed, there's a dbio log file generated with the same name as your input csv; that .log file (also accessible from the DbIo console) will identify what were considered errors.

Quote:

---

Originally Posted by boomy

The following warnings/errors were generated by the above action:


Data record at line #2 not imported. Column count (42) less than header column count (43)

---

That message implies that your import-CSV file identifies 43 columns in the header row (the first row) but only 42 rows in data.

Quote:

---

Originally Posted by frank18

And yet another great plugin by lat9 :cheers: !!

Found one little snag when testing on a local dev install (1.5.5e): I omitted to make the YOUR_ADMIN/dbio folder writable and of course my test export file was not created.

In case the YOUR_ADMIN/dbio folder is not writable then DbIo should give a warning in the admin when attempting to export to that folder, would avoid some raising of the eyebrows.

I guess that test could be done during the install process of DbIo.

---

I've got the change for this issue staged on the plugin's GitHub repository; it will be included in the plugin's 1.3.1 release.

1.5.5e / 1.3.0 - "Products: Auto-Create Categories on Import?" option set to "true". I created a custom product template to match the fields in an Easy Populate CSV from an old OSCommerce site I'm updating. I tested it by exporting the sample data and found a couple of problems.

Under the "v_categories_name" heading, all that's listed is a '1' for each product. However; under the v_products_status heading, instead of the expected "Active" or "Inactive", it has the category hierarchy for each product, separated by a caret --> Women^Bottoms^Jeans^Image Gallery.

Template creation is pretty straight forward so it's likely a bug but if there's a way to resolve it I'd appreciate the help.

Quote:

---

Originally Posted by Johnies

1.5.5e / 1.3.0 - "Products: Auto-Create Categories on Import?" option set to "true". I created a custom product template to match the fields in an Easy Populate CSV from an old OSCommerce site I'm updating. I tested it by exporting the sample data and found a couple of problems.

Under the "v_categories_name" heading, all that's listed is a '1' for each product. However; under the v_products_status heading, instead of the expected "Active" or "Inactive", it has the category hierarchy for each product, separated by a caret --> Women^Bottoms^Jeans^Image Gallery.

Template creation is pretty straight forward so it's likely a bug but if there's a way to resolve it I'd appreciate the help.

---

Hmm, it sounds like (somehow) the v_categories_name data and the v_products_status data got reversed (note that the products_status will show either 0 or 1, not "Inactive" or "Active") in the export.

Would you share, via image, the configuration of the template that you've set up ... that might help me to reproduce the issue.

Quote:

---

Originally Posted by lat9

Hmm, it sounds like (somehow) the v_categories_name data and the v_products_status data got reversed (note that the products_status will show either 0 or 1, not "Inactive" or "Active") in the export.

Would you share, via image, the configuration of the template that you've set up ... that might help me to reproduce the issue.

---

Thanks for the info. Shows how observant I am ...I forgot that zen cart used 1's and 0's for active/inactive. Here's an image of the template.

Attachment 17308

No clue what would cause the category and status details to swap so I appreciate any help you can offer. ~Johnie

I don't know what you attached, but the forum software found it "unacceptable". Please give it another try.

Quote:

---

Originally Posted by lat9

I don't know what you attached, but the forum software found it "unacceptable". Please give it another try.

---

Sorry about that. It's just a JPEG using the default image importer. Attachment 17311

misfire

Thanks for that; I've replicated the issue on a local testbed and should have a solution in a couple of days.

Quote:

---

Originally Posted by lat9

Thanks for that; I've replicated the issue on a local testbed and should have a solution in a couple of days.

---

Thanks!

Goodevening all,

I have a question, is it also possible to add new products (in a csv-file list) through dbio manager to the webstore?

Quote:

---

Originally Posted by LadyoftheCave

Goodevening all,

I have a question, is it also possible to add new products (in a csv-file list) through dbio manager to the webstore?

---

Yes, it's quite possible. I suggest that you export (to see the column headings) and go from there.

Be sure to make a database backup!

That is great news, I was busy installing easy populate for it, but then I realized that it was maybe also possible with db i/o manager :)

Goodmorning all,

I have a question about importing csv-files with new products. I have downloaded a product-csv file with the dbio manager, and then I changed the products and I have uploaded it to the webstore throught the dbio manager, but then I received the following message:

Code:

---

Foutmelding The current import-file is missing these (v_products_id, v_products_model) required columns; the import is disallowed.

---

But when I check the file on the missing columns, they are just there into the file..

Attachment 17326

Quote:

---

Originally Posted by LadyoftheCave

Goodmorning all,

I have a question about importing csv-files with new products. I have downloaded a product-csv file with the dbio manager, and then I changed the products and I have uploaded it to the webstore throught the dbio manager, but then I received the following message:

Code:

---

Foutmelding The current import-file is missing these (v_products_id, v_products_model) required columns; the import is disallowed.

---

But when I check the file on the missing columns, they are just there into the file..

Attachment 17326

---

I suspect you need to add a value for "v_products_model" to your products

Hi design75. I have tried to add 0, but it didn't worked..