geroid Posted February 8, 2011 Share Posted February 8, 2011 Hi I have a table (names_eng) created with the following fields: order family species_name common_name species_author I also have a list of 10,000 records with five fields each that I have converted to a csv file in excel. The list looks something like this in excel (the first 8 records - 40 lines): STRUTHIONIFORMES Struthionidae Struthio camelus Common Ostrich Linnaeus, 1758 STRUTHIONIFORMES Struthionidae Struthio molybdophanes Somali Ostrich Reichenow, 1883 STRUTHIONIFORMES Rheidae Rhea americana Greater Rhea (Linnaeus, 1758) STRUTHIONIFORMES Rheidae Rhea pennata Lesser Rhea Orbigny, 1834 STRUTHIONIFORMES Casuariidae Casuarius casuarius Southern Cassowary (Linnaeus, 1758) STRUTHIONIFORMES Casuariidae Casuarius bennetti Dwarf Cassowary Gould, 1858 STRUTHIONIFORMES Casuariidae Casuarius unappendiculatus Northern Cassowary Blyth, 1860 STRUTHIONIFORMES Casuariidae Dromaius novaehollandiae Emu (Latham, 1790) Can anyone tell me the sql syntax to import this data from the csv file into the names_eng table so that the first 5 entries from the csv file become the first record in the names_eng table and the next 5 entries from the csv file become the 2nd record in the names_eng table etc. as shown below: order family species_name common_name species_author STRUTHIONIFORMES Struthionidae Struthio camelus Common Ostrich Linnaeus, 1758 STRUTHIONIFORMES Struthionidae Struthio molybdophanes Somali Ostrich Reichenow, 1883 STRUTHIONIFORMES Rheidae Rhea americana Greater Rhea (Linnaeus, 1758) STRUTHIONIFORMES Rheidae Rhea pennata Lesser Rhea Orbigny, 1834 STRUTHIONIFORMES Casuariidae Casuarius casuarius Southern Cassowary (Linnaeus, 1758) STRUTHIONIFORMES Casuariidae Casuarius bennetti Dwarf Cassowary Gould, 1858 STRUTHIONIFORMES Casuariidae Casuarius unappendiculatus Northern Cassowary Blyth, 1860 STRUTHIONIFORMES Casuariidae Dromaius novaehollandiae Emu (Latham, 1790) Any help would be great Thanks Quote Link to comment Share on other sites More sharing options...
geroid Posted February 8, 2011 Author Share Posted February 8, 2011 I forgot to mention that my mysql version is 5.5.8 Thanks Quote Link to comment Share on other sites More sharing options...
mikosiko Posted February 9, 2011 Share Posted February 9, 2011 the best way is using LOAD DATA INFILE read description here http://dev.mysql.com/doc/refman/5.0/en/load-data.html you could run the command twice... first time just IGNORE 5 lines... second time... for you to figure it out Quote Link to comment Share on other sites More sharing options...
geroid Posted February 9, 2011 Author Share Posted February 9, 2011 Thank you mikosiko I solved the problem by copying the word table into excel and simply using the import option in phpMYAdmin (from spreadsheet) and it brought in all the records as I wanted it. In the end it was really simple Thanks again Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.