Export / Edit and Import an Excel CSV via phpmyadmin

[beegud2 0]

Hi All,

 

I'm new like many to this whole shopping cart and website thing. I've been going over this postand several others to help me get the hang of things. I've gotten the whole import thing down but i'm running in to some problems with exporting. :'(

 

1) I have phpMyAdmin 2.9 and everytime i try to export it goes to a page not available thingy. Does anyone know why it's doing that? :huh:

 

2) I afraid i'm a bit thick in the head, could someone explain how i get my tables to co-ordinate as was suggested earlier by GAZ. I'm afraid i still didn't get what you were trying to say...

 

for example....these are my additions under the Products Table after the prodcuts_id

 

products_item number

products_description

products_category

products_image

products_price

products_jewelry

 

NOW...do i add all those in the table structures of products_description TABLE , the products_to_categories TABLE and the categories TABLE

 

3)Can I INSERT instead of IMPORTING the data and if so how?

 

4)How do I get all this to show up in my OSC store OR Do I have to create a PHP code fo this and insert into the OSC file manager or edit a php file for each table in order for my products to show up in my OSC Store?

 

I'm sorry I knwo it is a lot to ask and trust me I have spent days going over tutorials, over forum posts etc and haven't quite found the answers. I've come to the end of my rope so any help is appreciated.

 

Bee

[newtech 0]

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.

Realized that there is an address table and customer_info table that must also be imported.

[6ixty6 0]

Check out the example picture.

 

I hope this explains things a bit easier...

 

 

Hi Guys

 

Ive been asked to find out weather this csv file will go into oscommerce...

 

I have tried to do it myself but with no luck im now turning to the pros (YOU GUYS) for much needed help

 

Link to CSV File

 

so if someone knows please can you help me out

 

thanks

 

:thumbsup:

[indoinvegas 0]

Check out the example picture.

 

I hope this explains things a bit easier...

 

Hello,

 

Where can I find phpadmin?

Where can I find the "products" as you describe on step 1?

 

Sorry, I am totally lost :rolleyes:

 

M

[thewrath1 3]

Hello,

 

Where can I find phpadmin?

Where can I find the "products" as you describe on step 1?

 

Sorry, I am totally lost :rolleyes:

 

M

 

If you have osCommerce installed and working, then it must be using and saving it's database somewhere.

This will be with your ISP/Host. Your host should have given you access to your account with them, where you should be able to log-in and find an option for MySQL Administration or something named similar.

 

You are looking for a feature within your account marked phpMyAdmin, which will launch the program which holds and controls all your database tables and fields.

 

For you newbies: Here are a few things about phpMyAdmin to help you find your way.

 

> The list down the left are the names of all of your existing database Tables. ie: products

 

> Each Table has a BROWSE tab, to let you view any data inside this table. if its greyed out, theres no data!

> Each Table has a STRUCTURE tab, where you will see details of each table that makes the data display in the order and way it needs to be organised.

> Each Table has a SQL tab for typing or pasting SQL queiries directly

> Each Table has a SEARCH tab, obvious one this.

> Each Table has a IMPORT tab, you guessed why.

> Each Table has a EXPORT tab, for exporting.

> Each Table has a OPERATIONS tab, for making major movements and common tasks.

> Each Table has a EMPTY tab for emptying a table of all its data, but keeping the structure.

> Each Table has a DROP tab, this is basically for deleting the complete table, or as we call it 'dropping' the table. Tread carefully here!

 

And remember guys, the field 'products' wont show anything on osc without having information provided in the 3 product tables that combine them and allow osc to display them.

 

The 3 product tables required are all linked by the first field in each: product_id

 

These are:

 

products (linked to the next with product_id)

products_description (linked by product_id)

products_to_categories (linked by product_id and linked to the categories table with.... category_id, also found in categories)

 

And this is how is goes, throughout the tables.

 

A little note: Make sure you have a test database with an untouched 'vanilla' oscommerce installed at all times, this way you can get familiar, and test things in phpMyAdmin without breaking everything. Remember, although you are playing directly with the heart of the machine by playing in the database, and although there is no UNDO!, you should not be scared to try things, as its all basically tables, within tables.

 

Before opening phpMyAdmin for the first time and attempting to export edit and import 10,000 products, for the first time of using it, try setting up a single product first in all 3 product tables and seeing if that works first. If it works for 1 product, and you follow the same rules you did for the first, it will work for 10,000 too.

 

Lastly, if any of you are getting TimeOut messages when importing large tables, to get around this you need to split and import the data in more than one csv file, not replacing the first imported data, until all the data is imported.

 

hope this helps some of you newbies ;-)

[shubbert 0]

I cant seem to find the phpadmin. where is it located?

 

 

EXPORT CSV

To EXPORT your list of products (or whatever) from phpmyadmin

 

Before you start - backup your database in your Admin,Tools,Database Backup or from phpmyadmin.

Before you save your file from Excel - just save it with only ONE product line and test it works with ONE product first. if successfull, go for the entire product list. Backup takes seconds, so DO IT!!!!

 

In phpmyadmin choose your products table

click BROWSE

scroll to the bottom of the page and hit EXPORT

choose CSV for MS Excel

choose from Excel edition: Windows (if on a PC)

click zipped

click GO and save you file to your PC, then unzip and alter in Excel.

 

When finished altering - Save as csv (comma delimited)

 

IMPORT CSV

To IMPORT your list of products (or whatever) in phpmyadmin

 

Go to phpmyadmin and choose your products table

click BROWSE

click SQL tab

 

Under the 'Run SQL query window' you will see a box marked

 

Or Location of the textfile:

Location of the textfile:

(Max: 8,192KB) (this is 8mb by the way, enough to upload over 20000 products)

 

Underneath you will see some text that says...

"Insert data from a textfile into table"

 

Click it, you will see this....

 

Location of the textfile

CHOOSE YOUR CSV FILE YOU SAVED FROM EXCEL AS COMMA DELIMITED

 

Replace table data with file

TICK THIS - (Warning! This will replace the entire databes product lines to read the same as the data you saved from Excel) The contents of the file replaces the contents of the selected table for rows with identical primary or unique key.

 

Fields terminated by

CHANGE THIS FIELD TERMINATOR TO A COMMA , HENCE THE CSV (Comma Delimited File)

 

...DATA LOCAL

Keep this to DATA LOCAL

 

SUBMIT

 

(You will have to wait around 15 seconds for 3000 to 4000 products to complete and then you will see...

Inserted rows: 14192 as an example)

 

Voila! - that's it. You're done. :D

- This can be used for any header field in your database - not just the product field, I used it for image names. It worked for me fine - but if things went ugly - then reload your backup).

 

You did backup didn't you? If you didn't, please don't complain to me. :-) :blink:

[shubbert 0]

I need guidence on this, I cant seem to find phpmyadmin in the OSC administrator is it under tools?

 

These are the only options I get:

 

I have loads of producst to upload and Im at a stand still

 

 

Configuration

 

Catalog

 

Modules

 

Customers

 

Locations / Taxes

 

Localization

 

Reports

 

Tools

Database Backup

Banner Manager

Cache Control

Define Languages

File Manager

Send Email

Newsletter Manager

Server Info

Who's Online

[enigma1 2]

I need guidence on this, I cant seem to find phpmyadmin in the OSC administrator is it under tools?

 

These are the only options I get:

 

I have loads of producst to upload and Im at a stand still

Configuration

 

Catalog

 

Modules

 

Customers

 

Locations / Taxes

 

Localization

 

Reports

 

Tools

Database Backup

Banner Manager

Cache Control

Define Languages

File Manager

Send Email

Newsletter Manager

Server Info

Who's Online

phpmyadmin maybe available from your host's cpanel. The tool is under the GPL license and is also available from here:

http://www.phpmyadmin.net/home_page/index.php

[andycnelson 0]

I think I'm doing all of this correctly, but it only imports the first row of my CSV data. Thoughts?

[shubbert 0]

Im am copletly lost I dont even know where to find the phpmyadmin to get to this CSV file yuo speak of. Im fimiliar with CSV files and if I find it I am very confident that I can get this accopmplised.

 

Can yuo walk me through how to get there right from the begginig so i know Im lookling in the right place?

 

 

 

 

Yes - The field seperator is the seperating character that divides each field in the csv.

 

Make sure you saved it as a csv (Comma Seperated Values).

 

When you import the csv back into phpmyadmin - make sure you change the default value of a ; semicolon to a comma in the box marked {Fields terminated by}

 

As a note to everyone. The IMPORT textfile options are very powerful, but read and understand what the program is looking for to avoid import troubles.

 

As an example - The field marked {Lines terminated by}, by default, this is looking for any \r (carriage returns) or \n (linefeeds) in your csv file. If it finds any, it will interpret them as a break in the line, and wrap the following text accordingly.

[shubbert 0]

Also here is a link that I'm trying to get pics to be seen and with no success. I’ve uploaded the pics inside the images folder and the path I get when I right click on the image goes to store then images (http://resourcesonline.us/osCommerce3/catalog/images/store/Audio%20Cables/FUL1_BE112234-10.jpg) I’m very clueless as t how t get these pictures to view.

 

Here is the link to my ecommerce page: http://resourcesonline.us/osCommerce3/catalog/

 

I have even went as far as creating a folder named store in the images folder to see if the path would fall into place. But for some reason no luck.

 

I have a huge project that is coming up that will require about 5000 products and I do not want to take that on without knowing how to get thee picture viewable.

 

PLEASE HELP ANYONE!

 

I would certainly appreciate it.

[JLatiolait 0]

hello,

 

regarding initial instructions-

 

i'm not able to follow "IMPORT" directions because "Insert data from a textfile into table" under the SQL tab under "Or Location of the textfile" is not present, version 2.6.2 w/ host provider. its not present anywhere else either. perhaps it was removed by host? i do not know but it's not there and i can not get a direct answer from host... so forget about version upgrades, etc.

 

using the "Run SQL query/queries on database XXX:" i'm executing the following SQL w/o error BUT it does NOT populate my "table" (my table as browsed via PMA):

 

LOAD DATA LOCAL INFILE 'j:/productsJL.txt' REPLACE INTO TABLE products

FIELDS TERMINATED BY ','

ESCAPED BY '\\'

LINES TERMINATED BY '\r\n'

 

'j:/productsJL.txt' =

29,10000,EME-BPLATE,NoImageJL.gif,500.25,7/5/2007 17:59,7/23/2007 20:02,NULL,45,1,0,0,0

(this is a one line only product table test file)

 

i've tried removing the 2nd backslash and also \n. i've also tried this as a csv (preferable).

 

i receive no errors; the table remains unchanged. any help here is greatly appreciated and i think others may benefit here? i feel this is potentially a valuable contribution to this thread please assist.

 

thanks, j

[JLatiolait 0]

hello,

 

regarding initial instructions-

 

i'm not able to follow "IMPORT" directions because "Insert data from a textfile into table" under the SQL tab under "Or Location of the textfile" is not present, version 2.6.2 w/ host provider. its not present anywhere else either. perhaps it was removed by host? i do not know but it's not there and i can not get a direct answer from host... so forget about version upgrades, etc.

 

using the "Run SQL query/queries on database XXX:" i'm executing the following SQL w/o error BUT it does NOT populate my "table" (my table as browsed via PMA):

 

LOAD DATA LOCAL INFILE 'j:/productsJL.txt' REPLACE INTO TABLE products

FIELDS TERMINATED BY ','

ESCAPED BY '\\'

LINES TERMINATED BY '\r\n'

 

'j:/productsJL.txt' =

29,10000,EME-BPLATE,NoImageJL.gif,500.25,7/5/2007 17:59,7/23/2007 20:02,NULL,45,1,0,0,0

(this is a one line only product table test file)

 

i've tried removing the 2nd backslash and also \n. i've also tried this as a csv (preferable).

 

i receive no errors; the table remains unchanged. any help here is greatly appreciated and i think others may benefit here? i feel this is potentially a valuable contribution to this thread please assist.

 

thanks, j

 

 

Additional Info: i have tried this also with date format (0000-00-00 00:00:00)

[ctec2001 2]

EXPORT CSV

To EXPORT your list of products (or whatever) from phpmyadmin

 

Before you start - backup your database in your Admin,Tools,Database Backup or from phpmyadmin.

Before you save your file from Excel - just save it with only ONE product line and test it works with ONE product first. if successfull, go for the entire product list. Backup takes seconds, so DO IT!!!!

 

In phpmyadmin choose your products table

click BROWSE

scroll to the bottom of the page and hit EXPORT

choose CSV for MS Excel

choose from Excel edition: Windows (if on a PC)

click zipped

click GO and save you file to your PC, then unzip and alter in Excel.

 

When finished altering - Save as csv (comma delimited)

 

IMPORT CSV

To IMPORT your list of products (or whatever) in phpmyadmin

 

Go to phpmyadmin and choose your products table

click BROWSE

click SQL tab

 

Under the 'Run SQL query window' you will see a box marked

 

Or Location of the textfile:

Location of the textfile:

(Max: 8,192KB) (this is 8mb by the way, enough to upload over 20000 products)

 

Underneath you will see some text that says...

"Insert data from a textfile into table"

 

Click it, you will see this....

 

Location of the textfile

CHOOSE YOUR CSV FILE YOU SAVED FROM EXCEL AS COMMA DELIMITED

 

Replace table data with file

TICK THIS - (Warning! This will replace the entire databes product lines to read the same as the data you saved from Excel) The contents of the file replaces the contents of the selected table for rows with identical primary or unique key.

 

Fields terminated by

CHANGE THIS FIELD TERMINATOR TO A COMMA , HENCE THE CSV (Comma Delimited File)

 

...DATA LOCAL

Keep this to DATA LOCAL

 

SUBMIT

 

(You will have to wait around 15 seconds for 3000 to 4000 products to complete and then you will see...

Inserted rows: 14192 as an example)

 

Voila! - that's it. You're done. :D

- This can be used for any header field in your database - not just the product field, I used it for image names. It worked for me fine - but if things went ugly - then reload your backup).

 

You did backup didn't you? If you didn't, please don't complain to me. :-) :blink:

 

Thanks, this is exactly what I am looking for. My client should be happy that they can now change the product prices at one time.

[newbie06 0]

This step-by-step is really helpful, but I'm having trouble -- when I follow this, it saves the file as .php and I can't open it in excel. Am I doing something wrong? It happens whether I zip it, gzip, or don't zip it at all. (I'm using a mac, and have no option to save it as anythin gother than "~.php."

 

I'm new to this.

 

Thanks for your help!

[twistedindustries 0]

I can only seem to update my customers this way with everything but a password but i can use the admin change password contribution to set them up and then everything works has anyone else had better luck with this?

[shubbert 0]

What about images? how do yuo upload thousands of images at a time, without having to do it one by one?

 

Ive done all these steps and the images get left behind.

 

EXPORT CSV

To EXPORT your list of products (or whatever) from phpmyadmin

 

Before you start - backup your database in your Admin,Tools,Database Backup or from phpmyadmin.

Before you save your file from Excel - just save it with only ONE product line and test it works with ONE product first. if successfull, go for the entire product list. Backup takes seconds, so DO IT!!!!

 

In phpmyadmin choose your products table

click BROWSE

scroll to the bottom of the page and hit EXPORT

choose CSV for MS Excel

choose from Excel edition: Windows (if on a PC)

click zipped

click GO and save you file to your PC, then unzip and alter in Excel.

 

When finished altering - Save as csv (comma delimited)

 

IMPORT CSV

To IMPORT your list of products (or whatever) in phpmyadmin

 

Go to phpmyadmin and choose your products table

click BROWSE

click SQL tab

 

Under the 'Run SQL query window' you will see a box marked

 

Or Location of the textfile:

Location of the textfile:

(Max: 8,192KB) (this is 8mb by the way, enough to upload over 20000 products)

 

Underneath you will see some text that says...

"Insert data from a textfile into table"

 

Click it, you will see this....

 

Location of the textfile

CHOOSE YOUR CSV FILE YOU SAVED FROM EXCEL AS COMMA DELIMITED

 

Replace table data with file

TICK THIS - (Warning! This will replace the entire databes product lines to read the same as the data you saved from Excel) The contents of the file replaces the contents of the selected table for rows with identical primary or unique key.

 

Fields terminated by

CHANGE THIS FIELD TERMINATOR TO A COMMA , HENCE THE CSV (Comma Delimited File)

 

...DATA LOCAL

Keep this to DATA LOCAL

 

SUBMIT

 

(You will have to wait around 15 seconds for 3000 to 4000 products to complete and then you will see...

Inserted rows: 14192 as an example)

 

Voila! - that's it. You're done. :D

- This can be used for any header field in your database - not just the product field, I used it for image names. It worked for me fine - but if things went ugly - then reload your backup).

 

You did backup didn't you? If you didn't, please don't complain to me. :-) :blink:

[noppie 1]

What about images? how do yuo upload thousands of images at a time, without having to do it one by one?

Ive done all these steps and the images get left behind.

I tell you I had a hard time with this..

the pictures have to be in read write execute

now I have linux and that is how i found out

so the question I have to ask you is your hosting company a linux company?

then you need to change the permissions on the pictures after you upload them to your server..

and Upload you pictures before you add the csv files..

yet is sounds like you already uploaded your csv... so go to your ftp program and see what permission the pictures have..

if you have any question..

ms me at rainbowstop Yahoo

I can try to help.

noppie

[fatriff 0]

are you people daft? what is phpmyadmin? you will find it in your website hosting control panel.. it is where your databases are stored.

[Envydia 0]

I just saw this post and I have the phpadmin version 2.10.0.2 and am confused with the exporting. Can someone put up an updated version on how to upload csv/excel and with possible screenshots. I have been a year trying to do this and would be greatly appreciated...

[No Posers Please! 0]

Hi people,

I sell vinyl records, and not only through my webshop, sometimes I take my stock of records to concerts as well.

 

So I would like to export an inventory list to Word/Excel of my entire stock, with product names and their quantity. How do I do this?

 

'products_name' is located under the 'products_description' entry.

'product_quantity' is located under the 'products' entry.

 

I can't seem to to able to join these two in one list.

 

As an added bonus I wouldn't mind having a column with the name of the category displayed for each product as well. Is this possible?

 

 

Thanks in advance.

-Hans

[tangwaichee 0]

Hi

 

im having this error : Invalid field count in CSV input on line 1.

 

what do this error means ??/

 

please advice

[tangwaichee 0]

Check out the example picture.

 

I hope this explains things a bit easier...

 

Hi

 

what do you mean by this error:-Invalid field count in CSV input on line 1.

 

cos im urgently need your help

[guywales 0]

im geting this error

MySQL said:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Model number"' at line 1

 

any ideas how i can get this sorted?

[nikao 0]

This is killin me - I manually added about 100 products to my store before I had the bright idea that mysql might be able to speed the process up. I've read your post herer and tried it several times and what keeps happening is this:

 

I backup / export to csv zipped / unzip / edit / remove header row / save as csv / import with all the details noted above.

 

When I do, I see the newly added products in my database in the products table, but they don't show up on site. Then I update the products to categories table and the "Featured Products" on the home page, which shows newly added products, is suddenly populated with missing image icons. When you click on the little "x" - missing image icon you get a details page that says "Product not found!"...

 

Any ideas for a stupid newbie?

 

Also, I don't see any of the "step2" or "step3" illustrations listed by tangwaichee above...here is my version info:

 

phpMyAdmin - 2.9.2

MySQL client version: 5.0.27

Used PHP extensions: mysql