Re: Problem with mysqlimport and timestamp
qt4x11 wrote: Hi- I'm using the command 'mysqlimport -u usr -ppassh -h mysqlserver -P 3306 -v db --local $workdir/$filename'to import a table into mysql from a file $filename. The data in $filename looks something like: test test where there is a blank space between the two 'test's to represent an empty column. This column is of type datetime NULL DEFAULT NULL in the database. The blank space between the two 'test's gets imported as (err) instead of NULL. Is there a way I can import empty column data as NULL? I'd like there to be a NULL for every row for which this column is empty. I'm not sure how to do this given the format of my $filename. As a check, I tried inserting a row at the top of $filename like test The row was imported correctly, as in there was a NULL in the timestamp column in the database, as well as a NULL in the next column in the database. So, this may have something to do with the format of the $filename, but I may not be able to control the format of that file. Thanks. You should alter your data like so: test\N test That is, separate the fields with a tab and place \N wherever you want a NULL. As it is, the data is being misinterpreted, which is why the second import you mentioned worked--there was nothing after the first field to be erroneously put into the timestamp field. If the data comes from somewhere else you will need to parse it out and re-write it using proper formatting, i'm afraid. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqlimport
--local is a valid option for mysqlimport in 3.23.49 according to the manual. (Source: http://www.cict.fr/app/mysql/manual.html#mysqlimport) What is the entire command you are using? Hi, My command is: mysqlimport -p -L ilk gwarancje.txt And I get error: mysqlimport: Error: The used command is not allowed with this MySQL version, when using table: gwarancje I used this command by Linux shell (logged as root). Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqlimport
is there a higher level directory that does not allow excecution? does it work if you run it as root? can you 'vi /root/mysql/gwarancje.txt'? just some thoughts Jeff Pawe Filutowski [EMAIL PROTECTED]To: [EMAIL PROTECTED] rfam.pl cc: Subject: Problem with mysqlimport 12/09/2003 10:27 AM I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with mysqlimport
If the the file is on the same machine as your shell is running, specify --local when running mysqlimport. Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: Problem with mysqlimport I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqlimport
I tryed this option but i got following error: mysqlimport: Error: The used command is not allowed with this MySQL version, when using table: gwarancje MySQL version is 3.23.49 What Can I do ?? Regards - Original Message - From: Matt Griffin [EMAIL PROTECTED] To: 'Pawe3 Filutowski' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 5:11 PM Subject: RE: Problem with mysqlimport If the the file is on the same machine as your shell is running, specify --local when running mysqlimport. Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: Problem with mysqlimport I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with mysqlimport
Paul, --local is a valid option for mysqlimport in 3.23.49 according to the manual. (Source: http://www.cict.fr/app/mysql/manual.html#mysqlimport) What is the entire command you are using? Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 12:06 PM To: [EMAIL PROTECTED] Subject: Re: Problem with mysqlimport I tryed this option but i got following error: mysqlimport: Error: The used command is not allowed with this MySQL version, when using table: gwarancje MySQL version is 3.23.49 What Can I do ?? Regards - Original Message - From: Matt Griffin [EMAIL PROTECTED] To: 'Pawe3 Filutowski' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 5:11 PM Subject: RE: Problem with mysqlimport If the the file is on the same machine as your shell is running, specify --local when running mysqlimport. Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: Problem with mysqlimport I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqlimport.
Idries Hamadi wrote: Hi all, I've just started using mysql and I'm sure that my all problems are something todo with my oracle-ness, so please bear with me if I use case-insensitive table names or somthing ;) Ok. I've been using the mysql interactive command-line interface for a few days now, and there's no problem there. I've made myself a ~/.my.cnf file and it appears to work: [client] user=idries_wedding password=** Since creating it I no longer need to enter username or password details when I run mysql :) Now, I'm trying to use mysqlimport: How about [mysqlimport] user=idries_esdding password=** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqlimport.
At 14:18 +0100 7/2/03, Idries Hamadi wrote: Hi all, I've just started using mysql and I'm sure that my all problems are something todo with my oracle-ness, so please bear with me if I use case-insensitive table names or somthing ;) Ok. I've been using the mysql interactive command-line interface for a few days now, and there's no problem there. I've made myself a ~/.my.cnf file and it appears to work: [client] user=idries_wedding password=** Since creating it I no longer need to enter username or password details when I run mysql :) Now, I'm trying to use mysqlimport: idries ~/src/wedding/database$ mysqlimport idries_Wedding GUEST.txt mysqlimport: Error: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO), when using table: GUEST The problem here actually has to do with how the file is getting read. If you don't specify the --local option, mysqlimport sends a LOAD DATA INFILE statement to the server, and the server itself tries to read the file, using the filename 'GUEST.txt'. That's a relative filename, and no doubt will not be correct in terms of the server's current working directory. Try this command: mysqlimport --local idries_Wedding GUEST.txt mysqlimport should read your .my.cnf file properly. The problem here seems to be that mysqlimport is not reading the password from the .my.cnf file. I had a similar problem with it not liking the database option to be specified in the .my.cnf file, so I removed that. I have tried several combinations of removing the password from the .my.cnf file and removing the .my.cnf file completly. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqlimport
Do not use mysqlimport. That is for flat file import. mysql test UM.txt Shripal Shah wrote: Dear Sir/Madam, I've one data file which is built with the help of mysqldump. This file contains structure data of 2 tables of test database. Like this.. mysqldump -C --add-drop-table -e test patient hospital UM.txt then I am trying to import data using this statement.. mysqlimport test UM.txt It give me error for table not found... Please guide me regarding this matter.. Thanks Regards, Shripal. - 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 - 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: Problem with mysqlimport
Hi, I didn't notice in the other e-mail, but the syntax of mysqlimport says everything.. :-) mysqlimport is like LOAD DATA INFILE and imports data to ONE table. To use your UM.txt you may use: # mysql test UM.txt For further explanation of mysqlimport: # mysqlimport --help Loads tables from text files in various formats. The base name of the text file must be the name of the table that should be used. OR http://www.mysql.com/doc/en/mysqlimport.html shell mysqlimport [options] database textfile1 [textfile2 ...] For each text file named on the command-line, mysqlimport strips any extension from the filename and uses the result to determine which table to import the file's contents into. For example, files named `patient.txt', `patient.text', and `patient' would all be imported into a table named patient. ... On Fri, 2003-01-17 at 13:45, Shripal Shah wrote: Hi, I am using version 3.23.47. Yes, DROP TABLE COMMAND IS PERFECT. See, here I am mentioning my steps: 1 mysqldump --opt test patient UM.txt 2 mysqlimport test UM.txt THEN IT GIVES ME FOLLOWING ERROR: mysqlimport: Error: Table 'test.UM' doesn't exist, when using table: UM So, this is my problem. Thanks for your immediate reply Thanks Regards, Shripal. - Original Message - From: Diana Soares [EMAIL PROTECTED] To: Shripal Shah [EMAIL PROTECTED] Sent: Friday, January 17, 2003 6:48 PM Subject: Re: Problem with mysqlimport Hi, What version of MySQL do you use ? Check if the DROP TABLE command on file UM.txt looks like this: DROP TABLE IF EXISTS table; Also, is it a NOTE or mysqlimport really exits ? Does it create any table ? http://www.mysql.com/doc/en/DROP_TABLE.html On Fri, 2003-01-17 at 12:33, Shripal Shah wrote: Dear Sir/Madam, I've one data file which is built with the help of mysqldump. This file contains structure data of 2 tables of test database. Like this.. mysqldump -C --add-drop-table -e test patient hospital UM.txt then I am trying to import data using this statement.. mysqlimport test UM.txt It give me error for table not found... Please guide me regarding this matter.. Thanks Regards, Shripal. -- Diana Soares - 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: problem with mysqlimport
Julia, Thursday, October 17, 2002, 2:29:57 PM, you wrote: JM This is what I've done: JM created a database testdb in access with a table cop JM exported the table as comma separated values into cop.txt JM created a database called testdb in mysql, JM created a table called cop of the same structure as the access table JM that works fine. JM when I try this: JM mysqlimport testdb cop.txt; JM I get the error: JM The used command is not allowed with this MySQL version when using JM table: cop JM any hints? What version of MySQL are you using? Usually this error ocurs when LOAD DATA LOCAL INFILE or mysqlimport --local are used: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html -- For technical support contracts, goto https://order.mysql.com/?ref=ma02-010c This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: Problem with MySQLImport utility
* [EMAIL PROTECTED] Hie I'm new in MySQL.anyone out there can help please? I'm actually experimenting with the mysqlimport utility to import data from a text file.however i'm always getting this error upon the command c:\mysql\binmysqlimport - - local - -fields-terminated-by=, test newtest.txt ERROR: The used command is not allowed with this MySQL version, when using table: newtest P.S. My SQL version is Ver. 11.16 Distrib 3.23.49 for Win95/98 (i32) Also i created a txt file with the following structure 100,200 200,200 300,300 . My table is newtest with 2 fields of char datatype of size 10. From the manual: In MySQL 3.23.49 and MySQL 4.0.2 LOCAL will only work if you have not started mysqld with --local-infile=0 or if you have not enabled your client to support LOCAL. See section 4.2.4 Security issues with LOAD DATA LOCAL. (There is probably a typo here... I guess LOCAL will only work if you HAVE enabled your client to support LOCAL... AND you have not started the server with --local-infile=0...) URL: http://www.mysql.com/doc/L/O/LOAD_DATA.html URL: http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html Beware that there was done some changes to LOAD DATA LOCAL in version 3.23.49, and some bugs was intrudused (--enable-local-infile (and --local-infile) did not work, IIRC). These bugs was fixed in version 3.23.50. URL: http://www.mysql.com/doc/N/e/News-3.23.49.html URL: http://www.mysql.com/doc/N/e/News-3.23.50.html -- 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: Problem with MySQLImport utility
ericloe, Tuesday, June 25, 2002, 12:17:03 PM, you wrote: e I'm new in MySQL.anyone out there can help please? e I'm actually experimenting with the mysqlimport utility to import data from e a text file.however i'm always getting this error upon the command c:\mysql\binmysqlimport - - local - -fields-terminated-by=, test e newtest.txt e ERROR: The used command is not allowed with this MySQL version, when using e table: newtest e P.S. My SQL version is Ver. 11.16 Distrib 3.23.49 for Win95/98 (i32) e Also i created a txt file with the following structure e 100,200 e 200,200 e 300,300 e . e My table is newtest with 2 fields of char datatype of size 10. e Please help. Thanks. You should start mysqld with --local-infile=1 to enable load data from client: http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: Problem with MySQLImport utility
you don't need space between dashes --local instead of - -local Borus Hie I'm new in MySQL.anyone out there can help please? I'm actually experimenting with the mysqlimport utility to import data from a text file.however i'm always getting this error upon the command c:\mysql\binmysqlimport - - local - -fields-terminated-by=, test newtest.txt ERROR: The used command is not allowed with this MySQL version, when using table: newtest P.S. My SQL version is Ver. 11.16 Distrib 3.23.49 for Win95/98 (i32) Also i created a txt file with the following structure 100,200 200,200 300,300 . My table is newtest with 2 fields of char datatype of size 10. Please help. 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 - 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: Problem with MySQLImport utility
* [EMAIL PROTECTED] I'm new in MySQL.anyone out there can help please? I'm actually experimenting with the mysqlimport utility to import data from a text file.however i'm always getting this error upon the command c:\mysql\binmysqlimport - - local - -fields-terminated-by=, test newtest.txt You should not have spaces between the '-' characters...: mysqlimport --local --fields-terminated-by=, test newtest.txt ...but I'm not sure if this is the reason of the error message you get... I get Error: Unknown database '-' when I try your command with the spaces between the '-' characters. (win2k, 3.23.30-gamma) -- 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
