OsCommerce Data Importer - Errors

[JeremyToaster]

Thanks for making the updates, when I ran this months ago on my test server, I had to manually remove all the apostrophes in my database...took a long time, but eventually everything worked just fine.

What I am trying to do now though is, add the "Past / CUrrent / Pending Orders" which I think is very critical to converting over to ZenCart.

I am trying to figure this part out on my own, and this is what I have come up with so far.
The only problem I see is that, OsCommerce has an "orders_session_info" table and ZenCart does not, and ZenCart has an "orders_products" table and OsCommerce does not. But all the others seem to match up.
If someone with some expertise can look this over and make any corrections and let me know if it could be patched into the script that would be made of awesome!

Thanks

jeremy

$query_orders = "SELECT * FROM orders";
$orders = mysql_query($query_orders);
$i = 0;
echo 'exporting orders';
while ($export_record = mysql_fetch_assoc($orders)) {
    $import_orders[$i]['orders'] =  $export_record['orders'];
//    $import_orders[$i]['orders_products'] =  $export_record['orders_products'];
    $import_orders[$i]['orders_products_attributes'] =  $export_record['orders_products_attributes'];
    $import_orders[$i]['orders_products_download'] =  $export_record['orders_products_download'];
//    $import_orders[$i]['orders_session_info'] =  $export_record['orders_session_info'];
    $import_orders[$i]['orders_status'] =  $export_record['orders_status'];
    $import_orders[$i]['orders_status_history'] =  $export_record['orders_status_history'];
    $import_orders[$i]['orders_total'] =  $export_record['orders_total'];
    $i++;
    echo '.';
}
mysql_free_result($orders);

echo '<br><br>';

echo 'importing orders';
mysql_query("TRUNCATE `orders`");
while (list($i, ) = each($import_orders)) {
    $insert_orders = "INSERT INTO zen_orders SET
    zen_orders = '" . $import_orders[$i]['orders'] . "', 
//    zen_orders_products = '" . $import_orders[$i]['orders_products'] . "',
     zen_orders_products_attributes = '" . $import_orders[$i][''orders_products_attributes'] . "', 
     zen_orders_products_download = '" . $import_orders[$i]['orders_products_download'] . "', 
//    zen_orders_sessions_info = '" . $import_orders[$i]['orders_session_info'] . "',
     zen_orders_status = '" . $import_orders[$i]['orders_status'] . "', 
     zen_orders_status_history = '" . $import_orders[$i]['orders_status_history'] . "', 
     zen_orders_total = '" . $import_orders[$i]['orders_total'] . "',  
    ";
    $success = mysql_query($insert_orders);
    if (!$success) { echo mysql_error(); exit;}
    echo '.';
}
    
echo '<br><br>'; 

[albertsavage]

Thanks for making the updates, when I ran this months ago on my test server, I had to manually remove all the apostrophes in my database...took a long time, but eventually everything worked just fine.

What I am trying to do now though is, add the "Past / CUrrent / Pending Orders" which I think is very critical to converting over to ZenCart.

I am trying to figure this part out on my own, and this is what I have come up with so far.
The only problem I see is that, OsCommerce has an "orders_session_info" table and ZenCart does not, and ZenCart has an "orders_products" table and OsCommerce does not. But all the others seem to match up.
If someone with some expertise can look this over and make any corrections and let me know if it could be patched into the script that would be made of awesome!

Thanks

jeremy

$query_orders = "SELECT * FROM orders";
$orders = mysql_query($query_orders);
$i = 0;
echo 'exporting orders';
while ($export_record = mysql_fetch_assoc($orders)) {
    $import_orders[$i]['orders'] =  $export_record['orders'];
//    $import_orders[$i]['orders_products'] =  $export_record['orders_products'];
    $import_orders[$i]['orders_products_attributes'] =  $export_record['orders_products_attributes'];
    $import_orders[$i]['orders_products_download'] =  $export_record['orders_products_download'];
//    $import_orders[$i]['orders_session_info'] =  $export_record['orders_session_info'];
    $import_orders[$i]['orders_status'] =  $export_record['orders_status'];
    $import_orders[$i]['orders_status_history'] =  $export_record['orders_status_history'];
    $import_orders[$i]['orders_total'] =  $export_record['orders_total'];
    $i++;
    echo '.';
}
mysql_free_result($orders);

echo '<br><br>';

echo 'importing orders';
mysql_query("TRUNCATE `orders`");
while (list($i, ) = each($import_orders)) {
    $insert_orders = "INSERT INTO zen_orders SET
    zen_orders = '" . $import_orders[$i]['orders'] . "', 
//    zen_orders_products = '" . $import_orders[$i]['orders_products'] . "',
     zen_orders_products_attributes = '" . $import_orders[$i][''orders_products_attributes'] . "', 
     zen_orders_products_download = '" . $import_orders[$i]['orders_products_download'] . "', 
//    zen_orders_sessions_info = '" . $import_orders[$i]['orders_session_info'] . "',
     zen_orders_status = '" . $import_orders[$i]['orders_status'] . "', 
     zen_orders_status_history = '" . $import_orders[$i]['orders_status_history'] . "', 
     zen_orders_total = '" . $import_orders[$i]['orders_total'] . "',  
    ";
    $success = mysql_query($insert_orders);
    if (!$success) { echo mysql_error(); exit;}
    echo '.';
}
    
echo '<br><br>'; 

Jeremy,

Couple of things:
1. the RC1 OSC did not have an orders_sessions_info table, so I didn't have it in mine. I doubt you will need this unless you are expecting customers to have products saved in their session during the conversion... coincidentally, all of your sessions will have to be new with Zen when you go live anyway. I would suspect you don't need this table at all.
2. I wouldn't know where to begin if your OSC installation does not have a orders_products table. All RC1 installations should have it. If there are no source records, that's a different story and the script will simply bypass that table 'cause there's nothing to migrate. I can't for the life of me imagine how it would happen, so I can't really comment on it. Yours must be a completely customized application if that's the case.
3. The script dumps everything in the Zen target tables it copies into, so beware, do backups before and after and check it only on a test system before doing a migration. Therefore, all closed, open, pending, and all of your order statuses will be migrated. This is important because if you have payment modules defaulted to order statuses, make sure they are properly updated after the migration to the OSC statuses. Basically, after testing, and setting up your Zen install in production, you can do a final customer and orders cutover, which is why I separated these out into separate migrations in the latest release.

[JeremyToaster]

Hey Albert,
Yes definitely makes sense not needing the sessions_info table.

I may have just over looked the orders_products table, I'll check again and see if it is there. I'm sure it is, as my installation is very standard with only Visual customization.

So do you think the queries I wrote are correct other than the two issues mentioned?
This is my first time to write SQL queries.
If they are correct, would you want to integrate them into the script you published?

jeremy

[albertsavage]

Jeremy,

I'll admit... I can't make heads or tails of your code, and I'm not sure what you're trying to do, so I can't really see the vision. It appears as if you're declaring individual tables, but clearing a specific table (orders). Either way, there are no fields used.

Can you please try to explain what you were trying to do?

Maybe my instructions weren't clear enough on the original, the way the scripts are intended to be used is that at the beginning of working on your new site, you can make a copy of your current OSC database using all 3 import scripts (products, customers, orders), but people often want to change the products information during the time of the migration to reflect new content, meta tags, pricing, descriptions whatever, so I split out the customers and orders into separate files. When you go live with your new site, you can re-run the customers and orders script to get the most up to the second information after you bring your Zen site up.

I would appreciate you explaining what the current script is deficient in the migration so that it can be improved upon.

Since the scripts can be run in seconds (rather than minutes or hours), you can be up and running with your new Zen site nearly instantly after copying the site files up.

In the end, test, test, test. I went through probably 50 iterations before I felt that I could comfortably release the code that I did. I don't yet get the sense that this has been tested, can you confirm?

Best wishes,
Albert

Hey Albert,
Yes definitely makes sense not needing the sessions_info table.

I may have just over looked the orders_products table, I'll check again and see if it is there. I'm sure it is, as my installation is very standard with only Visual customization.

So do you think the queries I wrote are correct other than the two issues mentioned?
This is my first time to write SQL queries.
If they are correct, would you want to integrate them into the script you published?

jeremy

[JeremyToaster]

Hey Albert,
I must just have an old version of your script or something, because the script I have exports only these details

Customers
Address Book
Categories
Category Descriptions
Products
Product Descriptions
Directory Structure
Products Options
Products Options Value
Products Options Values To Products Options
Products Attributes
Specials
Reviews
Review Descriptions


And that's it, there is no exportation of Orders anywhere. That's why I was copying your format for "Customers" and just replacing everything with "Orders" for corresponding tables in OsC and ZenCart.

If you have a new script that exports Orders, Past, Current, Pending, Shipped, etc, please point me to it as that would be great ! and my hacking would be unnecessary :)

[albertsavage]

Yeah, I think you have the old one developed by Netsparx. It was a pain for me so I just rewrote the entire thing. You can find it in the downloads section:

http://www.zen-cart.com/index.php?ma...roducts_id=918

I added, modified, you name it. I just pretty much rewrote the whole thing to handle a lot of new stuff.

My newest version description starts:

This is a very adapted version of the prior versions because it has 3 major improvements:

I recommend my version (for obvious reasons).

Albert

Hey Albert,
I must just have an old version of your script or something, because the script I have exports only these details

Customers
Address Book
Categories
Category Descriptions
Products
Product Descriptions
Directory Structure
Products Options
Products Options Value
Products Options Values To Products Options
Products Attributes
Specials
Reviews
Review Descriptions


And that's it, there is no exportation of Orders anywhere. That's why I was copying your format for "Customers" and just replacing everything with "Orders" for corresponding tables in OsC and ZenCart.

If you have a new script that exports Orders, Past, Current, Pending, Shipped, etc, please point me to it as that would be great ! and my hacking would be unnecessary :)

[JeremyToaster]

Nice!
Thanks a lot Albert.
Now I just have to wait for the Drupal module to be finished and I can get this site done!

Thanks so much for clearing things up for me, sorry if I was making things confusing!

Jeremy

[Centec2b]

Albert, All

Thank you for creating this download, it makes life a lot easier when your migrating from OSC to Zen

I have successfully used Albert's latest iteration of the contribution and am now trying to add in manufacturers and also the meta tags for categories and products.

Both pieces of code complain about the same error. Taking the manufacturers code;

importing manufacturersYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

I have banged this about for several hours and cannot see what I am doing wrong. It is probably a minor item but...

I would be very grateful for any suggestions as to where to take this

Below is the code for taking the data out of OSC

$query_manufacturers = "SELECT * FROM " . $source_db_table_prefix . "manufacturers";
$manufacturers = mysql_query($query_manufacturers);
$i = 0;
echo 'exporting manufacturers';
while ($export_record = mysql_fetch_assoc($manufacturers)) {
	$import_manufacturers[$i]['manufacturers_id'] =  mysql_real_escape_string($export_record['manufacturers_id']);
	$import_manufacturers[$i]['manufacturers_name'] =  mysql_real_escape_string($export_record['manufacturers_name']);
	$import_manufacturers[$i]['manufacturers_image'] =  mysql_real_escape_string($export_record['manufacturers_image']);
	$import_manufacturers[$i]['date_added'] =  mysql_real_escape_string($export_record['date_added']);
	$import_manufacturers[$i]['last_modified'] =  mysql_real_escape_string($export_record['last_modified']);
	$i++;
	echo '.';
}
mysql_free_result($manufacturers);

And this is the code for putting into Zen

echo 'importing manufacturers';
mysql_query("TRUNCATE `" . $target_db_table_prefix . "manufacturers`");
while (list($i, ) = each($import_manufacturers)) {
	$insert_manufacturers = "INSERT INTO " . $target_db_table_prefix . "manufacturers SET
	manufacturers_id = '" . $import_customer[$i]['manufacturers_id'] . "', 
	manufacturers_name = '" . $import_customer[$i]['manufacturers_name'] . "', 
 	manufacturers_image = '" . $import_customer[$i]['manufacturers_image'] . "', 
 	date_added = '" . $import_customer[$i]['date_added'] . "', 
 	last_modified = '" . $import_customer[$i]['last_modified'] . "', 
 	";
	$success = mysql_query($insert_manufacturers);
	if (!$success) { echo mysql_error(); exit;}
	echo '.';
}

Thank you

A very perplexed Centec2b

[skipwater]

I have uploaded a update to Albert's version 1.2 it has the Manufacturers section in it.
Try running version 1.3 and see if the error is still there. Version 1.3

If there is an error post your MySql version.

[Centec2b]

Try running version 1.3 and see if the error is still there.

Skipwater

Thank you.

When I compared the two and then reread the thread - duuhhhhh!

typos typos typos in my code.

I have just rerun after correcting and it worked like a dream.

The moral of the tale is dont sit up till the early hours and expect sense to prevail!

Thank you again

Centec2b