MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL Upload from CSV

with 6 comments

You have one approach to uploading CSV file data into a MySQL database. You use the LOAD statement. There two caveats about using the LOAD command, and they are:

  • You must convert dates to the default MySQL format – YYYYMMDD, or the four digit year, two digit month, and two digit day.
  • You can’t use AUTO_INCREMENT from the source to the loading table.

It’s untrue that you must input dates as strings and convert them in the database. However, you can do that if you like. I’d suggest you open the file in Microsoft Excel, then create a column with the correct data format. You can do that by creating a custom format, or with a formula. I figure the custom format is easiest but sometimes a formula is necessary for applications.

You should note that loading a date at the end of a line has problems in MySQL. The easiest fix that I’ve found is to place a comma at the end of each line. In a Microsoft world, that eliminates the need for the \r from the LINES TERMINATED BY clause.

While this shows a trivial amount of data, here’s a CSV file to use in the test case. Create a directory like Data off your base logical drive or mount point. Then create a file named transaction_upload.csv and put the following data set in it.

20090102,5
20091231,5
20091128,5
20090616,5

Create an importing.sql file in the C:\Data folder with the contents below. It’s only a recommendation but generally import tables aren’t useful in between uploads, and that’s why I create the table as an in-memory table. In-memory tables are dropped when you recycle (boot) the MySQL instance.

-- Conditionally drop the table.
DROP TABLE IF EXISTS transaction_upload;
 
-- Create the new upload target table.
CREATE TABLE transaction_upload
( transaction_date   DATE
, transaction_amount FLOAT ) ENGINE=MEMORY;
 
-- Load the data from a file, don't forget the \n after the \r on Windows or it won't work.
LOAD DATA INFILE 'c:/Data/mysql/transaction_upload.csv'
INTO TABLE transaction_upload
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';
 
-- Select the uploaded records.
SELECT * FROM transaction_upload;

Before you connect as anything other than the root account, you’ll need to grant the global FILE privilege to the targeted user. This is true because you’ve not qualified a LOCAL file in the import statement. Users that have all privileges on a given database also have read-write access to LOCAL tables, which is read write to client-side files. When the LOCAL key word is omitted, you’re instructing a read from the server by the client tool. You must therefore grant a global permission to enable a connected user can call a server-side file.

If you want to import without granting any additional global permissions, you can rewrite the statement like this:

LOAD DATA LOCAL INFILE 'c:/Data/mysql/transaction_upload.csv'
INTO TABLE transaction_upload
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

If you don’t want to rewrite the statement, you’ll get the following error:

ERROR 1045 (28000): Access denied FOR USER 'student'@'localhost' (USING password: YES)
Empty SET (0.01 sec)

Run the script:

mysql> \. importing.SQL

Then, you should see the following:

+------------------+--------------------+
| transaction_date | transaction_amount |
+------------------+--------------------+
| 2009-01-02       |                  5 |
| 2009-12-31       |                  5 |
| 2009-11-28       |                  5 |
| 2009-06-16       |                  5 |
+------------------+--------------------+
4 rows in set (0.00 sec)

Another quick tidbit, dates can be tricky when they’re the last column in the file. The best solution is to put a comma at the end of each string. If you put the comma at the end of each line, you can also dispense with the \r on the Windows platform.

Written by maclochlainn

June 16th, 2009 at 6:42 pm

6 Responses to 'MySQL Upload from CSV'

Subscribe to comments with RSS or TrackBack to 'MySQL Upload from CSV'.

  1. Hi, what do you do if you want to simply upload text and numerical values from csv to MySQL?

    Julia

    4 Apr 10 at 8:02 pm

  2. More or less, it is the same process. You simply create the .csv file with numbers and strings. If the strings don’t have white space they don’t need to be enclosed in double quotes but they should if there is white space. Just define the columns for number as float if they’ve got decimal components, instead of integers.

    maclochlainn

    4 Apr 10 at 11:22 pm

  3. hi..my problem is to upload 1 csv to two different tables? is this possible

    iza

    8 Apr 10 at 5:41 am

  4. Small correction to your excel formula as it doesn’t account for December, and makes it ’012′ in result:

    =CONCATENATE(CONCATENATE(“”,YEAR(A1)),IF(LEN(MONTH(A1))=1,CONCATENATE(“0″,MONTH(A1)),CONCATENATE(“”,MONTH(A1))),IF(LEN(DAY(A1)) = 1,CONCATENATE(“0″,DAY(A1)),CONCATENATE(“”,DAY(A1))))

    Gene

    30 Nov 10 at 1:00 pm

  5. Good help.

    Thida

    19 Jun 13 at 9:26 pm

Leave a Reply