OsCommerce Data Importer - Errors

osCommerce Online Merchant v2.2 RC1 exported to Zen Cart 1.3.9g
Clean install
No Addons - French and English Language installed on both OSC and Zen

Hi Thanks for your add on !

I have successfully exported my osc database but for some reason I can not get French product descriptions to show up .......It looks like the data was imported ok (it shows the tables) but does not show up if I select French language from the admin ? In my database the language code for French is 4 and English is 1

please help ........any Ideas thoughts gratefully received :frusty:

Thanks

Alex

alexoverton:

osCommerce Online Merchant v2.2 RC1 exported to Zen Cart 1.3.9g
Clean install
No Addons - French and English Language installed on both OSC and Zen

Hi Thanks for your add on !

I have successfully exported my osc database but for some reason I can not get French product descriptions to show up .......It looks like the data was imported ok (it shows the tables) but does not show up if I select French language from the admin ? In my database the language code for French is 4 and English is 1

please help ........any Ideas thoughts gratefully received :frusty:

Thanks

Alex

No change is made on the language codes.

Look at your osc languages table and see what is the value of your languages_id (1 is normally English, 2 is Deutsch, 3 is Español ) so what value is French 4 ?

After you check and see what value French 4 is look at products_description table. You should find that the language_id has the same value.

Now all you need to do is in the new zen cart languages table make a record that matches the languages_id in you products table languages_id.

Skip

First let me vigorously agree, fantastic life-saving script! Many many thanks!

I am having a similar trouble with my address book as previously described. I have changed all my address_book_id entries to unique sequential numbers. All the data is there (in database), but I cant see it through admin > customers. All other imports went fine.

OSC was set up back in 2002, although it was updated to v2.2 RC2a. ZC is 1.3.9h. PHP 5.2.14 and Linux OS.

Another symptom, when I log in as a previously stored customer in my new zen-cart store, my shipping address is a ","

Any clue? Thanks,
Achyuta

achyuta:

First let me vigorously agree, fantastic life-saving script! Many many thanks!

I am having a similar trouble with my address book as previously described. I have changed all my address_book_id entries to unique sequential numbers. All the data is there (in database), but I cant see it through admin > customers. All other imports went fine.

OSC was set up back in 2002, although it was updated to v2.2 RC2a. ZC is 1.3.9h. PHP 5.2.14 and Linux OS.

Another symptom, when I log in as a previously stored customer in my new zen-cart store, my shipping address is a ","

Any clue? Thanks,
Achyuta

Is this what you are referring to?
http://www.zen-cart.com/forum/showpost.php?p=758101&postcount=98

What you should look for is customers ID in the customers table and then find that customers ID in the address_book table and see if that address goes with that customer.

Also in the customers table you will find customers_default_address_id field the value in this field matches the address_book table field address_book_id.

The osc data is skewed some how you just need to find out where.

Skip

skipwater:

Is this what you are referring to?
http://www.zen-cart.com/forum/showpost.php?p=758101&postcount=98

What you should look for is customers ID in the customers table and then find that customers ID in the address_book table and see if that address goes with that customer.

Also in the customers table you will find customers_default_address_id field the value in this field matches the address_book table field address_book_id.

The osc data is skewed some how you just need to find out where.

Skip

That is what I was referring to, and your suggestions have corrected the problem.

I ran this code from my PHPAdmin: ```
UPDATE customers,address_book SET customers.customers_default_address_id=address_book.address_book_id WHERE customers.customers_id=address_book.customers_id;

---Achyuta

skipwater:

No change is made on the language codes.

Look at your osc languages table and see what is the value of your languages_id (1 is normally English, 2 is Deutsch, 3 is Español ) so what value is French 4 ?

After you check and see what value French 4 is look at products_description table. You should find that the language_id has the same value.

Now all you need to do is in the new zen cart languages table make a record that matches the languages_id in you products table languages_id.

Skip

I am also having the same problem, that we can transfer datas for English and German, but it's not working for the Dutch.

I am confused as to where I can find the values of the languages? Can someone point me in the right direction??

thank you

okay we found it. Changed the values in the MySQL

All working now :D

Dustie:

I am also having the same problem, that we can transfer datas for English and German, but it's not working for the Dutch.

I am confused as to where I can find the values of the languages? Can someone point me in the right direction??

thank you

1. In ZC check language setting
   admin -> localization -> language
   Is your language selected as default?

If 1 is true goto 2

2. Open the osc data base and look at the languages table now what is the value
   of your languages_id (1 is normally English, 2 is Deutsch, 3 is Español ) so
   what value is the language you need?

   Next look at products_description table. You should find the language_id
   field has the same value.

Example: languages table - field languages_id = 3
products_description table - field languages_id = 3

After you know the language value # open the ZC data base.

Open ZC languages table is there a record for your language?

Yes - And the language_id field is the correct value and the products_description table
languages_id field is correct.

No - Then add a record that matches the languages_id in you products table languages_id.
Note: The laguages table languages_id field is auto incremented this means that if
the languages_id you need = 3 and you only have one record you will need to add a dummy
record for 2 and then add record 3 that is your language.

Skip

Firstly, thank you for this script.

I ran it and got no errors, but the products are not showing up in Zencart. They show up in phpmyadmin, but not in the admin of zencart, nor in the shop.

I didn't get any errors...so any idea where to start?

Many thanks

Zen Cart Product Optional Settings
Set Product Review - Pending or Approved --
Transfer Product Specials - YES or NO --

Exporting Categories ...
..................................................50 -|

Records Exported 50

Importing Categories ...
..................................................50 -|

Records Imported 50
Exporting Categories Description ...
..................................................50 -|

Records Exported 50

Importing Categories Description ...
..................................................50 -|

Records Imported 50
Exporting Products ...
......................................................................................................................................................................166 -|

Records Exported 166

Importing Products ...
......................................................................................................................................................................166 -|

Records Imported 166
Exporting Products Description ...
......................................................................................................................................................................166 -|

Records Exported 166

Importing Products Description ...
......................................................................................................................................................................166 -|

Records Imported 166
Exporting Products to Categories ...
..........................................................................................................................................................................170 -|

Records Exported 170

Importing Products to Categories ...
..........................................................................................................................................................................170 -|

Records Imported 170
Exporting Products Options ...
.1 -|

Records Exported 1

Importing Products Options ...
.1 -|

Records Imported 1
Exporting Products Options Values ...
.2 -|

Records Exported 2

Importing Products Options Values ...
..2 -|

Records Imported 2
Exporting Products Options Values to Products Options ...
.1 -|

Records Exported 1

Importing Products Options Values to Products Options ...
.1 -|

Records Imported 1
Exporting Products Attributes ...
.1 -|

Records Exported 1

Importing Products Attributes ...
.1 -|

Records Imported 1
Exporting Products Attributes Download ...
.1 -|

Records Exported 1

Importing Products Attributes Download ...
.1 -|

Records Imported 1
Exporting Products Notifications ...
.......................................................................................................................................................................................................200 -|
.......................................................................................................................................................................................................400 -|
...........................................................................475 -|

Records Exported 475

Importing Products Notifications ...
.......................................................................................................................................................................................................200 -|
.......................................................................................................................................................................................................400 -|
...........................................................................475 -|

Records Imported 475
Exporting Manufacturers ...
...3 -|

Records Exported 3

Importing Manufacturers ...
...3 -|

Records Imported 3
Exporting Manufacturers Info ...
...3 -|

Records Exported 3

Importing Manufacturers Info ...
...3 -|

Records Imported 3
Exporting Reviews ...
...............15 -|

Records Exported 15

Importing Reviews ...
...............15 -|

Records Imported 15
Exporting Reviews Description ...
...............15 -|

Records Exported 15

Importing Reviews Description ...
...............15 -|

Records Imported 15
Exporting Languages ...
.1 -|

Records Exported 1

Importing Languages ...
.1 -|

Records Imported 1
Exporting Specials ...
0 -|

Records Exported 0

Importing Specials ...

Warning: Variable passed to each() is not an array or object in /home/aurora/public_html/loja/import_osc_products.php on line 1502
0 -|

Records Imported 0
Exporting Tax Class ...
.1 -|

Records Exported 1

Importing Tax Class ...
.1 -|

Records Imported 1
Exporting Tax Rates ...
.1 -|

Records Exported 1

Importing Tax Rates ...
.1 -|

Records Imported 1
DONE

xanthous:

Firstly, thank you for this script.

I ran it and got no errors, but the products are not showing up in Zencart. They show up in phpmyadmin, but not in the admin of zencart, nor in the shop.

I didn't get any errors...so any idea where to start?

Many thanks

1. Is your zen cart set to use the data base you are looking at in phpmyadmin?

2. Is your zen cart setup to use a table prefix?

3. Looking at the data in phpmyadmin are you seeing a table prefix? ie. zen_customers or no prefix customers

Skip

It's been a while since I did this but in Zen Cart, each product has to have a master category. When I did my import from osc, osc did not have a master category for products so I had to add a master category to each product.

You might be able to do it using the utility at the bottom of the multiple categories link manager screen.

If not, then use mysql to give every product a master category. Once it has a master category, it should appear in the listings and you can move the products to another master category.

Hope this helps,

Ed

Yes to all.
In the admin, I can see the customers from the import, so I believe everything is properly configured.

It may have more to do with what edadk said.

Although, with the import, came the 20+ categories from the osCommerce install.

I didn't see in the zen_products any indication to a master category id.

I'm willing to give login details if there is time/interest to have a look.

Many thanks. (and Merry Christmas :hug:)

skipwater:

1. Is your zen cart set to use the data base you are looking at in phpmyadmin?

2. Is your zen cart setup to use a table prefix?

3. Looking at the data in phpmyadmin are you seeing a table prefix? ie. zen_customers or no prefix customers

Skip

The products table has a field for master category id. If that's zero, then the product won't show up in the listings.

Assuming you can play (back up first anyway), go into phpmyadmin and check that field. If they are all zero, then change 1 product so that it has a master category which matches any of your existing category id's and then save the record.

If that works, then just run a query changing all the products to any master category, then you'll be able to administer them.

Ed

In the osCom. setup, there were about 50 cat/sub-cat.

In the import, they show up in the DB, but not in admin or on the live site.

I created a new category in admin, and assigned the ID of it to a product, but it still didn't show up....in admin or site.

edadk:

The products table has a field for master category id. If that's zero, then the product won't show up in the listings.

Assuming you can play (back up first anyway), go into phpmyadmin and check that field. If they are all zero, then change 1 product so that it has a master category which matches any of your existing category id's and then save the record.

If that works, then just run a query changing all the products to any master category, then you'll be able to administer them.

Ed

Your master category id's were all zero? If so then you're on the right track. If not, don't make any more changes of this sort.

I think the master category has to match one of the categories that the product is actually in. It's a little confusing, products are in one or more categories, and one of those categories needs to be the master category.

Try this on a product where you already know what regular category it is in, then add that category as the master category in the products table.

Without trying to be obvious, also make sure that both the category and product you are working with are not offline. Since you can't see them in admin, you'll have to look at the tables.

Ed

In the few I checked, of the 165 products, master ID is 0. I assume the rest are too.

Funny thing is, now the new category I made disapeared, and it now says: -> (141)
(http://auroraproducoes.com/loja/)

"offline" ...would that be products_status ? It's set to 0

Not sure if this is any help:
(trimmed)

-- phpMyAdmin SQL Dump
-- version 2.11.11
-- http://www.phpmyadmin.net

-- Host: localhost
-- Generation Time: Dec 26, 2010 at 09:55 PM
-- Server version: 4.1.22
-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: aurora_zc1

---

--
-- Table structure for table zen_products

CREATE TABLE IF NOT EXISTS zen_products (
products_id int(11) NOT NULL auto_increment,
products_type int(11) NOT NULL default '1',
products_quantity float NOT NULL default '0',
products_model varchar(32) default NULL,
products_image varchar(64) default NULL,
products_price decimal(15,4) NOT NULL default '0.0000',
products_virtual tinyint(1) NOT NULL default '0',
products_date_added datetime NOT NULL default '0001-01-01 00:00:00',
products_last_modified datetime default NULL,
products_date_available datetime default NULL,
products_weight float NOT NULL default '0',
products_status tinyint(1) NOT NULL default '0',
products_tax_class_id int(11) NOT NULL default '0',
manufacturers_id int(11) default NULL,
products_ordered float NOT NULL default '0',
products_quantity_order_min float NOT NULL default '1',
products_quantity_order_units float NOT NULL default '1',
products_priced_by_attribute tinyint(1) NOT NULL default '0',
product_is_free tinyint(1) NOT NULL default '0',
product_is_call tinyint(1) NOT NULL default '0',
products_quantity_mixed tinyint(1) NOT NULL default '0',
product_is_always_free_shipping tinyint(1) NOT NULL default '0',
products_qty_box_status tinyint(1) NOT NULL default '1',
products_quantity_order_max float NOT NULL default '0',
products_sort_order int(11) NOT NULL default '0',
products_discount_type tinyint(1) NOT NULL default '0',
products_discount_type_from tinyint(1) NOT NULL default '0',
products_price_sorter decimal(15,4) NOT NULL default '0.0000',
master_categories_id int(11) NOT NULL default '0',
products_mixed_discount_quantity tinyint(1) NOT NULL default '1',
metatags_title_status tinyint(1) NOT NULL default '0',
metatags_products_name_status tinyint(1) NOT NULL default '0',
metatags_model_status tinyint(1) NOT NULL default '0',
metatags_price_status tinyint(1) NOT NULL default '0',
metatags_title_tagline_status tinyint(1) NOT NULL default '0',
PRIMARY KEY (products_id),
KEY idx_products_date_added_zen (products_date_added),
KEY idx_products_status_zen (products_status),
KEY idx_products_date_available_zen (products_date_available),
KEY idx_products_ordered_zen (products_ordered),
KEY idx_products_model_zen (products_model),
KEY idx_products_price_sorter_zen (products_price_sorter),
KEY idx_master_categories_id_zen (master_categories_id),
KEY idx_products_sort_order_zen (products_sort_order),
KEY idx_manufacturers_id_zen (manufacturers_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=300 ;

--
-- Dumping data for table zen_products

INSERT INTO zen_products (products_id, products_type, products_quantity, products_model, products_image, products_price, products_virtual, products_date_added, products_last_modified, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id, products_ordered, products_quantity_order_min, products_quantity_order_units, products_priced_by_attribute, product_is_free, product_is_call, products_quantity_mixed, product_is_always_free_shipping, products_qty_box_status, products_quantity_order_max, products_sort_order, products_discount_type, products_discount_type_from, products_price_sorter, master_categories_id, products_mixed_discount_quantity, metatags_title_status, metatags_products_name_status, metatags_model_status, metatags_price_status, metatags_title_tagline_status) VALUES
(205, 1, 20, 'CMC001', 'startearlyvalues.image1.smallest.jpg', 34.9000, 0, '2009-10-19 21:40:30', '2010-02-03 03:08:42', '0000-00-00 00:00:00', 0.35, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0.0000, 0, 1, 0, 0, 0, 0, 0),
(206, 1, 20, 'CMC002', '176_Comece cedo.jpg', 34.9000, 0, '2009-10-19 22:17:11', '2010-02-03 03:15:12', '0000-00-00 00:00:00', 0.35, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0.0000, 0, 1, 0, 0, 0, 0, 0),
(207, 1, 48, 'CAJ001', 'ComAmorJesus.jpg', 29.9000, 0, '2010-01-13 18:21:59', '2010-11-16 22:57:05', '0000-00-00 00:00:00', 0.3, 0, 0, 11, 2, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0.0000, 0, 1, 0, 0, 0, 0, 0),
(208, 1, 49, 'CAJ012', 'P1130110 cropped.jpg', 29.9000, 0, '2010-01-13 19:28:37', '2010-01-13 21:24:56', '0000-00-00 00:00:00', 0.3, 1, 0, 11, 1, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0.0000, 0, 1, 0, 0, 0, 0, 0),
(209, 1, 50, 'CAJ004', 'P1130112.JPG', 29.9000, 0, '2010-01-13 19:50:13', '2010-11-16 22:59:57', '0000-00-00 00:00:00', 0.3, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0.0000, 0, 1, 0, 0, 0, 0, 0),

If your categories aren't showing up in admin or the front end, the issue may be more complex than just master category id's for the products.

I was originally going to suggest trying the Reset button for category id's which can be found in Tools - Store Manager but that may not be a good idea at this point.

I used a different contrib to do my import, so maybe my suggestions won't work.

Ed

Importing products for the second time. I already imported my product about 6 months ago. I have been working parttime on my Zen Cart Store. I need to import about 200 new products to Zen Cart. However, I do not want to import any images at all. How do I go about doing this. I have image handler installed and when I import the images I have to go in and change images back to my handler images (what a pain). I do not want the images imported at all.

Thanks,
Kaddie

I tried to edit my post but could not. Here is a better description of my issue.

Importing products for the second time. I already imported about 2600 products about 6 months ago. I have been working parttime on my Zen Cart Store. I need to import about 200 more new products and its options and attributes to Zen Cart. However, I do not want to import any images at all. How do I go about doing this. I have image handler installed and all images complete for each product, However, when I import the product images they are over written and change what I have already done. I have to go in and change images back to my handler images (what a pain). I do not want the images imported at all.

Thanks,
Kaddie

kaddie:

I tried to edit my post but could not. Here is a better description of my issue.

Importing products for the second time. I already imported about 2600 products about 6 months ago. I have been working parttime on my Zen Cart Store. I need to import about 200 more new products and its options and attributes to Zen Cart. However, I do not want to import any images at all. How do I go about doing this. I have image handler installed and all images complete for each product, However, when I import the product images they are over written and change what I have already done. I have to go in and change images back to my handler images (what a pain). I do not want the images imported at all.

Thanks,
Kaddie

Kaddie

OsCommerce Data Importer imports the image files names not the true image files.

If you do not want to have the image file names transferred.

Open file import_osc_products.php around line 505 you will find:

/********** start of import of products */
        target_zen(); // Get target db info
        echo '<br /><b>Importing Products ...</b><br />';
        $idot = 0;
        $ii   = 0;
        if (count($import_products) > 0) { // Test empty
            mysql_query("TRUNCATE `" . $target_db_table_prefix . "products`");
            while (list($i, ) = each($import_products)) {
                $insert_products = "INSERT INTO " . $target_db_table_prefix . "products SET
      			products_id = '" . $import_products[$i]['products_id'] . "', 
      			products_quantity = '" . $import_products[$i]['products_quantity'] . "', 
      			products_model = '" . $import_products[$i]['products_model'] . "', 
      			products_image = '" . $import_products[$i]['products_image'] . "', 
      			products_price = '" . $import_products[$i]['products_price'] . "', 
      			products_date_added = '" . $import_products[$i]['products_date_added'] . "', 
      			products_last_modified = '" . $import_products[$i]['products_last_modified'] . "', 
      			products_date_available = '" . $import_products[$i]['products_date_available'] . "', 
      			products_weight = '" . $import_products[$i]['products_weight'] . "', 
      			products_status = '" . $import_products[$i]['products_status'] . "', 
      			products_tax_class_id = '" . $import_products[$i]['products_tax_class_id'] . "', 
      			manufacturers_id = '" . $import_products[$i]['manufacturers_id'] . "', 
      			products_ordered = '" . $import_products[$i]['products_ordered'] . "'  
 			";

Now just delete line 517

products_image = '" . $import_products[$i]['products_image'] . "', 

as shown below:

 /********** start of import of products */
        target_zen(); // Get target db info
        echo '<br /><b>Importing Products ...</b><br />';
        $idot = 0;
        $ii   = 0;
        if (count($import_products) > 0) { // Test empty
            mysql_query("TRUNCATE `" . $target_db_table_prefix . "products`");
            while (list($i, ) = each($import_products)) {
                $insert_products = "INSERT INTO " . $target_db_table_prefix . "products SET
      			products_id = '" . $import_products[$i]['products_id'] . "', 
      			products_quantity = '" . $import_products[$i]['products_quantity'] . "', 
      			products_model = '" . $import_products[$i]['products_model'] . "',  
      			products_price = '" . $import_products[$i]['products_price'] . "', 
      			products_date_added = '" . $import_products[$i]['products_date_added'] . "', 
      			products_last_modified = '" . $import_products[$i]['products_last_modified'] . "', 
      			products_date_available = '" . $import_products[$i]['products_date_available'] . "', 
      			products_weight = '" . $import_products[$i]['products_weight'] . "', 
      			products_status = '" . $import_products[$i]['products_status'] . "', 
      			products_tax_class_id = '" . $import_products[$i]['products_tax_class_id'] . "', 
      			manufacturers_id = '" . $import_products[$i]['manufacturers_id'] . "', 
      			products_ordered = '" . $import_products[$i]['products_ordered'] . "'  
 			";
 

This will leave image file name field blank.
Skip