Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
INSERT INTO phonebook VALUES('01', 'John', 'Doe', 'North America')

I have a table of records and it is A LOT. Inserting it one by one through phpMyadmin is a tedious job. I was thinking maybe there would be an excel formula for it where every word after commas are replaced. So I cant just copy the codes then run it on sql.

share|improve this question

migrated from Jan 27 '13 at 8:35

This question came from our site for professional and enthusiast programmers.

What format is the data in? – Strawberry Jan 23 '13 at 8:42
Its in .doc format. Its in a table. So I could paste it on excel so it'd be easier. – jeffmangum Jan 23 '13 at 8:52
Copy to excel, then use string concatenation and cell references to create the INSERT stamements... – ppeterka Jan 23 '13 at 11:43
up vote 1 down vote accepted

Put all your data in a CSV file or a plain text files. Assuming that fields/columns are separeted by TAB and lines/records sepeareted by NEWLINE. Save it on disk e.g. C:\phoneData.csv

Execute the following statement

LOAD DATA INFILE 'C:/phoneData.csv' INTO TABLE yourtablename

Remember that TAB and NEWLINE spearators are not neccessary to be used, you can use alternate terminators/spearators. See the following link for more details


Hope this will help you.

share|improve this answer
Thank you! Right now i cant test to see if it works since im on a pc with no xampp. But i made a csv file and it looks like this: Will it work? Should I delete the column headers? – jeffmangum Jan 23 '13 at 9:01
Yes it should work. For sake of ease copy the excel sheet and paste it in notepad and then save the file. (As it will do the tab and new line separation automatically in this way). You can delete the header or append "IGNORE 1 LINES" at the end of above stated LOAD statement. – Mian Zeshan Farooqi Jan 23 '13 at 9:05
So a .txt works too? – jeffmangum Jan 23 '13 at 9:09
@jeffmangum I suppose the only difference between text and CSV is the file extension - at least for your case. – Peter L. Jan 23 '13 at 9:12
Yes, it doesn't matter what is the extension of file. The only thing that matters is the format of data inside that file. – Mian Zeshan Farooqi Jan 23 '13 at 9:12

You must log in to answer this question.