OsCommerce Data Importer - Errors

[albertsavage]

Hi all,

I installed the OsCommerce Data Importer and started testing it out on a test db to see how it would work.. as I am migrating from OSc to Zen.

I found 2 problems that someone could perhaps help me with.

1. I have a table prefix. I hardcoded it in the sql statements (my zen tables starts with zen_) Any ideas on how to add a prefix? I'd be happy to submit it back as a contribution for download when I was done.

2. I am getting errors any time an apostrophe is used. (for example, I have a customer name that is O'Connor) which causes the process to fail. I could go through and modify each customer name, but since I haven't reached the part where I start loading product descriptions, I'm sure I haven't seen the last of this pesky problem. Isn't there a quick way to account for this within a script? Like... stripslashes/addslashes? Forgive me for talking out of my ######## here since I'm not an accomplished PHP programmer. If anyone could look at the code here and start me out, I'll update and test!

Any help would be appreciated!

Thanks,
Albert

[Jimbofoxman]

I am trying to use the Netsparx OSC data importer, but I think the problem is that my original database has table prefixes. In the attachment is the code I am using, but obviously it doesn't have the table prefix being used. Anyone have any suggestions for including the table prefix? Or duplicating a database but with out the table prefixes?

[zerofriends]

I have been using oscommerce for the last year and wanted to upgrade to zencart. I just installed a fresh version of zencart 1.3.8a on my site. The only thing that I have changed is that I added the Cherry Zen 1.5 template. Now I am trying to import my oscommerce database (customers, products, etc) into zencart with the "osCommerce Data Importer" contribution submitted by netsparx.org that is found in the download section of the zencart site. I defined the database variables (database, username, and password) in the php file as instructed then I ran the php file on my server. it seemed to run smoothly during the exporting process but when it reached the importing section of the script it ran into a problem. This is the echo display that the script gave me when I ran it:

Connected Database Server
Database Opened


exporting customers....................................................................... ................................................................................ ................................................................................ ................................................................................ ..........................................

exporting address book............................................................................ ................................................................................ ................................................................................ ................................................................................ .............................................

exporting categories........

exporting category descriptions........

exporting products........................................................................ .................................................

exporting product descriptions.................................................................... .....................................................

exporting directory structure....................................................................... ...................................................

exporting products_options.

exporting products_options_values...........

exporting products_options_values_to_products_options...........

exporting products_attributes............................................................. ...............................................

exporting specials

exporting reviews..............

exporting review descriptions..............


importing customers....................................................................... ................................................................................ ................................................................................ ......................

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Atiqah', customers_lastname = 'Halim', customers_dob = '', customers_em' at line 4



I am not sure what I am doing wrong or what isn't working. If anyone could give me some advice I would greatly appreciate it. or if anyone has had luck with one of the other contributions. It will be a huge setback if I am not able to import my customers and their orders into Zen Cart so I do hope that someone can help me.

Thank you for your time,
Adam

[NHRADeuce]

Most likely there is a special character that's breaking SQL in that record. Take a look at your customer Halim Atiqah, I bet there is a ' in there somewhere.

[albertsavage]

I corrected this problem with my most recent issuance of the OSC Data importer. I had the same problems with customers having apostrophes in names and addresses. There was a simple mysql fix to handle those exceptions

[albertsavage]

I have added table prefixes to the new code just released. Good luck

[albertsavage]

OK, I was good enough to fix it myself and issue an update. I wanted to make sure that everything was in order and tested before I released it, so the newer version now takes care of these issues.

[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