I cant tell you how much i love you right now :)
This works flawless!! Thanks a million times!
From: Harrison Fisk <[EMAIL PROTECTED]>
To: "Jessica Svensson" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: LOAD DATA and skip columns in text file...
Date: Wed, 25 May 2005 20:05:40 -0400
Hi,
On May 25, 2005, at 7:38 PM, Jessica Svensson wrote:
Wow!! Thanks!
I will upgrade right away... but i can't find any information in the url
you supplied about how to specify what fields from the external file to
read...
It will read all of them, but you can just specify a variable for ones you
don't care about and have it ignore them.
So if my text file has 5 columns, but I only want 3 you can say:
LOAD DATA INFILE 'data.txt' INTO TABLE tmp (a, @useless, @ignoreme, b, c)
Then you get the first field as a, and the 4th and 5th as b and c
respectively. The @useless and the @ignoreme variables are just thrown
out, since you don't use them. I haven't actually tested this, but it
should work that way from what I have seen.
The other neat new thing is that you can now pre-process the data before it
is loaded. For example, you can use STR_TO_DATE to "fix" a datetime field
before it is loaded into the table directly.
will be quite hard to figure out on my own i guess?
Thanks again!
From: Harrison Fisk <[EMAIL PROTECTED]>
To: "Jessica Svensson" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: LOAD DATA and skip columns in text file...
Date: Wed, 25 May 2005 19:03:20 -0400
Hi,
On May 25, 2005, at 1:28 PM, Jessica Svensson wrote:
That just complicates things alot since i get around 200 files, 6 times
a day via an automated process and every textfile looks different from
the other. To just have different load data would make it much easier.
I have read alot of questions about just this and many people is asking
for this feature. Thats why i'm woundering if it really havent been
impemented in these 5 years that have passed.
The ability to do this was just implemented recently in MySQL 5.0, along
with some other enhancements to LOAD DATA INFILE in 5.0. I don't think
the documentation has been updated yet to include the new information
though. Hopefully it will be soon.
You see the changeset and a preliminary description at
http://lists.mysql.com/internals/23068 That should be able to do what
you want (and more cool stuff!)
Prior to that version, you would have to do as he recommended below with
a separate staging table.
From: Harald Fuchs <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: LOAD DATA and skip columns in text file...
Date: 25 May 2005 13:24:55 +0200
In article <[EMAIL PROTECTED]>,
"Jessica Svensson" <[EMAIL PROTECTED]> writes:
> LOAD DATA and skip columns in text file...
> What i have found out is that this is not possible in any existing
> version of mysql, correct?
> I found a message from Sinisa Milivojevic @ MySQL AB dated
06/29/2000
> while searching on google.
> It said "We have entered this on our TODO list one month ago."
> So that would be almost exactly 5 years ago... is it really that
this
> function has not been implemented during these 5 years? If so, then
i
> guess it could be 5 more years before its impelemented and that i
> should maybe look for other solutions.
Maybe they didn't implement it yet because there's an easy workaround:
create a temporary table including the columns to be skipped, LOAD it,
and then use INSERT..SELECT to copy over only the columns you're
interested in.
Habbo Hotel http://habbohotel.msn.se/habbo/sv/channelizer Checka in här!
Regards,
Harrison
--
Harrison C. Fisk, Trainer and Consultant
MySQL AB, www.mysql.com
Get a jumpstart on MySQL Cluster --
http://www.mysql.com/consulting/packaged/cluster.html
_________________________________________________________________
Hitta rätt på nätet med MSN Search http://search.msn.se/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
