Re: LOAD DATA problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jerome Smith wrote: Hello everybody, Im new to this list, and also new to MySQL. I would be very grateful if someone could help me with the following problem: I have MySQL server version 4.0.12 with a database on a Linux server. I work from a Windows XP client. I am trying to load data from a text file into a table on the server. I am using the LOAD DATA command and get the following error message: Database test - table RUBRO running on localhost Error SQL-query : LOAD DATA LOCAL INFILE '/tmp/php7oeURC' INTO TABLE `RUBRO` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' MySQL said: The used command is not allowed with this MySQL version The manual says that LOAD DATA command can be used with version 3.5 onwards, and I have 4.0.12. What could the trouble be? Thanks in advance. Jerome Smith It's actually a bug with the windows binaries (until version 4.0.13). This is fixed in 4.0.13 (which is the current stable build of MySQL). -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+2NVmtvXNTca6JD8RAiQFAJwJzqO+TfEbv9e7vLsZWd072dVnTgCdFIgR EkY+CRktLnc1YezXCA2IGxM= =Iakc -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Problem
* [EMAIL PROTECTED] Hie there, I'm having a potential problem with Load Data Infile function where i'm unable to get the right no. of records to appear in the table. I have a table created in MySQL with 2 fields region (char 1)-pkey and regionname (char 30). The table name is called region. I've also created a text file(Test.txt) with the following contents 1,New Record 1 2,New Record 2 3,New Record 3 My SQL load statement is: Load Data Infile 'test.txt' into table region fields enclosed by '' terminated by ,; The result: Query OK, 2 rows affected Records:2 Deleted:0 Skipped:0 Warnings:1 As you can see, only the 1st and 3rd lines of records are input in, the 2nd is ignoredany explanations? Try appending LINES TERMINATED BY '\r\n' to your query... \r\n is the line ending characters on a windows machine, mysql expects only \n by default. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load Data Problem
Hi kim, Try using load data local infile '~/Documents/web export.txt' into table stories fields terminated by ',' enclosed by '' escaped by '\\' lines terminated by '\r' (StoryName,Writer,Heading,Caption,Body,ListDate,Section,Publication); Also, if this is a windows text file you should be using \r\n not \r, if this is a unix text file you should simply use \n. Good luck. Kim Kohen wrote: Hello All, I'm having a problem when using Load Data where the single and double quotes in the text file are getting nuked. It appears that the escape character is not being respected but I'm not sure why. It is also knocking out characters near the quotes. The command I'm running is: load data local infile ~/Documents/web export.txt into table stories fields terminated by , enclosed by \ lines terminated by \r (StoryName,Writer,Heading,Caption,Body,ListDate,Section,Publication); A sample of the original text file is: kimtest,Kim,,,this is kim¹s test stories¹ to see how we c¹n accommodate single quotes¹There are a ³couple² of ³double² quotes here to. And ³here are some single¹ quotes that¹re enclosed in doubles²,2002-4-4,, The result from a command line query is: | 253 | this is kim? test stories? to see how we c? accommodate single ?uotes? There are a ?ouple?of ?ouble?quotes here to. And ?ere are some ?ingle?quotes that?e enclosed in doubles? The original file (an csv export from Filemaker Pro) has curly quotes but I have also tried after converting the file to plain text with the same results. Any assistance would be greatly appreciated. Cheers and thanks kim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Colin Faber (303) 859-1491 fpsn.net, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load Data Problem
G'day Colin load data local infile '~/Documents/web export.txt' into table stories fields terminated by ',' enclosed by '' escaped by '\\' lines terminated by '\r' (StoryName,Writer,Heading,Caption,Body,ListDate,Section,Publication); Thanks for your suggestion but sadly it didn't fix the problem - I get exactly the same result. Also, if this is a windows text file you should be using \r\n not \r, if this is a unix text file you should simply use \n. It's actually coming from a Mac (OS9) system and it exports using just /r as the record delimiter. Cheers and thanks again Kim sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load Data Problem
G'day All
kimtest,Kim,,,this is kim¹s test stories¹ to see how we c¹n
accommodate single quotes¹ There are a ³couple² of ³double² quotes here
to. And ³here are some single¹ quotes that¹re enclosed in
doubles²,2002-4-4,,
I seem to have found the problem to my own question and it didn't involve
the SQL statement I was using. It seems that mysql was not recognising the
RTF curly quotes (or their text only derivative - ( ¹ ³ ²) as quotes (' or
).
I opened the text document in a text editor and 'stupified' the quotes and
the file now imports perfectly. Would this be considered a bug or something
worth mentioning to the developers?
cheers
kim
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load Data Problem
Hi, I'm having a problem when using Load Data where the single and double quotes in the text file are getting nuked. It appears that the escape character is not being respected but I'm not sure why. It is also knocking out characters near the quotes. Your single and double quotes come out strangely in my (linux based) e-mail client as well, I suspect you're mixing the windows quote chars without realising it. Check out:- http://www.bbsinc.com/iso8859.html ..scroll down to the bit about 'Microsoft latin-1 added characters' Jon sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
