List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 22 2002 7:43pm
Subject:Re: MySQL Load Data Infile
View as plain text  
At 13:13 -0600 2/22/02, Craig Westerman wrote:
>I have a table that has following fields
>
>id (auto increment)
>date
>appleprice
>orangeprice
>pearprice
>
>When I try to LOAD DATA INFILE with this file using comma as field delimiter
>and newline as end of row

You need to add the column list:


... INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
(date, appleprice, orangeprice, pearprice)

Otherwise, LOAD DATA has no idea the id column is not present in the
data file.

>
>"2000-01-04","281.08","5.27","430.05"
>"2000-01-05","280.06","5.14","421.98"
>"2000-01-06","279.99","5.13","408.59"
>"2000-01-07","281.72","5.14","415.79"
>"2000-01-10","281.32","5.17","415.80"
>"2000-01-11","282.83","5.16","416.36"
>"2000-01-12","281.64","5.14","419.70"
>"2000-01-13","283.27","5.11","421.50"
>"2000-01-14","283.25","5.13","429.09"
>"2000-01-17","284.99","5.10","430.91"
>"2000-01-18","285.75","5.10","432.21"
>
>it only writes to one row in the table and seems to be adding the id fields
>and date fields. pearprice field is empty. This is result of the one row
>affected
>
>id       date       appleprice orangeprice pearprice
>127   2028-01-08       5.27     430.05
>
>Is my data formated incorrectly? Help!
>
>Can someone please show me example of their mysql data in a working
>situation?
>
>Thanks Craig

Thread
MySQL Load Data InfileCraig Westerman22 Feb
  • Re: MySQL Load Data InfileMichael Stassen22 Feb
  • Re: MySQL Load Data InfilePaul DuBois22 Feb