List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 9 1999 5:58pm
Subject:Re: LOAD DATA 2
View as plain text  
On Sat, 1999-10-09 11:40:26 -0500, Ralph Schindler wrote:
> [...] i have a table defined as -
> 
> create table job_search (
>   id int not null primary key auto_increment,
>   name VARCHAR(55),
>   url VARCHAR(40),
>   description VARCHAR(255),
>   updated timestamp
> );
> 
> and i have a file of two things: name (tab) then the url (line break)
> 
> how do i load this file into just the two fields named [name, url]?

LOAD DATA
  LOCAL INFILE 'filename'
  INTO TABLE job_search
  (name, url)
;

Chapter "7.15 LOAD DATA INFILE syntax" of the MySQL Reference Manual
has an example on this, too:

| If you wish to load only some of a table's columns, specify a field
| list:
| 
| mysql> LOAD DATA INFILE 'persondata.txt'
|            INTO TABLE persondata (col1,col2,...);
| 
| You must also specify a field list if the order of the fields in the
| input file differs from the order of the columns in the table.
| Otherwise, MySQL cannot tell how to match up input fields with table
| columns.
| 
| If a row has too few fields, the columns for which no input field is
| present are set to default values.


> the second problem:
> i have an old access database made of completely test fields.. i
> need to convert it but it is some 1200 records of 53 fields each...
> in the mysql db, i have defined date fields and enum data types...
> what the user of the db did was in one field if it was Y, she would
> mard and x in the field, if not, it would be left blank.  so i need
> to change all fields that have contents to 'Y' and all without to
> 'N'... also the data fields are entered rather randomly between
> having MM/YY and DD/MM/YY some have 'YY -- can i convert this?

It's maybe easiest, to convert the data first, either within Access or
exported in a text file and edited there, and only then import it into
MySQL.

Otherwise, I'd create a temporary table all with CHAR or VARCHAR
fields and import the data into this table.  Then you can update the
table content to fit the needed format, e.g. for the enum columns you
could do:
  UPDATE tmp SET ef = IF( ef='x', 'Y', 'N');
Or for the date columns:
  UPDATE tmp SET df = (1900+df)*10000
   WHERE LENGTH(df)=2; /* "YY" */
  UPDATE tmp SET df = (1900+MID(df,4,2))*10000
                           +MID(df,1,2)*100
   WHERE LENGTH(df)=5; /* "MM/YY" */
  UPDATE tmp SET df = (1900+MID(df,7,2))*10000
                           +MID(df,4,2)*100
                           +MID(df,1,2)
   WHERE LENGTH(df)=8; /* "DD/MM/YY" */

If all data is in correct format, copy it over to the real table:
  SELECT INTO realtable SELECT * FROM tmp;

If you want, you could do everything in a single UPDATE, of course,
using more IFs and updating several fields at once ... but this is
going to be a quite large query (not really complicated, but laaarge).


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
LOAD DATA 2Ralph Schindler9 Oct
  • Re: LOAD DATA 2Bob Kline9 Oct
  • Re: LOAD DATA 2Martin Ramsch9 Oct