QBlog | Importing a comma delimited list to a MySQL table - uniQode

( 4 Votes )

Localization Engineering

How to import a comma delimited list into a MySQL table

Importing a comma delimited list (any kind of delimited list actually) to a MySQL database is a fairly straight forward process. Using a short SQL query you can easily have your table populated with the delimited list.

What is needed?
Delimited list file - If your data is in a different format like an Excel Spreadsheet, you should first export it as a delimited list.
phpMyAdmin - to make life easier. You can of course run the query from the command line

What to do?
Launch phpMyAdmin and log in. If you are working on a local server it is probably going to be at http://localhost/phpmyadmin

Click the SQL tab to enter your query. Copy/paste the code block below and modify the query per the instructions right after.

    LOAD DATA INFILE './MyDelimitedList.csv'
    INTO TABLE tablename_inmydatabase 
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, Text, Gender, Temperature);

Line1 - Name of the source file. In this case it is located at the root. On a local server, root is mysql\data folder.
Line2 - Name of the table that will accept the data
Line3 - Delimiter of your text file. If your list is tab delimited, it would be '\t'
Line4 - This is the delimiter between the rows in your file. In most cases it will be '\n' which means a line break
Line5 - Column mapping for our table. On each row of our delimited list, first item will be inserted under "ID" column, second under "Text" column and so on...

Click SQL on phpMyAdmin, paste this query, click "Go" and enjoy your updated table :)