110 replies to this topic

[thewrath1]

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.
- 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. :-) [color=#990000] [color=#990000]

[bandwith]

thewrath1, on Sep 25 2005, 09:43 PM, said:

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.
- 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. :-) [color=#990000] [color=#990000]

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

[ozstar]

thewrath1, on Sep 25 2005, 09:43 PM, said:

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.
- 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. :-) [color=#990000] [color=#990000]

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

[newquay00]

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

[keithratliff]

newquay00, on Nov 14 2005, 02:19 PM, said:

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?

[keithratliff]

keithratliff, on Nov 15 2005, 10:01 PM, said:

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.

[JustPhish]

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.

Edited by JustPhish, 16 November 2005, 15:00.

[JustPhish]

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.

[JustPhish]

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?

[thewrath1]

JustPhish, on Nov 16 2005, 08:50 PM, said:

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.

[crafted4you]

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

[thewrath1]

keithratliff, on Nov 16 2005, 03:50 AM, said:

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

[crafted4you]

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.

[JustPhish]

thewrath1, on Nov 23 2005, 12:50 AM, said:

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.

[thewrath1]

crafted4you, on Nov 24 2005, 11:23 AM, said:

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.

[AlanR]

I'm posting this as a reference for anyone who might have problems with CSV formats and Excel.

Saving XL files as Text/CSV

Edited by AlanR, 29 November 2005, 17:56.

[thewrath1]

AlanR, on Nov 29 2005, 05:53 PM, said:

I'm posting this as a reference for anyone who might have problems with CSV formats and Excel.

Saving XL files as Text/CSV

Thanks Alan

[ShootPaint]

crafted4you, on Nov 23 2005, 03:38 PM, said:

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.

[fallingman]

keithratliff, on Nov 16 2005, 03:50 AM, said:

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

[thewrath1]

fallingman, on Dec 5 2005, 10:20 AM, said:

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...
[img]http://www.euro-guard.co.uk/images/instructions_csv_idiots.gif[/img]