MySQL provides a utility for importing text files into a database called mysqlimport. It has some nice features including:
- If you don’t include the ID in the data of the text file and your table is set up to auto-increment the ID then it will create the ID for you when importing the data.
- It recognizes different delimiters for the data, the common delimiters being tabs and commas.
- It will delete previous data.
An example of how to use it is:
mysqlimport --user=admin --password=admin --local --delete --verbose -c name,pos,age,b,tm,rel,dl,ab,r,h,d,t,hr,rbi,bb,k,sb,cs,'$$' --fields -terminated-by='\t' roto batters.txt
Note how in this example that the tab delimiter is specified by ‘\t’. Also special column names must be enclosed within quotes like ‘$$’. And finally the name of the text file you are using must be the same as the name of the table in which you are importing.
One frustrating thing about mysqlimport is that if there are warnings or errors during import it does not actually tell you what the errors are. mysqlimport does not have a problem with no data being present where it expects there to be some, for example in a comma delimited file if it sees ,, it just assumes that column should be blank or 0, depending on the data type.
However mysqlimport will count as a warning if it sees more data fields then you specified in the number of columns. This could happen because you forgot to specify a column or it could happen because your data has empty spaces at the end of the line.
To debug these warnings or errors what I do is import smaller sets of the data at a time to try to discover what is causing the problem. Usually the same pattern is causing the warning so once you discover what is the problem you can eliminate it from throughout the text file.