beegud2 Posted January 8, 2007 Share Posted January 8, 2007 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 Link to comment Share on other sites More sharing options...
newtech Posted January 9, 2007 Share Posted January 9, 2007 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. Link to comment Share on other sites More sharing options...
Guest Posted March 28, 2007 Share Posted March 28, 2007 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: Link to comment Share on other sites More sharing options...
indoinvegas Posted June 17, 2007 Share Posted June 17, 2007 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 Link to comment Share on other sites More sharing options...
thewrath1 Posted June 18, 2007 Author Share Posted June 18, 2007 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 ;-) 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 More sharing options...
shubbert Posted June 30, 2007 Share Posted June 30, 2007 I cant seem to find the phpadmin. where is it located? EXPORT CSVTo 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: Link to comment Share on other sites More sharing options...
shubbert Posted June 30, 2007 Share Posted June 30, 2007 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 Link to comment Share on other sites More sharing options...
Guest Posted June 30, 2007 Share Posted June 30, 2007 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 Link to comment Share on other sites More sharing options...
andycnelson Posted July 1, 2007 Share Posted July 1, 2007 I think I'm doing all of this correctly, but it only imports the first row of my CSV data. Thoughts? Link to comment Share on other sites More sharing options...
shubbert Posted July 3, 2007 Share Posted July 3, 2007 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. Link to comment Share on other sites More sharing options...
shubbert Posted July 16, 2007 Share Posted July 16, 2007 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. Link to comment Share on other sites More sharing options...
Guest Posted August 3, 2007 Share Posted August 3, 2007 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 Link to comment Share on other sites More sharing options...
Guest Posted August 3, 2007 Share Posted August 3, 2007 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) Link to comment Share on other sites More sharing options...
ctec2001 Posted August 16, 2007 Share Posted August 16, 2007 EXPORT CSVTo 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. Do or Do Not, there is no try. Link to comment Share on other sites More sharing options...
newbie06 Posted August 20, 2007 Share Posted August 20, 2007 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! Link to comment Share on other sites More sharing options...
twistedindustries Posted September 11, 2007 Share Posted September 11, 2007 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? Link to comment Share on other sites More sharing options...
shubbert Posted September 19, 2007 Share Posted September 19, 2007 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 CSVTo 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: Link to comment Share on other sites More sharing options...
Guest Posted September 20, 2007 Share Posted September 20, 2007 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 Link to comment Share on other sites More sharing options...
fatriff Posted September 20, 2007 Share Posted September 20, 2007 are you people daft? what is phpmyadmin? you will find it in your website hosting control panel.. it is where your databases are stored. Link to comment Share on other sites More sharing options...
Envydia Posted October 30, 2007 Share Posted October 30, 2007 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... Link to comment Share on other sites More sharing options...
No Posers Please! Posted October 30, 2007 Share Posted October 30, 2007 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 Link to comment Share on other sites More sharing options...
tangwaichee Posted December 21, 2007 Share Posted December 21, 2007 Hi im having this error : Invalid field count in CSV input on line 1. what do this error means ??/ please advice Link to comment Share on other sites More sharing options...
tangwaichee Posted December 22, 2007 Share Posted December 22, 2007 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 Link to comment Share on other sites More sharing options...
Guest Posted January 11, 2008 Share Posted January 11, 2008 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? Link to comment Share on other sites More sharing options...
Guest Posted January 18, 2008 Share Posted January 18, 2008 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.