MySQL Forums
Forum List  »  Newbie

Re: Importing a CSV file into a MySQL Table
Posted by: Jay Alverson
Date: January 03, 2009 08:31AM

Well, if you look at the syntax for the LOAD DATA statement you'll notice the last two lines of it:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]


This means you can specify the columns which the data is going into, in the order
from left to right of the columns in the file.

You shouldn't have to worry about the AUTO_INCREMENT field.

The date isn't in the correct format though. You need the CSV to look like

YYYY-MM-DD for the dates...

I added an extra column to keep the original date string...

mysql> 
mysql> use test;
Database changed
mysql> 
mysql> drop table if exists import;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create table import (
    -> id mediumint auto_increment,
    -> the_date date,
    -> date_str varchar(50),
    -> high double,
    -> low double,
    -> close double,
    -> primary key (id));
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> describe import;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | mediumint(9) | NO   | PRI | NULL    | auto_increment | 
| the_date | date         | YES  |     | NULL    |                | 
| date_str | varchar(50)  | YES  |     | NULL    |                | 
| high     | double       | YES  |     | NULL    |                | 
| low      | double       | YES  |     | NULL    |                | 
| close    | double       | YES  |     | NULL    |                | 
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> 
mysql> LOAD DATA INFILE 'J:/Temporary/Import-Test.txt' INTO TABLE import
    -> fields terminated by ','
    -> lines  terminated by '\r\n'
    -> (date_str, high, low, close);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> 
mysql> select * from import;
+----+----------+------------+------+------+-------+
| id | the_date | date_str   | high | low  | close |
+----+----------+------------+------+------+-------+
|  1 | NULL     | 12/29/2008 |  2.8 | 2.92 |  2.78 | 
|  2 | NULL     | 12/22/2008 | 2.93 | 2.99 |  2.65 | 
|  3 | NULL     | 12/15/2008 |  2.8 | 3.05 |   2.7 | 
+----+----------+------------+------+------+-------+
3 rows in set (0.00 sec)

mysql> 
mysql> notee

Unless one of the SQL Wizards knows how to convert your date format into a DATE data.

>

Thanks, Jay

Options: ReplyQuote


Subject
Written By
Posted
Re: Importing a CSV file into a MySQL Table
January 03, 2009 08:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.