Fields terminated problem

Please excuse me if this is a common problem - there is so much on the list
that I have not worked out how to check back

I have Win XP SP2
MySQL5
Apache2
and PHP5
All very recent.

Anyway IO am trying to import some values from a text file

I use :

LOAD DATA INFILE 'C:/xxxx/My Documents/yyy/datafile.TXT'
INTO TABLE chng(IssueNo, Head, chrdate, auth_id, PaperName)
FIELDS TERMINATED BY ':::';

(I have substituted xxx for the full path which I used)

But it will not run - just gives me ---
"You have an error in your SQL syntax.................near fields terminated
by at line 3"

I tried it with MySQL Query Browser and phpMyadmin, though in each case
writing the SQL so it could have been command line also.

But I cannot find out why this is the wrong syntax - it seems to be what the
MySQL cookbook recommends and the manual.
I tried also LOAD DATA LOCAL INFILE but same result
Can anyone help and is this a common issue?

Regards


AdrianG




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Fields terminated problem

OK, I'm not sure and I don't have the time to test it myself (you can
do it if you must), but I don't see any reference of fields from the
table in the syntax, just the table name.

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name =3D expr,...)]

(quoted from the MySQL Manual)

I'm not sure if you can load specific fields...

On 12/2/05, Adrian Greeman wrote:
> Please excuse me if this is a common problem - there is so much on the l=
ist
> that I have not worked out how to check back
>
> I have Win XP SP2
> MySQL5
> Apache2
> and PHP5
> All very recent.
>
> Anyway IO am trying to import some values from a text file
>
> I use :
>
> LOAD DATA INFILE 'C:/xxxx/My Documents/yyy/datafile.TXT'
> INTO TABLE chng(IssueNo, Head, chrdate, auth_id, PaperName)
> FIELDS TERMINATED BY ':::';
>
> (I have substituted xxx for the full path which I used)
>
> But it will not run - just gives me ---
> "You have an error in your SQL syntax.................near fields termina=
ted
> by at line 3"
>
> I tried it with MySQL Query Browser and phpMyadmin, though in each case
> writing the SQL so it could have been command line also.
>
> But I cannot find out why this is the wrong syntax - it seems to be what =
the
> MySQL cookbook recommends and the manual.
> I tried also LOAD DATA LOCAL INFILE but same result
> Can anyone help and is this a common issue?
>
> Regards
>
>
> AdrianG
>
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=3Ddanieldaveiga@gma=
il.com
>
>


--
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Fields terminated problem

--=_alternative 0067109B852570CB_=
Content-Type: text/plain; charset="US-ASCII"

(response intermingled) Yep, the fields are there. Follow me....

Daniel da Veiga wrote on 12/02/2005 12:15:17 PM:

> OK, I'm not sure and I don't have the time to test it myself (you can
> do it if you must), but I don't see any reference of fields from the
> table in the syntax, just the table name.
>
> LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
> [REPLACE | IGNORE]
> INTO TABLE tbl_name
> [FIELDS
> [TERMINATED BY 'string']
> [[OPTIONALLY] ENCLOSED BY 'char']
> [ESCAPED BY 'char' ]
> ]
> [LINES
> [STARTING BY 'string']
> [TERMINATED BY 'string']
> ]
> [IGNORE number LINES]
>>>> [(col_name_or_user_var,...)] <<<< here they are.
> [SET col_name = expr,...)]
>
> (quoted from the MySQL Manual)

It looks like he got his field list and his terminator descriptors out of
sequence. It should have read like this:

LOAD DATA...
INTO TABLE ...
FIELDS TERMINATED ....
(...column list...)

That's why it was confused to find FIELDS terminated after the () and
threw an error. Just another reason to keep a link to the manual handy and
to always check your syntax against what's published there. If an example
is broken, it may have been written for an older version or a different
product. Always check yourself against the manual.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


>
> I'm not sure if you can load specific fields...
>
> On 12/2/05, Adrian Greeman wrote:
> > Please excuse me if this is a common problem - there is so much on
the list
> > that I have not worked out how to check back
> >
> > I have Win XP SP2
> > MySQL5
> > Apache2
> > and PHP5
> > All very recent.
> >
> > Anyway IO am trying to import some values from a text file
> >
> > I use :
> >
> > LOAD DATA INFILE 'C:/xxxx/My Documents/yyy/datafile.TXT'
> > INTO TABLE chng(IssueNo, Head, chrdate, auth_id, PaperName)
> > FIELDS TERMINATED BY ':::';
> >
> > (I have substituted xxx for the full path which I used)
> >
> > But it will not run - just gives me ---
> > "You have an error in your SQL syntax.................near fields
terminated
> > by at line 3"
> >
> > I tried it with MySQL Query Browser and phpMyadmin, though in each
case
> > writing the SQL so it could have been command line also.
> >
> > But I cannot find out why this is the wrong syntax - it seems to be
what the
> > MySQL cookbook recommends and the manual.
> > I tried also LOAD DATA LOCAL INFILE but same result
> > Can anyone help and is this a common issue?
> >
> > Regards
> >
> >
> > AdrianG
> >
> >
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: http://lists.mysql.com/win32?
> unsub=danieldaveiga@gmail.com
> >
> >
>
>
> --
> Daniel da Veiga
> Computer Operator - RS - Brazil
> -----BEGIN GEEK CODE BLOCK-----
> Version: 3.1
> GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
> PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
> ------END GEEK CODE BLOCK------
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 0067109B852570CB_=--

RE: Fields terminated problem

Many thanks - the resequencing did it.

AdrianG


-----Original Message-----
From: SGreen@unimin.com [mailto:SGreen@unimin.com]
Sent: 02 December 2005 18:46
To: Daniel da Veiga
Cc: MySQL Win32 List
Subject: Re: Fields terminated problem

(response intermingled) Yep, the fields are there. Follow me....

Daniel da Veiga wrote on 12/02/2005 12:15:17 PM:

> OK, I'm not sure and I don't have the time to test it myself (you can
> do it if you must), but I don't see any reference of fields from the
> table in the syntax, just the table name.
>
> LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
> [REPLACE | IGNORE]
> INTO TABLE tbl_name
> [FIELDS
> [TERMINATED BY 'string']
> [[OPTIONALLY] ENCLOSED BY 'char']
> [ESCAPED BY 'char' ]
> ]
> [LINES
> [STARTING BY 'string']
> [TERMINATED BY 'string']
> ]
> [IGNORE number LINES]
>>>> [(col_name_or_user_var,...)] <<<< here they are.
> [SET col_name = expr,...)]
>
> (quoted from the MySQL Manual)

It looks like he got his field list and his terminator descriptors out of
sequence. It should have read like this:

LOAD DATA...
INTO TABLE ...
FIELDS TERMINATED ....
(...column list...)

That's why it was confused to find FIELDS terminated after the () and threw
an error. Just another reason to keep a link to the manual handy and to
always check your syntax against what's published there. If an example is
broken, it may have been written for an older version or a different
product. Always check yourself against the manual.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


>
> I'm not sure if you can load specific fields...
>
> On 12/2/05, Adrian Greeman wrote:
> > Please excuse me if this is a common problem - there is so much on
the list
> > that I have not worked out how to check back
> >
> > I have Win XP SP2
> > MySQL5
> > Apache2
> > and PHP5
> > All very recent.
> >
> > Anyway IO am trying to import some values from a text file
> >
> > I use :
> >
> > LOAD DATA INFILE 'C:/xxxx/My Documents/yyy/datafile.TXT'
> > INTO TABLE chng(IssueNo, Head, chrdate, auth_id, PaperName) FIELDS
> > TERMINATED BY ':::';
> >
> > (I have substituted xxx for the full path which I used)
> >
> > But it will not run - just gives me --- "You have an error in your
> > SQL syntax.................near fields
terminated
> > by at line 3"
> >
> > I tried it with MySQL Query Browser and phpMyadmin, though in each
case
> > writing the SQL so it could have been command line also.
> >
> > But I cannot find out why this is the wrong syntax - it seems to be
what the
> > MySQL cookbook recommends and the manual.
> > I tried also LOAD DATA LOCAL INFILE but same result Can anyone help
> > and is this a common issue?
> >
> > Regards
> >
> >
> > AdrianG
> >
> >
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: http://lists.mysql.com/win32?
> unsub=danieldaveiga@gmail.com
> >
> >
>
>
> --
> Daniel da Veiga
> Computer Operator - RS - Brazil
> -----BEGIN GEEK CODE BLOCK-----
> Version: 3.1
> GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
> PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ ------END
> GEEK CODE BLOCK------
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org