List:General Discussion« Previous MessageNext Message »
From:Neeraj Date:June 1 2006 12:10pm
Subject:RE: Problem in using LOAD DATA LOCAL INFILE
View as plain text  
Thanks for replaying 

 

That is what I am not able to figure out what to use in FIELDS TERMINATED
BY, LINES TREMINATED BY

 

My fields are terminated by ‘Ü’ and my lines are terminated by '¢’ and that
I have clearly mentioned in my query

 

LOAD DATA LOCAL INFILE 'd:\\data3.txt'  INTO TABLE dsc FIELDS TERMINATED  BY
'Ü\n' LINES TERMINATED BY '¢\n' (ch_code, name, info);

 

The sample of text file is 
===========================

Ü

 

 ¢

 

 Ch.Code  :02Ü

 

 MEATÜ

 

 

 

          1   COUNTRY    : JORDAN

 

 

          2   COUNTRY    : SAUDI ARABIA

 

 

 

 

 

 Ü

 

 ¢

 

 Ch.Code  :03Ü

 

 FISHÜ

 

 

 

          1   COUNTRY    : JORDAN

 

 

 

 

 Ü

 

 ¢

 

 Ch.Code  :04Ü

 

 FISHÜ

 

 

 

          1   COUNTRY    : JORDAN

 

 

          2   COUNTRY    : SAUDI ARABIA 

 

 

 

 ...

 

These are three records but there are more than 1000 records in my text
file..

 

Can you help

 

Cheers ………:-)

 

Neeraj Black Bits

  _____  

From: Harish TM [mailto:harish.tmh@stripped] 
Sent: Wednesday, May 31, 2006 9:54 PM
To: sheeri kritzer
Cc: Neeraj; mysql@stripped
Subject: Re: Problem in using LOAD DATA LOCAL INFILE

 

>You need to check your syntax.  You probably don't have FIELDS
>TERMINATED BY and [OPTIONALLY] ENCLOSED BY and ESCAPED BY set
>correctly.

True... those options are there for both LOAD DATA LOCAL INFILE and
mysqlimport 

example:
mysqlimport -u <user> -p<password> --local --fields-terminated-by='|'
<database> <file_to_import>
one thing here: if you are inserting the data into a table called "TABLE"
then your file should be called TABLE.<anything you like> 

for LOAD DATA... :
LOAD DATA LOCAL INFILE '<file   with complete path> INTO TABLE <table>
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

but the load data infile is messy especially if you installed MySql through
the source files. Better if you did through binaries but still not too good.


Unless you are going to use this load inside a program its probably better
to go ahead with mysqlimport


harish





On 5/31/06, sheeri kritzer <awfief@stripped> wrote:

You need to check your syntax.  You probably don't have FIELDS
TERMINATED BY and [OPTIONALLY] ENCLOSED BY and ESCAPED BY set
correctly.

http://dev.mysql.com/doc/refman/5.0/en/load-data.html 

-Sheeri

On 5/31/06, Neeraj <neeraj@stripped> wrote:
> Hi harish
> Thanks for reply 
>
> By executing this query for this table I am not getting any error.. 
> But I am getting wrong result.
> I have tried many combinations for LOAD DATA LOCAL INFILE but I am getting
> the results in two ways...
> 1. all the data getting loaded in first columns of first row. 
> Or
> 2. data for all columns getting loaded in first column only and getting
> error line now data for other columns
>
>
> Hope you will find the solution
>
> Cheers.:)
>
> Neeraj Black Bits
> -----Original Message-----
> From: Harish TM [mailto:harish.tmh@stripped
<mailto:harish.tmh@stripped> ]
> Sent: Tuesday, May 30, 2006 10:23 PM
> To: sheeri kritzer 
> Cc: Neeraj; mysql@stripped
> Subject: Re: Problem in using LOAD DATA LOCAL INFILE
>
> I went through a lot of trouble with Load data local infile... There are a

> lot of things regarding Local that MySql does not allow due to security
> reasons. As mentioned before this you could give us the error... that
might
> help
>
> alternatively try out mysqlimport 
>
> http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html
> 
>
> Its similar but a lot easier to use.
>
> However its a program so embedding it in code is not efficient.. 
>
> hope this helps
>
>
> harish
>
> On 5/30/06, sheeri kritzer <awfief@stripped  <mailto:awfief@stripped>
> >
wrote:
> >
> > What's the error you are getting?  What MySQL version are you using? 
> >
> > On 5/26/06, Neeraj <neeraj@stripped> wrote:
> > > Hi all
> > >
> > >
> > >
> > > I have a table with following structure 
> > >
> > >
> > >
> > > CREATE TABLE   `dsc` (
> > >
> > >   `ch_code` varchar(255) default NULL,
> > >
> > >   `name` varchar(255) default NULL, 
> > >
> > >   `inof` blob
> > >
> > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> > >
> > >
> > >
> > > And I have give a text file with following format 
> > >
> > >
> > >
> > > Ü
> > >
> > > ¢
> > >
> > > Ch.Code  :02Ü
> > >
> > > MEATÜ
> > > 
> > >
> > >
> > >          1   COUNTRY    : JORDAN
> > >
> > >
> > >          2   COUNTRY    : SAUDI ARABIA
> > >
> > >
> > > 
> > >
> > >
> > > Ü
> > >
> > > ¢
> > >
> > > Ch.Code  :03Ü
> > >
> > > FISHÜ
> > >
> > > 
> > >
> > >          1   COUNTRY    : JORDAN
> > >
> > >
> > >
> > >
> > > Ü
> > >
> > > ¢
> > >
> > > Ch.Code  :04Ü
> > >
> > > FISHÜ
> > >
> > >
> > >
> > >          1   COUNTRY    : JORDAN
> > >
> > >
> > >          2   COUNTRY    : SAUDI ARABIA 
> > >
> > >
> > >
> > > ...
> > >
> > >
> > >
> > > I am trying to import the text file into table. I have tried various
> > ways
> > > but unable to find the solution. the code I am trying to import the
file
> > is
> > > as follows
> > >
> > >
> > >
> > > LOAD DATA LOCAL INFILE 'd:\\data3.txt'  INTO TABLE dsc FIELDS
TERMINATED 
> > BY
> > > 'Ü\n' LINES TERMINATED BY '¢\n' (ch_code, name, info);
> > >
> > >
> > >
> > > Can any one help me in loading this file…
> > > 
> > >
> > >
> > > Regards
> > >
> > >
> > >
> > > Neeraj Black Bits
> > >
> > >
> > >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
<http://lists.mysql.com/mysql> 
> > To unsubscribe:
<http://lists.mysql.com/mysql?unsub=1
http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>

 


Thread
Problem in using LOAD DATA LOCAL INFILENeeraj26 May
  • Re: Problem in using LOAD DATA LOCAL INFILEsheeri kritzer30 May
    • Re: Problem in using LOAD DATA LOCAL INFILEHarish TM30 May
      • RE: Problem in using LOAD DATA LOCAL INFILENeeraj31 May
        • Take my name off your list !!Michael Price31 May
          • RE: Take my name off your list !!SST - Adelaide)31 May
          • Re: Take my name off your list !!Daniel da Veiga31 May
            • Re: Take my name off your list !!Daniel McQuay31 May
        • Re: Problem in using LOAD DATA LOCAL INFILEsheeri kritzer31 May
          • Re: Problem in using LOAD DATA LOCAL INFILEHarish TM31 May
            • RE: Problem in using LOAD DATA LOCAL INFILENeeraj1 Jun