|
Trouble Importing Delimited Files into MySQL
This is an issue i just had to troubleshoot myself, and in my search for an answer I came across many threads posted by users with a similar predicament but little to no help.
Firstly, the ability to import delimited files (ie: CSV) into MySQL is obscurely hidden away at the bottom of the page when viewing the table structure... There is a line of text that read "INSERT DATA FROM A TEXT FILE INTO THE TABLE".
Clicking this option will present a form that allows you to select a file to upload to the server. It also allows you to define the delimiter, the carriage return marker, the escape character and an optional 'field encloser'.
The tricky part that catches most people of guard is the LOAD METHOD.
There are two options: DATA and DATA LOCAL.
Now most people assume that since they are uploading the file they want to import, the data is NOT local. Unfortunately, on most hosts, this will present you with a Access Denied error message.
You must choose DATA LOCAL if you get this error. The file will be uploaded to a /tmp directory on the server, and then MySQL will connect to the file to import the data, making it a LOCAL data transfer.
Alternatively, there are ways to import data into the table from a file already on the server using "LOAD DATA INFILE" but again, most hosts disable this feature for security reasons.
I just wanted to get this solution down somewhere so others looking for an answer don't have to go through what I did to get it to work.
Mike.
|