110 replies to this topic

[melinhead]

I just wanted to thank Gaz for posting the initial instructions here. I had figured out which tables required editing, but I couldn't get the table to export from phpMyAdmin in a way I could use.

Gaz, you ROCK!! Thank you!!

For me, Gaz's info was about half the puzzle. Here are some of the issues I'm hearing, and how I fixed them:

• phpMyAdmin is available in a variety of versions. In my case I have two different hosting companies and they use different versions. So if something (like the Export TAB) isn't where you expected it to be, just look a little harder.

• When EXPORTING, I had to select Windows even though I'm on a Mac. Excel didn't like the Mac version of the .csv file.

• I would recommend selecting the box to "Put fields names at first row" and then deleting that row before you import the finished file.

• Note that if you make changes to any table containing TIME & DATE info, (in my experience) Excel will screw this up for you by rearranging the date format. I did not find a way to fix this, other than to edit by hand in a text editor. I use BBEdit, but I suspect Notepad is similar.

• After editing in Excel and saving as CSV I was ready to import. When IMPORTING, I had to change "Lines terminated by:" FROM "\r\n" TO just "\r"

• When IMPORTING, I also had to select "...DATA LOCAL" - as I was getting the error some others in this post referred to: "MySQL said: The used command is not allowed with this MySQL version"

I have learned this whole thing from scratch with the support of these forums. This is my first post, as I am no expert. But I'll keep sharing the knowledge as I pick it up, as I'm grateful for all the support. This has saved my company several THOUSAND dollars by being able to DIY.

Melanie

[thedotcom]

Many thanks for the tip.

Now expalain to me what the various columns:
(a)111 [b]0 ( c) (d)shop items 095.jpg (e)175 (f)1/11/2005 12:13 (g)9/11/2005 18:33 (h)NULL (i)0 (j)1 (k)0 (l)0 (m)0


Many thanks in advance

Edited by thedotcom, 06 January 2006, 02:16.

[thewrath1]

thedotcom, on Jan 6 2006, 02:15 AM, said:

Many thanks for the tip.

Now expalain to me what the various columns:

(a)111 [b]0 ( c) (d)shop items 095.jpg (e)175 (f)1/11/2005 12:13 (g)9/11/2005 18:33 (h)NULL (i)0 (j)1 (k)0 (l)0 (m)0
Many thanks in advance

I take it from the above that you are referring to the fields in the table "products", if so, then the below are the field names.

A=products_id
B=products_quantity
C=products_model
D=products_image
E=products_price
F=products_date_added
G=products_last_modified
H=products_date_available
I=products_weight
J=products_status
K=products_tax_class_id
L=manufacturers_id
M=products_ordered or products_sort_order

By the way, if never want any image name problems when using a PC, don't ever use image names like the one you've used in your database. I'm talking about "shop items 095.jpg".
It's not good practice to name files like this and you will discover many problems naming files in this way.

Always save files in lower case, never any spaces, and if you want to read things clearer for filenames with more than one word always same them with an underscore between each word, as underscore is considered as the safest space option to use. eg: name_files_like_this.jpg or like_this.htm or like_this.php

:-) (end of nag).

Hope this helps. - Now off to get some Zzzz.
================================

(MANY THANKS MELANIE FOR THE PRAISE - above!)
================================

[AlanR]

melinhead, on Jan 5 2006, 12:58 PM, said:

• Note that if you make changes to any table containing TIME & DATE info, (in my experience) Excel will screw this up for you by rearranging the date format. I did not find a way to fix this, other than to edit by hand in a text editor. I use BBEdit, but I suspect Notepad is similar.

The solution for your date problem is to select the column(s) with the dates in the Excel Spreadsheet and apply a custom date format to those columns.

Go to the Formatting Palette -> Number and choose Custom.

In the Format Cells Window that Appears if Custom is not already shown scroll down to it.

You need this date format: yyyy/mm/dd hh:mm:ss

You can select a date format that's close and edit it to match the above or just copy and paste my line.

[gphilip]

So was anyone able to successfully export/import customers with their addresses and passwords? Is it possible? Thanks.

Philip

Edited by gphilip, 10 February 2006, 07:18.

[AlanR]

gphilip, on Feb 10 2006, 02:18 AM, said:

So was anyone able to successfully export/import customers with their addresses and passwords? Is it possible? Thanks.

Philip

I just did so using a test store. I noticed one thing and I'm not sure when or why this change has taken place or exactly what to blame yet.

The last field is the newsletters checkbox which should be a 1, a zero or NULL. Even though the field is set to null if you browse the table you'll see that no value at all (including null) appears in that field unless it's one. That's the case for me at least. I tried setting the default to '0' (zero, not a null) and the same thing happens. I get the same result with "not null" and a default value of zero. More research is required.

The result is that in the exported csv file the last character in a line is a comma, (',') when there is no value for newsletter. This causes the csv import parser in phpMyAdmin some problems. In my case it drops the last line of the import.

The simple work around is to set it to zero (if it's not 1) in Excel before attempting a csv reimport. You can do this in an extra (temporary) column in Excel. Test for the existence of '1' in the newsletter column. If 1 exists then it's 1 in the temporary column, if not it's explicitly 0.

Convert the temp column to absolute values, not calculated. Then delete the original newsletter column, shifting the temp column to the left.

Edited by AlanR, 10 February 2006, 18:49.

[AlanR]

Further to the above post, setting the newletter column to int, not null, default value '0' (zero) seems to solve the problem and doesn't seem to interfere with osC's ability to recognise values in that column.

[candisgifts]

I just want to say thanks for all this information. I was entering my database individual one by one, because easypopulate caused me to dump my previous database. This works perfectly on the test I performed. I also made a Master like recommended (pretty sad I didn't do that in the first place since I used to be a MCT )

Thanks for the info everyone has posted.

[technotoys]

Hi all, First post so just want to say hi and thanks to all the folks that have taken the time to provide pricles info for all us wannabe programmers!!!

I have just got to the final stage of my oscom install. after about 20 contributions and 5 months work
So came the time to populate the db, What a bore, so i tried easy pop,excel import all with no luck (no disrispect intended) After a few hours on the standard admin and with the help of an oscom managemet system i got up to about 50 products or so!! 5500 to go ! HELP.

I then stumbeled onto this thread. one cup of coffe and and one hour later my site had 3000 products in

So a large thanks for one of the most usfull, time saving, simply put, most obvoius and best way to poulate the site. Thanks

All the best John.

I will put a link up to hte sit in afew days so yoou can all dig your claws in and maybe give me some feedback.

[lancaster_unik]

OK - I have completed my phpMyAdmin upload. It seems to have worked due to the fact that i can see the data in the tables. However i cant see the data on the actual site!

I upload CSV files to the following tables with the correct information in:

products
catagories
products_to_catagories
product_description


Have i done this correct or is there something i am missing? I havent yet uploaded the images but i shouldnt imagine that to be a problem?

I presume that others have had this problem, can somebody please advise me?

[Simpel]

lancaster_unik, on Mar 2 2006, 05:05 PM, said:

OK - I have completed my phpMyAdmin upload. It seems to have worked due to the fact that i can see the data in the tables. However i cant see the data on the actual site!

I upload CSV files to the following tables with the correct information in:

products
catagories
products_to_catagories
product_description
Have i done this correct or is there something i am missing? I havent yet uploaded the images but i shouldnt imagine that to be a problem?

I presume that others have had this problem, can somebody please advise me?

I got the same problem. I don't see them in the catalog also. But If I put for example the categories by hand with admin I wil see these in the phpmyadmin. Someone give us advise? is there a little button to set the power on?

[lancaster_unik]

Simpel, on Mar 6 2006, 07:21 PM, said:

I got the same problem. I don't see them in the catalog also. But If I put for example the categories by hand with admin I wil see these in the phpmyadmin. Someone give us advise? is there a little button to set the power on?

You need to add them directly in the phpmyadmin table categories_description. This will then show you the products providing you have completed the rest of the update as per the tutorial.

One thing to note is that when i did it i set the date in the wrong format. i thought it was mmddyyyy but in actual fact it was ddmmyyyy or vice versa cant quite remember :| !! This meant that the products were added but in the "products expected" section. I needed to make them active. School boy error but its just a thought

[Simpel]

Problem solved.

Look at your products in the phpmyadmin. product_status has to be set on 1 then you will see the products in your shop, otherwise they where set in your expected products in future

[Kyder Keltic]

Hi All, I have been reading the posts on importing categories and products using a csv file. In all the carts that I have used in the past I have always imported products using excel-csv. The only thing I'm having second thoughts about is using PHPMYADMIN. Isn't there anywhere where I can get all the headers for excel and just upload it this way? I have never used PMA and I'm unsure about using it. I have added one product and then downloaded the db file and opened it up in excel. There was 978 rows with about 99 columns, and none of them looked anything like the headings that I have seen in the posts. Any help here without using the PMA app.

[AlanR]

Kyder Keltic, on Mar 8 2006, 05:12 PM, said:

Hi All, I have been reading the posts on importing categories and products using a csv file. In all the carts that I have used in the past I have always imported products using excel-csv. The only thing I'm having second thoughts about is using PHPMYADMIN. Isn't there anywhere where I can get all the headers for excel and just upload it this way? I have never used PMA and I'm unsure about using it. I have added one product and then downloaded the db file and opened it up in excel. There was 978 rows with about 99 columns, and none of them looked anything like the headings that I have seen in the posts. Any help here without using the PMA app.

PMA is intimidating at first but it's a superb tool. Once you get used to it you'll never want to administer a database centered program without it.

Just experiment a bit. If you have the ability to create more than one database on your server create a spare one for testing and experimenting, import the osC database into it, then really give PMA a workout. You'll be surprised, within a few days you'll feel like a pro.

As to the export which confuses you, export only one table at a time. Visit that table by clicking its name on the left then click the export tab on the top. Then you'll be exporting only that table. You don't have to save to file right away, you can just let it create a screen view by making sure no Save as file box is clicked. That way you can check your selection.

I think the newest versions are a little easier to use and more intuitive. Check to see which version you have, the latest is 2.8.0. I always download and install the latest ones as soon as they're available, it takes me about five minutes. I check their bug reports and sometimes submit one.

I think there is an import/export via Excel CSV contribution here in the contributions section but I've never looked at it. I much prefer to have full control and the ability to see exactly what is happening.

Edited by AlanR, 08 March 2006, 22:48.

[bwprice100]

Just joined the forum and am evaluating osCommerce for use on my on-line shop at www.ebolt.co.uk
At present my site uses a simple shopping cart I hacked using PHP of a combination of flat text files and a MSQL database. In order to populate my database (which is a non relational setup) I use a simple web that calls a PHP script that then populates the table in my database. I would like to extend this to work with osCommerce but the problem is the relational aspect of the osCommerce database. My question is there a relationship map of all the tables in the osCommerce database.
I have also tried to use ‘Easy populate’ but with no success; I have also tried uploading the data to the tables using my PHP scripts but it is easy to break the relationships and not get the products to show in the catalogue.

Brian

[eyestuffguy]

bwprice100, on Mar 10 2006, 10:57 AM, said:

Just joined the forum and am evaluating osCommerce for use on my on-line shop at www.ebolt.co.uk
At present my site uses a simple shopping cart I hacked using PHP of a combination of flat text files and a MSQL database. In order to populate my database (which is a non relational setup) I use a simple web that calls a PHP script that then populates the table in my database. I would like to extend this to work with osCommerce but the problem is the relational aspect of the osCommerce database. My question is there a relationship map of all the tables in the osCommerce database.
I have also tried to use ‘Easy populate’ but with no success; I have also tried uploading the data to the tables using my PHP scripts but it is easy to break the relationships and not get the products to show in the catalogue.

Brian

Hi. I can not comment on the Relationship Map question, but unless you are SURE you want to import your data manually, I would suggest re-visiting Easy Populate. I too debated this issue and contribution, but I was able to get it installed and working (after a day or so) and I'm glad I did! The instructions are...let's say...poorly written and more complex than they need to be. I really wish someone, who knows a little more about it then I do, would write a CLEAN 'How To' guide. My suggestion would be to follow the "1readmeFIRST.txt" document and only reference the "EasyPopulate_Manual" for further explanation. I use Easy Populate with the Header Tags contribution (installed BEFORE Easy Populate) and it makes for a great combination!
Good luck!

[tegralens]

Can some explain about exporting customers and importing them again. I tried several times and I'm having no luck I even tried to import back the same ones again after exporting them. All I get is 00000 someone have any ideas?

[tegralens]

tegralens, on Mar 31 2006, 01:35 PM, said:

Can some explain about exporting customers and importing them again. I tried several times and I'm having no luck I even tried to import back the same ones again after exporting them. All I get is 00000 someone have any ideas?

I finally was able to get it done i see all the customers now but when I go to edit a customers information in oscommerce I see this error

Warning: reset(): Passed variable is not an array or object in /home/abcde/public_html/catalog/admin/includes/classes/object_info.php on line 17

Warning: Variable passed to each() is not an array or object in /home/abcde/public_html/catalog/admin/includes/classes/object_info.php on line 18

And I don't see a single information there even though i see it in the phpmyadmin.

Any ideas?

[Nanci]

See, now this is extremely helpful!!!! Thank you so much for the visual help. I'm with the other guy...I need it simple or need to see it.
THANKS!!!!!