Latest News: (loading..)

Archived

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

thewrath1

Export / Edit and Import an Excel CSV via phpmyadmin

111 posts in this topic

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:

Share this post


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

 

 

 

Please help me. I have successfully imported my products into the SQL database. Now, how do I get them to show up in my catalog?

 

Please don't recommend easypopulate to me (for me it has been no where near "easy"). I have worked for a week and still have not uploaded one product using that particular contribution, so I gave up on it. This way to me seems much easier. Now if I can only get the catalog to reflect my import!

 

Thank you in advance!

 

Thank you in advance

Share this post


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

 

 

 

 

This is the most understandable way to import I have seen thus far.

 

After what I have read in the forums about EasyPopulate, it scares me to even try.

 

The only thing about this is..

 

How best to change the columns in Excel to reflect the fields in Myql. so the data lands in the correct place.

 

Thanks

 

Oz

 

:)

Share this post


Link to post
Share on other sites

ok i managed to get the products added into the actual database but when searching through the admin or the store i just couldn't find them. did you have this problem iif so how do you get around it. and i agree the easy populate is a nightmare and just isn't easier at all

Share this post


Link to post
Share on other sites
ok i managed to get the products added into the actual database but when searching through the admin or the store i just couldn't find them. did you have this problem iif so how do you get around it. and i agree the easy populate is a nightmare and just isn't easier at all

 

I'm having the same problem as well. Any fixes for this guys?

Share this post


Link to post
Share on other sites
I'm having the same problem as well. Any fixes for this guys?

 

 

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.

Share this post


Link to post
Share on other sites

I am curious if this can be used to import the entire database as well rather than just the products?

 

I have currently exported my database from one domain as a csv file or Excel and I just now downloaded phpmyadmin (the test domain has it in it's tools, my current domain does not). So I am going to give this a shot.

 

Thank you very much for the info! It's a great help.

Share this post


Link to post
Share on other sites

Well this is turning out to be a disaster. I downloaded phomyadmin but I can't seem to figure out how to actualy install it. I went into the configure file like directed but I just don't see where I am supposed to put in the required info. That file is HUGE.

Share this post


Link to post
Share on other sites

OK I sat down and got it installed. I believe I put the correct information where it belonged and I ran phpmyadmin.

 

However, when I follow the instuctions given here this is the error I get.

 

#1083 - Field separator argument is not what is expected. Check the manual

 

I have replaced the ; with the , as the instructions said but it doesn't matter which way I do it, it always says the same error.

 

Anyone have any ideas?

Share this post


Link to post
Share on other sites
OK I sat down and got it installed. I believe I put the correct information where it belonged and I ran phpmyadmin.

 

However, when I follow the instuctions given here this is the error I get.

 

#1083 - Field separator argument is not what is expected. Check the manual

 

I have replaced the ; with the , as the instructions said but it doesn't matter which way I do it, it always says the same error.

 

Anyone have any ideas?

 

 

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.

Share this post


Link to post
Share on other sites

I want export selected bits of my database and copy them over to a new one but I have looked a these instructions and in phpmyadmin i dont get

 

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

click zipped

 

and even if I just select export to CSV for MS Excel when I open the file it has the who databases worth of info in and not just the table I have choose to brose.

 

I need help I have loads of products and dont want to have to add them all again on OSCommerce admin.

 

I want to move to a new database because this one was setup when I didnt have a clue about OSCommerce and I ran so may scripts on the database trying to install contributions that I have got a clue now what needs to be there and what doesnt and I am get errors every now and then.

 

Thanks

Dave

Share this post


Link to post
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.

 

Exactly Keith.

 

I can't explain every possible solution to everybody, but this is the way it works.

 

As a rule, I always keep all of my info for products/descriptions/categories in just one Excel File.

 

This is the easiest method, since you have a MASTER COPY to copy the information from, (pasting each new column into the corrresponding colums in each csv exported from tables in phpmyadmin.)

 

This way, when you update say 1000 prices one day, you do it in the MASTER COPY in excel first, save it as a csv, then choose the products table in phpmyadmin and import the file and only that column in your csv by typing its column name in the COLUMN NAMES box in the import feature.

 

If anybody doesn't understand what I am talking about. Then you are way out of your depth and I suggest you opt for the snail option and use EasyPopulate. :-)

Share this post


Link to post
Share on other sites

I have done as said but all I get is

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

Error

 

SQL-query :

 

LOAD DATA LOCAL INFILE '/var/tmp/php4P6JeT' REPLACE INTO TABLE `customers`

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

ESCAPED BY '\\'

LINES TERMINATED BY '\r\n'

 

MySQL said:

 

 

The used command is not allowed with this MySQL version

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

I have saved the file as cvs and changed the lines terminated by to a , when inserting from a file but still no joy.

Share this post


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

 

Thanks. I followed the directions initially laid out to the T and it just didn't work. The file was exported and saved correctly, etc etc. I paid great attention to detail.

 

On a happier note though, I was able to get done what I needed by first copying all the files from the old store to the new store. Then I ran the install and put in all the new databse info. Once this was done the new store had my old template style and all the new pages I had created in the info boxes. I then used the admin tool in the new store to restore a backed up database copy from the old store. It worked perfectly except I got the dreaded OSC loop. I fixed that with some corrections in the config files and I was in business.

Share this post


Link to post
Share on other sites
I have done as said but all I get is

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

Error

 

SQL-query :

 

LOAD DATA LOCAL INFILE '/var/tmp/php4P6JeT' REPLACE INTO TABLE `customers`

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

ESCAPED BY '\\'

LINES TERMINATED BY '\r\n'

 

MySQL said:

The used command is not allowed with this MySQL version

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

I have saved the file as cvs and changed the lines terminated by to a , when inserting from a file but still no joy.

 

I take it your trying to import a file to add customers to the customers table.

 

From what you wrote above, the DATA LOCAL INFILE - is this a csv? and if so save it to your desktop and import it from there.

 

If it is a csv file, open it in Notepad, it should read something like the below, every field with speach-marks and seperated with a , comma.

 

Like this;

 

"customers_id","customers_gender","customers_firstname","customers_lastname","customers_dob","customers_email_address","customers_default_address_id","customers_telephone","customers_fax","customers_password","customers_newsletter"

 

I'm not sure myself if you can import passwords, since they may be mapped to another table in pmadmin, but lets just take this for granted and asmume it is not a problem.

 

You also wrote;

 

ESCAPED BY '\\'

 

This should be a single backslash, unless you did this on purpose. ie: \

 

Hope this helps.

Share this post


Link to post
Share on other sites
I want export selected bits of my database and copy them over to a new one but I have looked a these instructions and in phpmyadmin i dont get

 

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

click zipped

 

and even if I just select export to CSV for MS Excel when I open the file it has the who databases worth of info in and not just the table I have choose to brose.

 

I need help I have loads of products and dont want to have to add them all again on OSCommerce admin.

 

I want to move to a new database because this one was setup when I didnt have a clue about OSCommerce and I ran so may scripts on the database trying to install contributions that I have got a clue now what needs to be there and what doesnt and I am get errors every now and then.

 

Thanks

Dave

 

I know when I was trying to import my CSV files I got the same error. I changed the seperator from a semi colon to a comma and still recieved the problem.

 

It wasnt until after I reread the direction I saw my problem.

On my PHPadmin screen there is a text link below that says add CSV text file, just like it states in the directions. When I click this link it gives me more options for the importing of files. Once I used this link all was good.

Share this post


Link to post
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.

 

Sorry, but I'm having this problem after a successful import within PHPAdmin.

 

Please could someone explain this a tad more clearly - I understand what's causing the problem, but the above instructions are a bit too enigmatic for me (no offence).

 

Many thanks in advance,

 

 

Andy

 

a.k.a. "fallingman"

 

http://www.fallingman.co.uk

Share this post


Link to post
Share on other sites
Sorry, but I'm having this problem after a successful import within PHPAdmin.

 

Please could someone explain this a tad more clearly - I understand what's causing the problem, but the above instructions are a bit too enigmatic for me (no offence).

 

Many thanks in advance,

Andy

 

a.k.a. "fallingman"

 

http://www.fallingman.co.uk

 

 

Check out the example picture.

 

I hope this explains things a bit easier...

instructions_csv_idiots.gif

Cyrusxxx likes this

Share this post


Link to post
Share on other sites

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 B)

Share this post


Link to post
Share on other sites

Many thanks for the tip.

 

Now expalain to me what the various columns:

(a)111 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

Share this post


Link to post
Share on other sites
Many thanks for the tip.

 

Now expalain to me what the various columns:

 

(a)111 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!)

================================

Share this post


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

Share this post


Link to post
Share on other sites

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

 

Philip

Share this post


Link to post
Share on other sites