Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite 
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
What format is the data in? – Strawberry Jan 23 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 at 8:52
Copy to excel, then use string concatenation and cell references to create the INSERT stamements... – ppeterka Jan 23 at 11:43

migrated from stackoverflow.com Jan 27 at 8:35

1 Answer

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

MySQL Load Data INFILE

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: i.imgur.com/UBqumuR.png Will it work? Should I delete the column headers? – jeffmangum Jan 23 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 at 9:05
So a .txt works too? – jeffmangum Jan 23 at 9:09
1  
@jeffmangum I suppose the only difference between text and CSV is the file extension - at least for your case. – Peter L. Jan 23 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 at 9:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.