Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Export / Edit and Import an Excel CSV via phpmyadmin


thewrath1

Recommended Posts

Check out the example picture.

 

I hope this explains things a bit easier...

instructions_csv_idiots.gif

 

That screenshot was excellent. It helps but now I am not getting the products_to_categories to show any of the new records that are showing up in the products section. I have set all of my products_ids to match what was initially there, but the new products wont show anywhere else. Am I missing something? Does anyone have a script I can write in mySQL to create my products database over from scratch? and If so, could you explain to me what ways to change the verbiage so that I can make my own columns? I am sorry but I don't understand php language, looks like html but the latin version to it...LOL

Link to comment
Share on other sites

  • Replies 110
  • Created
  • Last Reply
That screenshot was excellent. It helps but now I am not getting the products_to_categories to show any of the new records that are showing up in the products section. I have set all of my products_ids to match what was initially there, but the new products wont show anywhere else. Am I missing something? Does anyone have a script I can write in mySQL to create my products database over from scratch? and If so, could you explain to me what ways to change the verbiage so that I can make my own columns? I am sorry but I don't understand php language, looks like html but the latin version to it...LOL

 

 

Okay, so I just got the products_to_Categories to show the new products, but I still don't see it on oscommerce admin. Any help here guys? :( :blink:

Link to comment
Share on other sites

? 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 tried this every which way without success. I keep getting the above error message.

I've exported into Windows Excel, Mac Excel (I use OS X 10.4.6) and plain old CSV. I've compressed it, and exported it uncompressed. I've exported the file and immediately imported it without making any changes, and I still get the same error.

 

SQL-query :

 

LOAD DATA LOCAL INFILE '/tmp/phpymOtDD' REPLACE INTO TABLE `products`

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

ESCAPED BY '\\'

LINES TERMINATED BY '\r\n'

 

MySQL said:

 

#1148 - The used command is not allowed with this MySQL version

 

Yes the escape character has been put in as a double backslash.?

I've also tried removing the \n from Lines Terminated, same result.

 

Browsers used Safari 2.0.3 and Firefox 1.5.0.1 although I can't see it will make a difference.

phpMyAdmin 2.5.6

MySQL 4.1.7

Apache/2.0.55 (Unix) mod_ssl/2.0.55 OpenSSL/0.9.7a 5.0.5 (Zend: 2.0.5)

 

Does anyone have any idea why this works for some and yet others keep getting this error?

This, for me at least, is potentially very useful, not only for populating the store but for making changes such as global price changes. With several thousand items that is not something I would care to do through the admin backend.

 

Help. Please.

Link to comment
Share on other sites

? 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 tried this every which way without success. I keep getting the above error message.

I've exported into Windows Excel, Mac Excel (I use OS X 10.4.6) and plain old CSV. I've compressed it, and exported it uncompressed. I've exported the file and immediately imported it without making any changes, and I still get the same error.

 

SQL-query :

 

LOAD DATA LOCAL INFILE '/tmp/phpymOtDD' REPLACE INTO TABLE `products`

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

ESCAPED BY '\\'

LINES TERMINATED BY '\r\n'

 

MySQL said:

 

#1148 - The used command is not allowed with this MySQL version

 

Yes the escape character has been put in as a double backslash.?

I've also tried removing the \n from Lines Terminated, same result.

 

Browsers used Safari 2.0.3 and Firefox 1.5.0.1 although I can't see it will make a difference.

phpMyAdmin 2.5.6

MySQL 4.1.7

Apache/2.0.55 (Unix) mod_ssl/2.0.55 OpenSSL/0.9.7a 5.0.5 (Zend: 2.0.5)

 

Does anyone have any idea why this works for some and yet others keep getting this error?

This, for me at least, is potentially very useful, not only for populating the store but for making changes such as global price changes. With several thousand items that is not something I would care to do through the admin backend.

 

Help. Please.

 

check your MySQL language. I found that these commands are sometimes different for languages...I had lang for MyPHP admin set at en-utf-8 and MySQL connection collation set to cp1251_Ukrainian_ci

 

This worked but I had to try the different language settings in the MyPHP admin SQL home page. Another thing that worked successfully for me was the contribution at this link:

 

http://www.oscommerce.com/community/contri...5/category,all/

 

Start by installing version 1 dated 15 Nov 2002. it comes with very good detailed directions, then install and replace the excel.php file with the upgraded contribution dated 29 Jul 2005.

 

After this you can see the changes immediately on your oscommerce manager pages. One other note, I prefered to save my files as Tab Deliminated Text, for one, you can work on your spreadsheet in excel and then select Tab Deliminated Text for the save as function. I liked this better than CSV because it uses a simpler seperator [tabs] and it doesn't get confused with anything else that might appear in your spreadsheet. Also, because you can even view and edit in Notepad, which I also liked because you can see all the columns lined up and all you have to do is use tab button to move them over.....this really worked for me because I have thousand of products that each have lengthy descriptions and everything imported and exported in a synch!

 

:-"

Link to comment
Share on other sites

Found the problem.. If anyone is manually importing catalog data with csv files and their products are not showing up.. You have to also modify the table "products_to_categories" to link everything together. So, this is the answer.

 

You will need to make at least 4 manually modifications. Actually 4 csv files can do all this for you.

 

1) Create your entry in the "products" table... You will need to know the identifier of this record.

2) Create your entry in the "products_description" table. You will need to use the same identifier to link this description of the item back to the "products" table.

3) Create a category in the "category" table.. You will need remember the record identifier.

4) Create an entry in the "products_to_categories" that link the "products" table entry to the "category" table entry.

 

I followed everything you said up until directions # 1 I am now totally lost.

 

Could you please ( or someone else if they have the time) explain to me:

 

1) what you mean by the "identifier" of the "products" table and how do I figure out what the identifier is?

 

2) Is the identifier generated by the computer or do I pick my own products id numbers and so on?

 

3) If my products have their own Manufacturers UPC information for identifying their products, how do I imput them?

 

4) One more thing, I installed the excel contribution so, I don't know if I may have some conflict with anything here. I also installed the upgrade to the original contribution but I didn't like it, both gave me problems with the fields.

 

5) If I go into the PHP file myself, is there a code that I can add to get my MUPC's put on so that the tables can be organised by this so that customer order fullfillment could be easier?

 

6) One more thing...I use the same manufacturer for all of my products, is there any way I can make my storefront have a way customers can browse by Design Style instead of Browse by Manufacturer?

----------Does anyone have the code for doing this too?

 

 

I MUCH MUCH MUCH Appreciate any help that anyone can give me.

Link to comment
Share on other sites

check your MySQL language. I found that these commands are sometimes different for languages...I had lang for MyPHP admin set at en-utf-8 and MySQL connection collation set to cp1251_Ukrainian_ci

 

:-"

 

Thanks for the response, but it's not the languaage.

This error is caused by the "Load Data Local" command and is a "Security Feature" in current versions of MySQL. It is possible to configure MySQL without this feature, but it is a default setting.

There are workarounds if you know how and can be bothered to faff around, but ask your hosting company to change the settings their end, it's simpler.

Link to comment
Share on other sites

I followed everything you said up until directions # 1 I am now totally lost.

 

Could you please ( or someone else if they have the time) explain to me:

 

1) what you mean by the "identifier" of the "products" table and how do I figure out what the identifier is?

 

2) Is the identifier generated by the computer or do I pick my own products id numbers and so on?

 

3) If my products have their own Manufacturers UPC information for identifying their products, how do I imput them?

 

4) One more thing, I installed the excel contribution so, I don't know if I may have some conflict with anything here. I also installed the upgrade to the original contribution but I didn't like it, both gave me problems with the fields.

 

5) If I go into the PHP file myself, is there a code that I can add to get my MUPC's put on so that the tables can be organised by this so that customer order fullfillment could be easier?

 

6) One more thing...I use the same manufacturer for all of my products, is there any way I can make my storefront have a way customers can browse by Design Style instead of Browse by Manufacturer?

----------Does anyone have the code for doing this too?

I MUCH MUCH MUCH Appreciate any help that anyone can give me.

 

1,2) The identifier is the row number, which is given to that row (ie: a row with a product) when inserting it into the db.

The row number, is unique, and increments by 1 automatically. It has to be unique.

If you keep inserting things into the db, the row number will just continue, unless you choose to replace the existing numbers.

When I export a table, I empty it, and when bringing it back i replace any existing, so the numbers always remain the same as they were.

3) The manufacturer id is a column in the product table, the number is referred to the manufacturer table values.

So if you change them in this column in products, they can be found on the site when looking at that particular manufacturer.

4) I cant speak for the Excel contrib, as I have never used it, never needed to. As I remember, Easy Populate is the same contrib, taken further, which is how EP began.

5) If you are looking to add an extra field for a Manufacturer code, you can do one of 2 things, either set it as a products attribute, or get someone or a contrib to help u add an extra column to the products table.

Im sure you will find this aleady done many times on the forum.

6) Again, this could be done 2 ways, either a complete duplicate of the way manufacturers work, except by style. Or, preferably, as a product attribute, which is what this was designed for.

 

You choose your styles, colours, finishes, and fill them at the time of enetering products.

 

These values can then be applied when searching through your products by visitors.

 

Hope this helps.

 

thewrath1

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

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

Link to comment
Share on other sites

  • 3 weeks later...

I read in this post somewhere about installing a new mysql database on your hosts server. My question is where do you get the latest version at.

 

I know this sounds like a nubie question

 

Thanks

Link to comment
Share on other sites

at first excuse my mistakes i'm french but I didn't found anything about my question into french forums.

 

So : i'm working on a database which present some products ( a lot of products ^^)

someone made an excel array with every datas i need so i export this file in CSV to import it in phpmyadmin.

but my problem is that the original file (.xsl) contain some special charts like " ' " or " ? " and my database in phpmyadmin is in UTF8 : it means that it cuts some strigs when tere is some ' or other special charts... annoying ^^

 

how can i export my original file in CSV UTF8 ? is it possible ?

or simply : what can i do to conserv my special chart in final database ?

 

thanks a lot to everyone which will help me ^^

Link to comment
Share on other sites

it's good I found how to resolve my problem !

 

in fact i save my XSL document in CVS with excel, then i open it in the note pad and save it in CSV with the option UTF-8 !

 

but it's not possible to do it with excel only : it codes automatically in ANSI.

 

thanks nevertheless !

 

bye !

Link to comment
Share on other sites

  • 1 month later...

Helloo all,

 

I have switched host awhile back to a new and far better one. I have re-read all of this info and I am sure I under stand it. The only thing I cant figure out is when I go and upload the revised csv file it tells me I have an error in my sql syntex.

 

What am I doing wrong?

 

I currently use easypopulate but have noticed that it wasnt working as far as using the model, price, quantity download and upload. My quantity doesnt change.

 

This is really all that I need to change on a daily bassis.

 

Thank you,

 

Eric

Link to comment
Share on other sites

  • 4 weeks later...
Found the problem.. If anyone is manually importing catalog data with csv files and their products are not showing up.. You have to also modify the table "products_to_categories" to link everything together. So, this is the answer.

 

You will need to make at least 4 manually modifications. Actually 4 csv files can do all this for you.

 

1) Create your entry in the "products" table... You will need to know the identifier of this record.

2) Create your entry in the "products_description" table. You will need to use the same identifier to link this description of the item back to the "products" table.

3) Create a category in the "category" table.. You will need remember the record identifier.

4) Create an entry in the "products_to_categories" that link the "products" table entry to the "category" table entry.

 

To add one product and make it show up in the catalog i would need to download those four tables and add the information for the product to each, then upload the four tables?

I think i may have missed something as it seems like a bit of a long winded way to do it.

 

(sorry if that sounded confrontational, I'm a newbie to these things so i genuinely want to know)

Link to comment
Share on other sites

  • 2 weeks later...

Gaz,

Thanks for the database tips.

 

I have a test store that I installed on the same server. I want to transfer all of my products from one database (osc1) (my live stroe) to a new database (osc7)(my test store).

 

I exported the products table just fine, using your examples.

 

Here is my question:

 

What other tables do I need to export so that once I am done with importing into my new database (osc7), I will have all of my products along with their descriptions, images, etc.?

 

I am guessing that I will need the following tables exported/imported:

 

manufacturers, manufacturers_info, products, products_attributes, products_attributes_download, products_description, products_notifications, products_options, products_options_values, products_options_values_to_products_options, products_to_categories.

 

Is this correct? If so, I'm assuming that all I would need to do is then upload the images from my images folder along with importing the product tables above and all should be fine.

 

Thanks for any clarification on this,

 

Bill Kellum

Bill Kellum

 

Sounds Good Productions

STS Tutorials & more: STSv4.6, STS Add-ons (STS Power Pack), STS V4 Forum STS Forum FREE TEMPLATE

Link to comment
Share on other sites

  • 2 months later...

I have been looking at the screen shots to try and work out how to import new products, but I am confused. My phpadmin doesn't look anything like this, I thing I must be running a very old version of oscommerce. How do I check what version I am running?

Like someone else on this thread, I have bought my website as a package from a host and know very little about how to change things in php.

Can someone tell me how I check what version I am running?

Link to comment
Share on other sites

  • 2 weeks later...
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.

 

 

 

 

Hi Thanks

 

This worked ok with exporting

how do you import? I have used the excel csv comma delimited , it only seem to work if the file has the quotation marks around each figure, which is a real pain to edit as it is only in on column.When I export it does not have the quotes around the figures , but only get that if dont save the file but let it open in he screen.and then copy those paste them into the excel sheet and save as...

What am I doing wrong, also running phpmyadmin ....

cheers

Rusty

-------------------------------------------

Link to comment
Share on other sites

i am having great success with this but i tried to export my address book all fields are being exported apart from entry_state if i click browse in phpmyadmin i also cannot se the states, here are my tables below as you can see i have NULL next to state but if i go to my ADMIN and click invoice or packing slip i get all the details any help would be appreciated.

 

thanks

 

Rob

 

Field	Type	Collation	Attributes	Null	Default	Extra	Action
address_book_id	int(11)			No		auto_increment	 	 	 	 	 	 	
customers_id	int(11)			No	0		 	 	 	 	 	 	
entry_gender	char(1)	latin1_swedish_ci		No			 	 	 	 	 	 	
entry_company	varchar(32)	latin1_swedish_ci		Yes	NULL		 	 	 	 	 	 	 
entry_firstname	varchar(32)	latin1_swedish_ci		No			 	 	 	 	 	 	 
entry_lastname	varchar(32)	latin1_swedish_ci		No			 	 	 	 	 	 	 
entry_street_address	varchar(64)	latin1_swedish_ci		No			 	 	 	 	 	 	 
entry_suburb	varchar(32)	latin1_swedish_ci		Yes	NULL		 	 	 	 	 	 	 
entry_postcode	varchar(10)	latin1_swedish_ci		No			 	 	 	 	 	 	 
entry_city	varchar(32)	latin1_swedish_ci		No			 	 	 	 	 	 	 
entry_state	varchar(32)	latin1_swedish_ci		Yes	NULL		 	 	 	 	 	 	 
entry_country_id	int(11)			No	0		 	 	 	 	 	 	
entry_zone_id	int(11)			No	0		 	 	 	 	 	 	
Check All / Uncheck All With selected:

Link to comment
Share on other sites

  • 1 month later...

Hi there

 

I seem to be having isseus with importing a csv file via phpmyadmin. the instructions at the start do not seem to tally with the version I have. I guess that things have changed since then.

 

Normally if I try and import I get a ferror with filed count on line 1. I accidently uploaded a file with the headers in the first line. This then gave a field error on line 2. Its extremely frustrating as each time I try something it takes about 15 minutes before it tells me it has failed.

 

I have all my 9 fields as varchar set to 100 I then intend to import this data into other tables in my database. I have never managed to import the field manufacturers_id which consists of letters, numbers and sometimes charecters such as / and -

 

Can anyone help?

 

Thanks a lot

 

Olly

Link to comment
Share on other sites

After 15 contribs, battling with easypopulate to import products and ready to open my store, I forgot that I need to import existing customers. Just found this topic-looks like a great answer for what I need to do.

 

The screenshots really help out, but can someone give a step-by-step process to import customers? I am sure if I spend some itnensive time reading the posts I can figure it out but after over 100 hours of working on this store, I don't care to 'break it' when I import customers.

Link to comment
Share on other sites

After 15 contribs, battling with easypopulate to import products and ready to open my store, I forgot that I need to import existing customers. Just found this topic-looks like a great answer for what I need to do.

 

The screenshots really help out, but can someone give a step-by-step process to import customers? I am sure if I spend some itnensive time reading the posts I can figure it out but after over 100 hours of working on this store, I don't care to 'break it' when I import customers.

Any step-by-step instructions?

Link to comment
Share on other sites

I have imported my customer .csv file.

 

All the data appears in thecustomer database properly, but when I go into OSC admin, the name are there, but when I go to edit a specific customer, there is no data in any of the fields including the name fields.

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...