OsCommerce Data Importer - Errors

[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

[skipwater]

For my first post in this forum I just wanted to say Thanks Albert your code worked

With that said I was trying to modify your code to convert the manufacturer tables.

I got it to half work because I ran into manufacturer_id field which is an auto increment field.
The issue is that all the stores products have the manufacturer id in there record. So over the years adding and deleting manufacturers have left the osc manufacturer_id field non-sequential. So what I need is to convert the osc value in manufacturer_id field unchanged/raw.

I have posted the code that I am working on for reference. You might know the sql command that will allow the insert of the true value of manufacturer id and not the auto increment value.

Your help would be appreciated.

$connected = mysql_connect($source_db_host, $source_db_username, $source_db_password);
$dataopened = mysql_select_db($source_db);

if ($connected) {
	echo "Connected Database Server<br>";
} else {
	echo mysql_error();
	exit;
}
if ($dataopened) {
	echo "Database Opened<br><br><br>";
} else {
	echo mysql_error();
	exit;
}


// Get row id before insert Not used yet
function get_current_insert_id($zen_table)
{
    $q = "SELECT LAST_INSERT_ID() FROM $zen_table";
    return mysql_num_rows(mysql_query($q)) + 1;
}

$query_manufactures = "SELECT * FROM " . $source_db_table_prefix . "manufacturers";
$manufactures = mysql_query($query_manufactures);
$i = 0;
echo 'exporting manufactures ';
while ($export_record = mysql_fetch_assoc($manufactures)) {

	$import_manufactures[$i]['manufacturers_id'] =  $export_record['manufacturers_id'];
	$import_manufactures[$i]['manufacturers_name'] =  mysql_real_escape_string($export_record['manufacturers_name']);
	$import_manufactures[$i]['manufacturers_image'] =  $export_record['manufacturers_image'];
	$import_manufactures[$i]['date_added'] =  $export_record['date_added'];
	$import_manufactures[$i]['last_modified'] =  $export_record['last_modified'];
	$i++;
	echo 'GOod.';

}
mysql_free_result($manufactures);

echo '<br><br>';

$query_manufacturers_info = "SELECT * FROM " . $source_db_table_prefix . "manufacturers_info";
$manufacturers_info = mysql_query($query_manufacturers_info);
$i = 0;
echo 'exporting manufacturers info ';
while ($export_record = mysql_fetch_assoc($manufacturers_info)) {
      $import_manufacturers_info[$i]['manufacturers_id'] = mysql_real_escape_string($export_record['manufacturers_id']);
      $import_manufacturers_info[$i]['languages_id'] = mysql_real_escape_string($export_record['languages_id']);
      $import_manufacturers_info[$i]['manufacturers_url'] = mysql_real_escape_string($export_record['manufacturers_url']);
      $import_manufacturers_info[$i]['url_clicked'] = mysql_real_escape_string($export_record['url_clicked']);
      $import_manufacturers_info[$i]['date_last_click'] = mysql_real_escape_string($export_record['date_last_click']);
	$i++;
	echo '.oO';
}
mysql_free_result($manufacturers_info);

echo '<br><br>';
mysql_close($connected);


$connected = mysql_connect($target_db_host, $target_db_username, $target_db_password);
$dataopened = mysql_select_db($target_db);

echo '<br><br>';

echo 'importing manufacturers <br>';
mysql_query("TRUNCATE `" . $target_db_table_prefix . "manufacturers`");

while (list($i, ) = each($import_manufactures)) {
	$insert_manufacturers = "INSERT INTO " . $target_db_table_prefix . "manufacturers SET
	manufacturers_id = '" . $import_manufacturers[$i]['manufacturers_id'] . "', 
	manufacturers_name = '" . $import_manufacturers[$i]['manufacturers_name'] . "', 
 	manufacturers_image = '" . $import_manufacturers[$i]['manufacturers_image'] . "', 
 	date_added = '" . $import_manufacturers[$i]['date_added'] . "', 
 	last_modified = '" . $import_manufacturers[$i]['last_modified'] . "' 
	";
	echo 'Insert record<br>';
	$success = mysql_query($insert_manufacturers);
	if (!$success) { echo mysql_error(); exit;}
	echo 'Oo.';
}

echo '<br><br>';

echo 'clearing manufacturers_info';
echo '<br><br>';
mysql_query("TRUNCATE `" . $target_db_table_prefix . "manufacturers_info`");
echo 'importing manufacturers_info';
while (list($i, ) = each($import_manufacturers_info)) {
	$insert_manufacturers_info = "INSERT INTO " . $target_db_table_prefix . "manufacturers_info SET
      manufacturers_id = '" . $import_manufacturers_info[$i]['manufacturers_id'] . "', 
      languages_id = '" . $import_manufacturers_info[$i]['languages_id'] . "', 
      manufacturers_url = '" . $import_manufacturers_info[$i]['manufacturers_url'] . "', 
      url_clicked = '" . $import_manufacturers_info[$i]['url_clicked'] . "', 
      date_last_click = '" . $import_manufacturers_info[$i]['date_last_click'] . "'
	";
	$success = mysql_query($insert_manufacturers_info);
	if (!$success) { echo mysql_error(); exit;}
	echo '.';
}

echo '<br><br>';

echo '<br><br>';

mysql_close($connected);

echo '<br><br><br>All Done';

?>

[albertsavage]

Skip,

I just wrote myself a little macro to get all of the right fields in the right place some months ago. I'll see if I can dig it out if I can find it later and apply it to that table.

However, it won't matter whether the field is auto_increment or not on source or destination. The script is intended to pretty much "copy_paste" the data from one table to another, so it does not "auto increment" anything. There is no transformation going on. This is important to consider since it's a garbage_in-garbage_out script.

Your script at first glance looks good and you should test it on a test database. I have since dismantled my test database, and I didn't have any manufacturersto test it on to begin with anyway. If you do add it, I would add it to the products script since that's the natural location for it. Feel free to upload your first contribution if you do (hey, it's open source).

Albert

For my first post in this forum I just wanted to say Thanks Albert your code worked

With that said I was trying to modify your code to convert the manufacturer tables.

I got it to half work because I ran into manufacturer_id field which is an auto increment field.
The issue is that all the stores products have the manufacturer id in there record. So over the years adding and deleting manufacturers have left the osc manufacturer_id field non-sequential. So what I need is to convert the osc value in manufacturer_id field unchanged/raw.

I have posted the code that I am working on for reference. You might know the sql command that will allow the insert of the true value of manufacturer id and not the auto increment value.

Your help would be appreciated.

$connected = mysql_connect($source_db_host, $source_db_username, $source_db_password);
$dataopened = mysql_select_db($source_db);

if ($connected) {
	echo "Connected Database Server<br>";
} else {
	echo mysql_error();
	exit;
}
if ($dataopened) {
	echo "Database Opened<br><br><br>";
} else {
	echo mysql_error();
	exit;
}


// Get row id before insert Not used yet
function get_current_insert_id($zen_table)
{
    $q = "SELECT LAST_INSERT_ID() FROM $zen_table";
    return mysql_num_rows(mysql_query($q)) + 1;
}

$query_manufactures = "SELECT * FROM " . $source_db_table_prefix . "manufacturers";
$manufactures = mysql_query($query_manufactures);
$i = 0;
echo 'exporting manufactures ';
while ($export_record = mysql_fetch_assoc($manufactures)) {

	$import_manufactures[$i]['manufacturers_id'] =  $export_record['manufacturers_id'];
	$import_manufactures[$i]['manufacturers_name'] =  mysql_real_escape_string($export_record['manufacturers_name']);
	$import_manufactures[$i]['manufacturers_image'] =  $export_record['manufacturers_image'];
	$import_manufactures[$i]['date_added'] =  $export_record['date_added'];
	$import_manufactures[$i]['last_modified'] =  $export_record['last_modified'];
	$i++;
	echo 'GOod.';

}
mysql_free_result($manufactures);

echo '<br><br>';

$query_manufacturers_info = "SELECT * FROM " . $source_db_table_prefix . "manufacturers_info";
$manufacturers_info = mysql_query($query_manufacturers_info);
$i = 0;
echo 'exporting manufacturers info ';
while ($export_record = mysql_fetch_assoc($manufacturers_info)) {
      $import_manufacturers_info[$i]['manufacturers_id'] = mysql_real_escape_string($export_record['manufacturers_id']);
      $import_manufacturers_info[$i]['languages_id'] = mysql_real_escape_string($export_record['languages_id']);
      $import_manufacturers_info[$i]['manufacturers_url'] = mysql_real_escape_string($export_record['manufacturers_url']);
      $import_manufacturers_info[$i]['url_clicked'] = mysql_real_escape_string($export_record['url_clicked']);
      $import_manufacturers_info[$i]['date_last_click'] = mysql_real_escape_string($export_record['date_last_click']);
	$i++;
	echo '.oO';
}
mysql_free_result($manufacturers_info);

echo '<br><br>';
mysql_close($connected);


$connected = mysql_connect($target_db_host, $target_db_username, $target_db_password);
$dataopened = mysql_select_db($target_db);

echo '<br><br>';

echo 'importing manufacturers <br>';
mysql_query("TRUNCATE `" . $target_db_table_prefix . "manufacturers`");

while (list($i, ) = each($import_manufactures)) {
	$insert_manufacturers = "INSERT INTO " . $target_db_table_prefix . "manufacturers SET
	manufacturers_id = '" . $import_manufacturers[$i]['manufacturers_id'] . "', 
	manufacturers_name = '" . $import_manufacturers[$i]['manufacturers_name'] . "', 
 	manufacturers_image = '" . $import_manufacturers[$i]['manufacturers_image'] . "', 
 	date_added = '" . $import_manufacturers[$i]['date_added'] . "', 
 	last_modified = '" . $import_manufacturers[$i]['last_modified'] . "' 
	";
	echo 'Insert record<br>';
	$success = mysql_query($insert_manufacturers);
	if (!$success) { echo mysql_error(); exit;}
	echo 'Oo.';
}

echo '<br><br>';

echo 'clearing manufacturers_info';
echo '<br><br>';
mysql_query("TRUNCATE `" . $target_db_table_prefix . "manufacturers_info`");
echo 'importing manufacturers_info';
while (list($i, ) = each($import_manufacturers_info)) {
	$insert_manufacturers_info = "INSERT INTO " . $target_db_table_prefix . "manufacturers_info SET
      manufacturers_id = '" . $import_manufacturers_info[$i]['manufacturers_id'] . "', 
      languages_id = '" . $import_manufacturers_info[$i]['languages_id'] . "', 
      manufacturers_url = '" . $import_manufacturers_info[$i]['manufacturers_url'] . "', 
      url_clicked = '" . $import_manufacturers_info[$i]['url_clicked'] . "', 
      date_last_click = '" . $import_manufacturers_info[$i]['date_last_click'] . "'
	";
	$success = mysql_query($insert_manufacturers_info);
	if (!$success) { echo mysql_error(); exit;}
	echo '.';
}

echo '<br><br>';

echo '<br><br>';

mysql_close($connected);

echo '<br><br><br>All Done';

?>

[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

[Centec2b]

Hi Folks

Following earlier dialogues with Skipwater, I am now trying to include the metatags that appear in OSC categories_description and transfer them to the separate table meta_tags_categories_description

I have mimicked the code, but I must be missing some refinement as whilst the code says I have exported the data, there is no data imported into Zen.

I do not get error messages of any description

I would be grateful for any pointers on how I can solve this

The code excerpts are

From OSC

/* start of export of categories_meta_tags */
$query_categories_meta_tags = "SELECT * FROM " . $source_db_table_prefix . "categories_description";
$categories_meta_tags = mysql_query($query_categories_meta_tags);
$i = 0;
echo 'Exporting Categories Meta Tags...';
while ($export_record = mysql_fetch_assoc($categories_meta_tags)) {
      $import_categories_meta_tags[$i]['categories_id'] = mysql_real_escape_string($export_record['categories_id']);
      $import_categories_meta_tags[$i]['language_id'] = mysql_real_escape_string($export_record['language_id']);
	  $import_categories_meta_tags[$i]['categories_htc_title_tag'] = mysql_real_escape_string($export_record['categories_htc_title_tag']);
      $import_categories_meta_tags[$i]['categories_htc_desc_tag'] = mysql_real_escape_string($export_record['categories_htc_desc_tag']);
      $import_categories_meta_tags[$i]['categories_htc_keywords_tag'] = mysql_real_escape_string($export_record['categories_htc_keywords_tag']);
$i++;
 echo '.';
}
mysql_free_result($categories_meta_tags);
 
echo ' Done '.$i.' Records Exported<br>';
/* end of export of categories_meta_tags */

Insert in Zen

/* start of import of categories meta tags */
$ii=0;
echo 'Importing Categories Meta Tags...<br>';
mysql_query("TRUNCATE `" . $target_db_table_prefix . "meta_tags_categories_description`"); 
while (list($i, ) = each($import_categories_meta_tags)) {
$insert_meta_tags_categories_description = "INSERT INTO " . $target_db_table_prefix . "meta_tags_categories_description SET 
	categories_id = '" . $import_categories_meta_tags[$i]['categories_id'] . "', 
	language_id = '" . $import_categories_meta_tags[$i]['language_id'] . "', 
	metatags_title = '" . $import_categories_meta_tags[$i]['categories_htc_title_tag'] . "', 
	metatags_keywords = '" . $import_categories_meta_tags[$i]['categories_htc_keywords_tag'] . "',
	metatags_description = '" . $import_categories_meta_tags[$i]['categories_htc_desc_tag'] . "'
";
 $success = mysql_query($insert_meta_tags_categories_description);
 if (!$success) { echo mysql_error(); exit;}
 echo '.';
}
echo ' Done '.$ii.' Records Imported<br>';
/* end of import of categories meta tags  */

Thank you

Centec2b