LOAD DATA problem
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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.470 / Virus Database: 268 - Release Date: 08-04-2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
Load Data Problem
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? By the way, i'm using mysql ver 11.16 Distrib 3.23.49 for Win95/98 (i32) Any help would be much appreciated. Thanks Regards, Eric. ** NOTICE OF CONFIDENTIALITY ** This message and any files transmitted with it may be privileged and/or confidential and are intended only for the use of the addressee. If you, the reader of this message, are not the intended recipient, you should not disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately by return email and delete the original message. Thank you. - 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
Load Data Problem
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
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
Strange LOAD DATA problem
I have a file (data.txt) which contains the data: ELITEô85.ôQô4 DaysôLilleshallôChris IMPô75.ôQôTuesFriôLilleshallôSue LEFTô0.ôôôLeftô PRESCô24.ôTôOne DayôWrekinôArleen RECô27.ôQôTuesôWrekinôTony I use the LOAD DATA DDL statement: LOAD DATA LOCAL INFILE data.txt INTO TABLE squad FIELDS TERMINATED BY ô LINES TERMINATED BY \n; But when I select * from squad MySQL returns: mysql select * from squad; +--+---+--+--++-+ | Squad_id | Fee | Per | Days | Location | Coach | +--+---+--+--++-+ |LITE| 85.00 | Q| 4 Days | Lilleshall | Chris | | 75.00 | Q| TuesFri | Lilleshall | Sue | | 0.00 | | | Left | |PRESC| 24.00 | T| One Day | Wrekin | Arleen | REC | 27.00 | Q| Tues | Wrekin | Tony| +--+---+--+--++-+ 5 rows in set (0.00 sec) Notice how the table (more specifically the data) is malformmed even thought the data in the file is visibly correct. Is there anything I can do to make sure that the data in the file is imported successfully. Also I use the field delimiter ô (ALT+147) because the data MAY contain your more standard delimiter , (comma). Any ideas NOTE: The table was created using CREATE TABLE squad ( Squad_id VARCHAR(255), Fee DECIMAL(20,2), Per VARCHAR(255), Days VARCHAR(255), Location VARCHAR(255), Coach VARCHAR(255) ); Both client and server ar on the same machine (running on WinXP) Thanks lots David Ayliffe - 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: Strange LOAD DATA problem
Yeah useful but that still doesn't solve my problem -Original Message- From: Maciek Dobrzanski [mailto:[EMAIL PROTECTED]] Sent: 06 January 2002 17:43 To: David Ayliffe; 'MySQL' Subject: Re: Strange LOAD DATA problem Also I use the field delimiter ô (ALT+147) because the data MAY contain your more standard delimiter , (comma). You may use any string as the field delimiter (i.e. '~EOF~', '#END#'). - 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: Strange LOAD DATA problem
At 17:36 + 1/6/02, David Ayliffe wrote: I have a file (data.txt) which contains the data: ELITEô85.ôQô4 DaysôLilleshallôChris IMPô75.ôQôTuesFriôLilleshallôSue LEFTô0.ôôôLeftô PRESCô24.ôTôOne DayôWrekinôArleen RECô27.ôQôTuesôWrekinôTony I use the LOAD DATA DDL statement: LOAD DATA LOCAL INFILE data.txt INTO TABLE squad FIELDS TERMINATED BY ô LINES TERMINATED BY \n; But when I select * from squad MySQL returns: mysql select * from squad; +--+---+--+--++-+ | Squad_id | Fee | Per | Days | Location | Coach | +--+---+--+--++-+ |LITE| 85.00 | Q| 4 Days | Lilleshall | Chris | | 75.00 | Q| TuesFri | Lilleshall | Sue | | 0.00 | | | Left | |PRESC| 24.00 | T| One Day | Wrekin | Arleen | REC | 27.00 | Q| Tues | Wrekin | Tony| +--+---+--+--++-+ 5 rows in set (0.00 sec) Notice how the table (more specifically the data) is malformmed even thought the data in the file is visibly correct. Is there anything I can do to make sure that the data in the file is imported successfully. Also I use the field delimiter ô (ALT+147) because the data MAY contain your more standard delimiter , (comma). When I see this, it's typically because I've gotten carriage returns (\r) into the file. I'd take a look at the file with a utility that shows every character (like a hex dumper or something similar) to see what's really in your file. - 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
