How to load the csv file in the local machine - Page 2

10-27-2006, 05:16 PM	#11
manu007 Junior Member Join Date: Oct 2006 Posts: 10	Re: How to load the csv file in the local machine i know, it is getting worse. But i dont see that my.ini file anywhere. Table name is fine. Training is the schema. So the syntax is fine.
	Bookmark this post with Del.icio.us

10-28-2006, 12:11 PM	#12
Administrator Administrator Join Date: Jun 2004 Posts: 383	Re: How to load the csv file in the local machine It's hard to say what the issue other than saying it sounds that you have a permission problem rather than a mysql problem. Windows is not allowing mysql to write to file...I don't really use windows for anything heavy so I'm not sure how to fix it.
	Bookmark this post with Del.icio.us

10-28-2006, 11:09 PM

#13

Administrator

Join Date: Jun 2004

Posts: 383 Administrator is on a distinguished road

Re: How to load the csv file in the local machine

Maybe this page can help
http://dev.mysql.com/doc/refman/5.0/en/select.html

Quote:

#

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. As of MySQL 5.0.19, the character_set_filesystem system variable controls the interpretation of the filename.

The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host.

SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax for the export_options part of the statement consists of the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE statement. See Section 13.2.5, “LOAD DATA INFILE Syntax”.

FIELDS ESCAPED BY controls how to write special characters. If the FIELDS ESCAPED BY character is not empty, it is used as a prefix that precedes following characters on output:

*

The FIELDS ESCAPED BY character
*

The FIELDS [OPTIONALLY] ENCLOSED BY character
*

The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values
*

ASCII NUL (the zero-valued byte; what is actually written following the escape character is ASCII ‘0’, not a zero-valued byte)

The FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY characters must be escaped so that you can read the file back in reliably. ASCII NUL is escaped to make it easier to view with some pagers.

The resulting file does not have to conform to SQL syntax, so nothing else need be escaped.

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

Here is an example that produces a file in the comma-separated values (CSV) format used by many programs:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

#

If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only one row into the file, without any column or line termination and without performing any escape processing. This is useful if you want to store a BLOB value in a file.
#

The INTO clause can name a list of one or more user-defined variables. The selected values are assigned to the variables. The number of variables must match the number of columns.

Within a stored routine, the variables can be routine parameters or local variables. See Section 17.2.7.3, “SELECT ... INTO Statement”.
#

Note: Any file created by INTO OUTFILE or INTO DUMPFILE is writable by all users on the server host. The reason for this is that the MySQL server cannot create a file that is owned by anyone other than the user under whose account it is running. (You should never run mysqld as root for this and other reasons.) The file thus must be world-writable so that you can manipulate its contents.
#

The SELECT syntax description at the beginning this section shows the INTO clause near the end of the statement. It is also possible to use INTO OUTFILE or INTO DUMPFILE immediately preceding the FROM clause.

Bookmark this post with Del.icio.us

10-28-2006, 11:14 PM

#14

Administrator

Join Date: Jun 2004

Posts: 383 Administrator is on a distinguished road

Re: How to load the csv file in the local machine

Quote:

Originally Posted by manu007

i know, it is getting worse. But i dont see that my.ini file anywhere.

Quote:

Originally Posted by manu007

Table name is fine. Training is the schema. So the syntax is fine.

Maybe I'm remembering the name wrong. I think it's my.cnf

Bookmark this post with Del.icio.us

10-30-2006, 09:11 AM	#15
manu007 Junior Member Join Date: Oct 2006 Posts: 10	Re: How to load the csv file in the local machine thanks for all the help u are providing me. In your last post it talks about this: "If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host." where can i use the command "-e "select..." Can i use somewhere in the mysql query browser. I did search for my.cnf file. I couldnt find it. thanks
	Bookmark this post with Del.icio.us

10-30-2006, 12:38 PM

#16

Administrator

Join Date: Jun 2004

Posts: 383 Administrator is on a distinguished road

Re: How to load the csv file in the local machine

Quote:

Originally Posted by manu007

thanks for all the help u are providing me.
In your last post it talks about this:
"If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host."

where can i use the command "-e "select..." Can i use somewhere in the mysql query browser.
I did search for my.cnf file. I couldnt find it.
thanks

I've never used the mysql query browser, so I don't really know. When you have mysql full blown installed it works. Scan your mysql directory for a file named mysql (or I guess for windows mysql.exe). You can run the command from the command line as long as mysql.exe is in the path.

Bookmark this post with Del.icio.us

10-30-2006, 03:12 PM	#17
manu007 Junior Member Join Date: Oct 2006 Posts: 10	Re: How to load the csv file in the local machine it only has mysql query browser.exe not mysql.exe. It is used to open mysql query browser. Thanks for all your help. I think i keep on researching that.
	Bookmark this post with Del.icio.us

FAQ	Members List	Calendar	Search	Today's Posts	Mark Forums Read

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Importing CSV file for numeric columns	wrandall	General	2	10-19-2005 11:42 AM
Does adding a deny to a cgi file work for parameters added to the file?	Azkaban	Robots & Spiders	0	09-06-2005 09:52 PM
Import Access, Excel and data of other formats to MySQL by Navicat	NavicatGuy	Articles	0	08-17-2004 01:08 AM
3.3 Creating and Using a Database	Administrator	Documentation	13	06-09-2004 04:56 PM
2 Installing MySQL	Administrator	Documentation	103	06-09-2004 02:53 AM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)


	Web http://www.mysqltalk.com