Jump to content

Archived

This topic is now archived and is closed to further replies.

thewrath1

Export / Edit and Import an Excel CSV via phpmyadmin

Recommended Posts

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.


Local: Mac OS X 10.5.8 - Apache 2.2/php 5.3.0/MySQL 5.4.10 • Web Servers: Linux

Tools: BBEdit, Coda, Versions (Subversion), Sequel Pro (db management)

Share this post


Link to post
Share on other sites

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.


Local: Mac OS X 10.5.8 - Apache 2.2/php 5.3.0/MySQL 5.4.10 • Web Servers: Linux

Tools: BBEdit, Coda, Versions (Subversion), Sequel Pro (db management)

Share this post


Link to post
Share on other sites

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 :thumbsup: )

 

Thanks for the info everyone has posted.

Share this post


Link to post
Share on other sites

:D 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 :D

 

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

:thumbsup:

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.


Local: Mac OS X 10.5.8 - Apache 2.2/php 5.3.0/MySQL 5.4.10 • Web Servers: Linux

Tools: BBEdit, Coda, Versions (Subversion), Sequel Pro (db management)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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!!!!!

Share this post


Link to post
Share on other sites

Maybe someone in here can help me.

 

I have tried to export the tables as described, I have read and understand what you are saying but my myphpAdmin does have the Windows or a zip button and everytime I try it a different way it wants me to download the whole SQL file ie a backup.

 

I also am not so sure this will even work for me because I have a ^ delimited products.dat file(which can be opened by EL and open office) and the line returns well there is none

ex. product_name^item^3.90

 

Could someone give me a general idea how I could add a this kind of file. Because I agree with the other guy who posted on page one that EP&PA and even the excel import didnt work.

 

Maybe I should just stick to snail pace. I dunno

 

Thanks for any help,


Eric

 

Keep up on osCommerce changes and updates at

Github | Understand osCommerce a little further at OsCommerce Documentation | Copy and paste your error message in Google add "in osCommerce" at the end to get relevant answers to most issues.

Share this post


Link to post
Share on other sites
Maybe someone in here can help me.

 

I have tried to export the tables as described, I have read and understand what you are saying but my myphpAdmin does have the Windows or a zip button and everytime I try it a different way it wants me to download the whole SQL file ie a backup.

 

I also am not so sure this will even work for me because I have a ^ delimited products.dat file(which can be opened by EL and open office) and the line returns well there is none

ex. product_name^item^3.90

 

Could someone give me a general idea how I could add a this kind of file. Because I agree with the other guy who posted on page one that EP&PA and even the excel import didnt work.

 

Maybe I should just stick to snail pace. I dunno

 

Thanks for any help,

 

What version of PHPMyAdmin are you running? My example's were originally posted when I was using 2.6.1, and should be compatible upward.

 

If you can't export, then I would suggest updating PMA, if your host allows.

 

Meanwhile: just for those who don't understand the basics of exporting a table, below are some visual guides.

 

Hope they help :-)

 

basic1.gif

 

basic2.gif

 

basic3.gif

 

Following these steps will provide you with a CSV on your desktop.

 

If you want it zipped, Zip it.


Q/ How many therapist's does it take to change a lightbulb?

A/ Two. But the lightbulb has to really 'want' to change.

Share this post


Link to post
Share on other sites

My myphpAdmin is version 2.4.0 I have the csv part but not the EL options and it looks way different.

 

If I know how to post a screen shot I would do that so everyone can see.

 

Thanks for the help


Eric

 

Keep up on osCommerce changes and updates at

Github | Understand osCommerce a little further at OsCommerce Documentation | Copy and paste your error message in Google add "in osCommerce" at the end to get relevant answers to most issues.

Share this post


Link to post
Share on other sites
My myphpAdmin is version 2.4.0 I have the csv part but not the EL options and it looks way different.

 

If I know how to post a screen shot I would do that so everyone can see.

 

Thanks for the help

2.4.0?

 

You're way, way behind. The current version is 2.8.0.2. I suggest you download and install the most recent stable version, you'll be glad you did.


Local: Mac OS X 10.5.8 - Apache 2.2/php 5.3.0/MySQL 5.4.10 • Web Servers: Linux

Tools: BBEdit, Coda, Versions (Subversion), Sequel Pro (db management)

Share this post


Link to post
Share on other sites

WOW, those screen shots are fantastic. I sure wish more people would do that. I'm so lost!!!! But I took Vger's advice, from another topic, and bought PHP & MySQL for dummies. But even that is hard to understand. So does that mean I really am a dummy....oh shit :(

But really, I am learning so much and I appreciate all of this help!

Share this post


Link to post
Share on other sites

And can I ask a dumb question...sorry in advance...

Since I have a hosting company that has PHP and MySQL available,( but I can't really play with it until I upgrade my service...which I'm not ready to do...) You show the Myphpadmin page... is that a generic page that everyone gets to from their hosting company or do you have to go to the PHP site to do what you are talking about. Sorry to be such a beginner....:(

Share this post


Link to post
Share on other sites
And can I ask a dumb question...sorry in advance...

Since I have a hosting company that has PHP and MySQL available,( but I can't really play with it until I upgrade my service...which I'm not ready to do...) You show the Myphpadmin page... is that a generic page that everyone gets to from their hosting company or do you have to go to the PHP site to do what you are talking about. Sorry to be such a beginner....:(

Most hosting companies provide a shared version of phpMyAdmin available to all users these days but it's often an older version with some of the best features disabled because a common or shared version can not support the server specific settings.

 

It's not hard to install although the directions are a little cryptic.


Local: Mac OS X 10.5.8 - Apache 2.2/php 5.3.0/MySQL 5.4.10 • Web Servers: Linux

Tools: BBEdit, Coda, Versions (Subversion), Sequel Pro (db management)

Share this post


Link to post
Share on other sites

Okay, so if they have this older, out dated version....that everyone uses, would I be allowed or able to download it myself and use my own or in those circumstances do you have to use the older, out dated version because it's all that is allowed??

Or would you suggest finding a hosting company that does allow it??

THANKS!!!

Share this post


Link to post
Share on other sites
Okay, so if they have this older, out dated version....that everyone uses, would I be allowed or able to download it myself and use my own or in those circumstances do you have to use the older, out dated version because it's all that is allowed??

Or would you suggest finding a hosting company that does allow it??

THANKS!!!

If you can install and run osC you can install and run phpMyAdmin. It's just another php based application.

Local: Mac OS X 10.5.8 - Apache 2.2/php 5.3.0/MySQL 5.4.10 • Web Servers: Linux

Tools: BBEdit, Coda, Versions (Subversion), Sequel Pro (db management)

Share this post


Link to post
Share on other sites

Can anybody tell me why my new products don't show up in the products to categories section on myPHP? I see them after I imported them on the products listing, but they don't show up under products to categories. Note, to make life easier I first exported the original products php file and added my products according to the columns. The file appears to have imported successfully what's wrong now?

Share this post


Link to post
Share on other sites

×