Re: LOAD DATA and skip columns in text file...
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]
Re: LOAD DATA and skip columns in text file...
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA and skip columns in text file...
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... 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. 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 Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Mobile: +1 315 380-6048 Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html _ Chat: Ha en fest på Habbo Hotel http://habbohotel.msn.se/habbo/sv/channelizer Checka in här! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA and skip columns in text file...
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. 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 Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Mobile: +1 315 380-6048 Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA and skip columns in text file...
The folowing is out of the current MySQL manual. It looks like you could create an intermediate table with the fields you are interested in the front and "garbage" fields on the end. Then build a specific LOAD DATA INFILE with correct mapping for each file type [assuming you can tell this in your automated process]. Then after loading all 200 of your files run 1 INSERT...SELECT... with just the pertinent FIELDS from the intermediate table to your final table and TRUNCATE the intermediate table. It probably makes sense for the intermediate table to have minimal indexing so the initial loads will run faster. _ mysql> LOAD DATA INFILE 'persondata.txt' -> INTO TABLE persondata (col1,col2,...); You must also specify a column 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 an input line has too many fields, the extra fields are ignored and the number of warnings is incremented. If an input line has too few fields, the table columns for which input fields are missing are set to their default values. Default value assignment is described in section 13.2.6 CREATE TABLE Syntax. -Original Message- From: Jessica Svensson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 25, 2005 12:28 PM To: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... 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. >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. > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: >http://lists.mysql.com/[EMAIL PROTECTED] > _ Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA and skip columns in text file...
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. 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA and skip columns in text file...
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA and skip columns in text file...
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. Thanks! _ Nyhet! Hotmail direkt i din Mobil! http://mobile.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
