Database Import from Yahoo

[osmee]

Let's assume for a minute that I know nothing about OsCommerce and have been given the task of moving an existing store from a Yahoo hosting account to GoDaddy setting up a new OsCommerce site....

Okay, now you may know how confused I am!

So, I managed to get the site up and working and have familiarized myself with the process of adding products, categories, prices, descriptions, etc. Here's the thing. My client has over 800 products on the current store and I would prefer not to have to input everything manually, when there is an existing database!

I have downloaded the database from Yahoo and I have it in .xml format, .csv format and .txt. I am really clueless as to how to IMPORT it into GoDaddy so it will display all of those wonderful items on the store without me having to do it all from scratch....

I would LOVE some pointers. I'm becoming more and more frustrated as I've been trying to find the solution here for 2 weeks and I promise I have searched all over trying not to bother anyone.

I don't know much about mysql or phpmyadmin, though they both seem to be working properly. When I am logged into mysql, there is no "import" button...

PLEEEAASSSEEEE Help if you can!

[davidinottawa]

Let's assume for a minute that I know nothing about OsCommerce and have been given the task of moving an existing store from a Yahoo hosting account to GoDaddy setting up a new OsCommerce site....

Okay, now you may know how confused I am!

So, I managed to get the site up and working and have familiarized myself with the process of adding products, categories, prices, descriptions, etc. Here's the thing. My client has over 800 products on the current store and I would prefer not to have to input everything manually, when there is an existing database!

I have downloaded the database from Yahoo and I have it in .xml format, .csv format and .txt. I am really clueless as to how to IMPORT it into GoDaddy so it will display all of those wonderful items on the store without me having to do it all from scratch....

I would LOVE some pointers. I'm becoming more and more frustrated as I've been trying to find the solution here for 2 weeks and I promise I have searched all over trying not to bother anyone.

I don't know much about mysql or phpmyadmin, though they both seem to be working properly. When I am logged into mysql, there is no "import" button...

PLEEEAASSSEEEE Help if you can!

 

Just open the file locally and copy and paste everything into the SQL window in your phpMyAdmin.

Or - there should be a button to "Browse" for a local file.

Select the file - and hit Go!

 

Back up first!

[osmee]

Just open the file locally and copy and paste everything into the SQL window in your phpMyAdmin.

Or - there should be a button to "Browse" for a local file.

Select the file - and hit Go!

 

Back up first!

 

Hey,

Thanks for taking the time to post, I appreciate that. I had tried that a few days ago but kept getting an error message and it never would work so I tried looking elsewhere. I have backed everything up (twice) and have managed to install and manipulate a (purchased) template with a new header, so I thought I was doing pretty good until this....

I have a sneaky feeling that what I had downloaded from Yahoo is in a different format from that which it needs to be in for OsCommerce. It seems to be just table entries without the "insert into" and "values", etc. (See below)

If you can shed any light on this I would be very grateful, sorry to be so duh but this is my first osc experience, but I am determined to get it under control! Thanks again to all for looking and offering assistance :)

 

Downloaded from Yahoo: (There are LOTS of these one for each item...)

 

<Table ID="wicksnmore">

<TableField ID="product-url" Type="text"/>

<TableField ID="name" Type="text"/>

<TableField ID="sale-price" Type="numbers"/>

<TableField ID="taxable" Type="yes-no"/>

<TableField ID="ship-weight" Type="numbers"/>

<TableField ID="price" Type="numbers"/>

<TableField ID="orderable" Type="orderable"/>

<TableField ID="options" Type="med-text"/>

<TableField ID="code" Type="text"/>

</Table>

 

OsCommerce Format:

 

INSERT INTO `products` VALUES (1, 1000, '', 'jelly_jar.jpg', 10.5000, '2006-08-00 00:00:00', '2007-02-09 15:09:52', NULL, 0.00, 1, 0, 0, 0);

INSERT INTO `products` VALUES (2, 1000, '', 'Clear_20Ginger_20Large1.jpg', 8.9500, '2006-08-00 00:00:01', '2007-02-09 15:06:00', NULL, 0.00, 1, 0, 0, 0);

INSERT INTO `products` VALUES (3, 1000, '', 'tins.jpg', 10.5000, '2006-08-00 00:00:02', '2007-02-09 15:08:02', NULL, 0.00, 1, 0, 0, 0);

[davidinottawa]

Hey,

Thanks for taking the time to post, I appreciate that. I had tried that a few days ago but kept getting an error message and it never would work so I tried looking elsewhere. I have backed everything up (twice) and have managed to install and manipulate a (purchased) template with a new header, so I thought I was doing pretty good until this....

I have a sneaky feeling that what I had downloaded from Yahoo is in a different format from that which it needs to be in for OsCommerce. It seems to be just table entries without the "insert into" and "values", etc. (See below)

If you can shed any light on this I would be very grateful, sorry to be so duh but this is my first osc experience, but I am determined to get it under control! Thanks again to all for looking and offering assistance :)

 

Downloaded from Yahoo: (There are LOTS of these one for each item...)

 

<Table ID="wicksnmore">

<TableField ID="product-url" Type="text"/>

<TableField ID="name" Type="text"/>

<TableField ID="sale-price" Type="numbers"/>

<TableField ID="taxable" Type="yes-no"/>

<TableField ID="ship-weight" Type="numbers"/>

<TableField ID="price" Type="numbers"/>

<TableField ID="orderable" Type="orderable"/>

<TableField ID="options" Type="med-text"/>

<TableField ID="code" Type="text"/>

</Table>

 

OsCommerce Format:

 

INSERT INTO `products` VALUES (1, 1000, '', 'jelly_jar.jpg', 10.5000, '2006-08-00 00:00:00', '2007-02-09 15:09:52', NULL, 0.00, 1, 0, 0, 0);

INSERT INTO `products` VALUES (2, 1000, '', 'Clear_20Ginger_20Large1.jpg', 8.9500, '2006-08-00 00:00:01', '2007-02-09 15:06:00', NULL, 0.00, 1, 0, 0, 0);

INSERT INTO `products` VALUES (3, 1000, '', 'tins.jpg', 10.5000, '2006-08-00 00:00:02', '2007-02-09 15:08:02', NULL, 0.00, 1, 0, 0, 0);

 

Hi Shell - you are correct.

 

In my expereince - it is best to downlaod and save as an SQL file.

I'm sure this is too basic - but here are your steps - and it may help others too :

 

Login to Yahoo phpMyAdmin

Select your database

Click Export

Options selected should be :

SQL

 

Structure

Add IF NOT EXISTS

Add AUTO_INCREMENT value

Enclose table and field names with backquotes

 

Data

Complete inserts

Use hexadecimal for binary fields

Export Type : Insert

 

Check 'Save File As'

 

Login to Other phpMyAdmin on new host

 

Import --> Browse for the SQL (or gz) file you just downloaded

Hit Go.

 

If your phpMyAdmin does not support the Import tab :

Click :

SQL

Browse for the file

Hit Go

 

If your phpMyAdmin does not support the Browse File from the SQL query window :

Open your file locally using a good text editor (like Editplus)

Copy and Paste the SQL into the SQL query window

Hit Go.

 

 

Even with a 50 meg database, this whole process should take you about 5 minutes to complete.

 

hth, david

[osmee]

Hi Shell - you are correct.

 

In my expereince - it is best to downlaod and save as an SQL file.

I'm sure this is too basic - but here are your steps - and it may help others too :

 

Login to Yahoo phpMyAdmin

Select your database

Click Export

Options selected should be :

SQL

 

Structure

Add IF NOT EXISTS

Add AUTO_INCREMENT value

Enclose table and field names with backquotes

 

Data

Complete inserts

Use hexadecimal for binary fields

Export Type : Insert

 

Check 'Save File As'

 

Login to Other phpMyAdmin on new host

 

Import --> Browse for the SQL (or gz) file you just downloaded

Hit Go.

 

If your phpMyAdmin does not support the Import tab :

Click :

SQL

Browse for the file

Hit Go

 

If your phpMyAdmin does not support the Browse File from the SQL query window :

Open your file locally using a good text editor (like Editplus)

Copy and Paste the SQL into the SQL query window

Hit Go.

Even with a 50 meg database, this whole process should take you about 5 minutes to complete.

 

hth, david

 

David,

Thank you SO much!

It really is not too basic, those are precisely the instructions I dreamed about last night! :)

Now I will try to do this from the beginning and will probably, with your help, be able to get it to work. (I'll be sure to back up first!)

I will let you know once I get it up and running.

Many many thanks.

[Velveeta]

David,

Thank you SO much!

It really is not too basic, those are precisely the instructions I dreamed about last night! :)

Now I will try to do this from the beginning and will probably, with your help, be able to get it to work. (I'll be sure to back up first!)

I will let you know once I get it up and running.

Many many thanks.

Make sure that Yahoo's database structure is compatible with Oscommerce's... I've never used a yahoo store before, but I'm willing to bet they don't have tables like products, products_description, products_to_categories, etc... So you may have to modify that sql dump before you can import it successfully, and you may have to shuffle some fields around between tables and whatnot...

 

Richard.

[davidinottawa]

Make sure that Yahoo's database structure is compatible with Oscommerce's... I've never used a yahoo store before, but I'm willing to bet they don't have tables like products, products_description, products_to_categories, etc... So you may have to modify that sql dump before you can import it successfully, and you may have to shuffle some fields around between tables and whatnot...

 

Richard.

 

You think so Richard ? Why would the host make a difference in a standard install of OSC ?

 

d

[Velveeta]

You think so Richard ? Why would the host make a difference in a standard install of OSC ?

 

d

Ahhhh I see what you mean, I'd misread the original post, and thought he was moving from a "Yahoo Store" to an oscommerce install, since Yahoo offers its own branding of store software... If Yahoo was only providing the hosting service, and it was running osc on that hosting service, and he only changed hosts, then yeah, and direct import should work just fine, otherwise, there are going to be translation issues from one database schema to the other...

 

Richard.

[osmee]

Ahhhh I see what you mean, I'd misread the original post, and thought he was moving from a "Yahoo Store" to an oscommerce install, since Yahoo offers its own branding of store software... If Yahoo was only providing the hosting service, and it was running osc on that hosting service, and he only changed hosts, then yeah, and direct import should work just fine, otherwise, there are going to be translation issues from one database schema to the other...

 

Richard.

 

Hi All,

Actually Richard, you didn't misread, it IS an existing "Yahoo Store" that I am trying to install into a new osc store on GoDaddy. I think that may be why it doesn't look anything like the osc table structure. "Translation Issues" are, I believe, exactly what's going on here.

For some reason, my client's previous web person has changed the password (groan...) so I'm waiting to be able to get in and export the table again, I'll try executing all the previous advice once I can log in.

Thanks guys for your assistance, you have no idea how greatful I am.

Shell.

[Velveeta]

Hi All,

Actually Richard, you didn't misread, it IS an existing "Yahoo Store" that I am trying to install into a new osc store on GoDaddy. I think that may be why it doesn't look anything like the osc table structure. "Translation Issues" are, I believe, exactly what's going on here.

For some reason, my client's previous web person has changed the password (groan...) so I'm waiting to be able to get in and export the table again, I'll try executing all the previous advice once I can log in.

Thanks guys for your assistance, you have no idea how greatful I am.

Shell.

In that case, rather than exporting to sql, I would advise exporting to either .csv or tab-delimited text file... This way you can write a script to split the data up into an array when you import it, and if you know which tables and fields translate to what, such as say, prod_name in yahoo's format being products_name in osc, or whatever, you can just loop through the array of data and stuff everything where it really belongs, which is that translation I was talking about...

 

Richard.

[osmee]

I cannot BELIEVE I spelled grateful like that

[osmee]

In that case, rather than exporting to sql, I would advise exporting to either .csv or tab-delimited text file... This way you can write a script to split the data up into an array when you import it, and if you know which tables and fields translate to what, such as say, prod_name in yahoo's format being products_name in osc, or whatever, you can just loop through the array of data and stuff everything where it really belongs, which is that translation I was talking about...

 

Richard.

 

(I cannot BELIEVE I spelled grateful like that)

Um, I understand what you are saying, only I do not write scripts and I don't fully comprehend "loop through the array of data...".

If I sound like a total newbie, that's because I am when it comes to osc and .php, etc. Dare I ask if I can find a script like this somewhere ...

[Guest]

(I cannot BELIEVE I spelled grateful like that)

Um, I understand what you are saying, only I do not write scripts and I don't fully comprehend "loop through the array of data...".

If I sound like a total newbie, that's because I am when it comes to osc and .php, etc. Dare I ask if I can find a script like this somewhere ...

 

 

I hate using phpmyadmin!!!

I found this software very helpful in saving and adding to or rebuilding MYSQL DBs.

http://www.ozerov.de/bigdump.php

[Velveeta]

(I cannot BELIEVE I spelled grateful like that)

Um, I understand what you are saying, only I do not write scripts and I don't fully comprehend "loop through the array of data...".

If I sound like a total newbie, that's because I am when it comes to osc and .php, etc. Dare I ask if I can find a script like this somewhere ...

I don't know that you'll be able to find a premade script for this translation anywhere, but check the contribution section for maybe "yahoo store" or something... I could probably do it if I took a look at that db dump, although I'd have to charge for it :D However, if you then wanted to stick it in the contributions section for future convertees, you could feel free to :)

 

Richard.