trying to read in data from file

------=_NextPart_000_0004_01C6D2AB.068AFB50
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit


I have My SQL 5.0.21 and either the GUI MySQL Query tool or phpMySQLAdmin to
run queries via Apache 1.3 on a Win XP SP2 computer.

I am trying to load some data from a text file with comma separators and
Windows line ends - it is exported from an Approach database which escapes
double quotes with double quotes. As far as I know this works just like a
slash \.

I ran this query

SQL query:

LOAD DATA LOCAL INFILE 'C:\\etc.\\My Documents\\the_file.txt' INTO TABLE
chng
(IssueNo, Head, Chardate, auth_id, PaperName)
FIELDS TERMINATED BY ','
FIELDS OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n';

and got the errors

Documentation
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'FIELDS TERMINATED BY ',' FIELDS OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY' at line 3

I have looked at the manual but cannot see what I am doing wrong.

Please would the experts out there spot my beginners mistakes?

Regards


Adrian

------=_NextPart_000_0004_01C6D2AB.068AFB50--

Re: trying to read in data from file

adrian GREEMAN wrote:
> I have My SQL 5.0.21 and either the GUI MySQL Query tool or phpMySQLAdmin to
> run queries via Apache 1.3 on a Win XP SP2 computer.
>
> I am trying to load some data from a text file with comma separators and
> Windows line ends - it is exported from an Approach database which escapes
> double quotes with double quotes. As far as I know this works just like a
> slash \.
>
> I ran this query
>
> SQL query:
>
> LOAD DATA LOCAL INFILE 'C:\\etc.\\My Documents\\the_file.txt' INTO TABLE
> chng
> (IssueNo, Head, Chardate, auth_id, PaperName)
> FIELDS TERMINATED BY ','
> FIELDS OPTIONALLY ENCLOSED BY '\"'
> LINES TERMINATED BY '\r\n';
>
> and got the errors
>
> Documentation
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> 'FIELDS TERMINATED BY ',' FIELDS OPTIONALLY ENCLOSED BY '\"'
> LINES TERMINATED BY' at line 3
>
> I have looked at the manual but cannot see what I am doing wrong.
>
> Please would the experts out there spot my beginners mistakes?

The syntax shown at:

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

indicates that your column list should *follow* the field and line
specifiers. Other than that, I think it should be okay. You might
not need the backslash in front of " for the OPTIONALLY ENCLOSED BY.

--
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