Load Data InFile
I was having trouble the other day getting PHPMyAdmin to import a standard CSV file. It does that sometimes. I am not sure if it is my computer, the version installed or whatever, but it’s really annoying when you need to get your data into a mysql table. The great thing about mysql is that I can always fall back to command line, and I do on occasion “when all else fails”. The same thing happens with Windows too. I’ll be sad when there’s no commands to type in. Maybe at that point I learn some flavor of Linux for real. So, in order to gather up that data and pop it into the database, I had to run a command line ‘load data infile’ command. The only problem was that for as much as there’s written about the command, I couldn’t find all that I needed in one place, so I had to search around. For example, I had to learn that the designations for the data being read in must come before the column listing, which felt unnatural. I’m used to a standard insert statement where the column listing follows directly after the insert statement, and any further stuff goes after that.
My data was in the form of an Excel spreadsheet, which I saved as a CSV file, pipe (|) delimited. I find that the pipe delimiter is much nicer to work with when the data may have commas and quotes and things. Since we are on a Windows server, there are two things to remember. One, the path to the file must either use the unix slash or the double slash (unless you have a different escape character, and in that case you don’t need to read this anyway). Two, CSV files will have lines terminated by the newline and carriage return ‘\n\r’. Pretty basic stuff. So once I had my new table added to the database, I was able to proceed with the data load.
load data infile ‘C:\\somefolder\\Documents\\data.txt’
into table mytable
FIELDS TERMINATED BY ‘|’ ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n\r’