List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 11 1999 2:30am
Subject:Re: Loading Date and Time information
View as plain text  
On Sun, 1999-10-10 21:35:56 -0400, Brian E. Seppanen wrote:
> CREATE TABLE bbs (
> time TIME NOT NULL,
> date DATE NOT NULL,
> location CHAR(1) NOT NULL,
> firstname CHAR(25) NOT NULL,
> lastname CHAR(35) NOT NULL,
> PRIMARY KEY (time));
>  
> And my table is created fine.  I've read the information about the data
> types, but when I try to do 
> 
> load data local infile "test_data_file" into table bbs;
> 
> When loading a text file as below:
> 06:00:00,1999-10-31,'h','joe','schmoe'  -- it skips the information that
> I'm trying to enter in.  
> 
> When loading a text file as below:
> '06:00:00','19991031','h','joe','schmoe' -- it puts all nulls into time
> and date and skips the location, firstname, and lastname with a warning
> (Another question is where are the warnings displayed).

This is not a problem with date or time fields, but how to use the
LOAD DATA command!

You have to specify that you wish to seperate fields by commas instead
of the default tabulators, and that you're sometimes (or always) using
single quotes to enclose values ...

  LOAD DATA
    LOCAL INFILE 'test_data_file'
    INTO TABLE bbs
    FIELDS SEPERATED BY ','
    OPTIONALLY ENCLOSED by "'";

The details are in chapter "7.15 LOAD DATA INFILE syntax" of the MySQL
Reference Manual ... ;-)

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
Thread
Loading Date and Time informationBrian E. Seppanen11 Oct
  • Re: Loading Date and Time informationMartin Ramsch11 Oct