visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: phpMyAdmin "insert textfiles into table" ?
By: dresdena1 U.S.A.  Date: 29/09/2003 00:00:00  English  Points: 300 Status: Answered
Quality : Excellent
EEE,
I am trying to insert a tab delimited textfile (converted from Access) into an existing empty table.
The table and textfile have the same name.
The field properties in the table match up to the text file.

I have tried to use "insert textfiles into table" by locating the file on my hard drive and I get the error message:
"MySQL said: Query was empty"

I have also tried by locating the file on the server (<A HREF="http://www.???.com/filename.txt">http://www.???.com/filename.txt</a>) and it doesn't do anything. No error message, nothing.

Any idea how I can get this to insert into the table?

Thanks very much.
dresdena1
By: mquiroz Date: 29/09/2003 01:02:00 English  Type : Comment
try something like:

LOAD DATA INFILE "TEXTFILE.TXT" INTO TABLE YOURTABLE;
By: wskoczen Date: 29/09/2003 08:45:00 English  Type : Comment
mysql> LOAD DATA INFILE 'textfile.txt' INTO TABLE empty_table
-> FIELDS TERMINATED BY '\t';
By: wskoczen Date: 29/09/2003 08:48:00 English  Type : Comment
dresdena1
For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege on the server host.

Good Luck
Wesley
By: VGR Date: 30/09/2003 21:44:00 English  Type : Comment
agree withthe above, but first try the absolutely stupid :

LOAD DATA INFILE "full path to file" INTO TABLE yourtable;

if you get an error, please tell us which one ;-)
By: dresdena1 Date: 30/09/2003 21:52:00 English  Type : Comment
VGR,
I tried that and got the error message:
MySQL said: Access denied for user: 'abd@localhost' (Using password: YES)

I don't understand the access denied message. I have access to the database with the login and password.

Thanks.
dresdena1
By: VGR Date: 30/09/2003 22:31:00 English  Type : Comment
it means you don't use a user that has enough privileges.

try as "root"
By: dresdena1 Date: 30/09/2003 22:39:00 English  Type : Comment
VGR,
I don't know what you mean by "root"?

Sorry for my ignorance.
I do web development and design. phpMyAdmin and MySQL are used in several of the sites.
I know how to do a basic amount of work with them, but not much more.

Where do I input "root" information.

Thanks.
dresdena1
By: VGR Date: 30/09/2003 22:51:00 English  Type : Answer
in fact, a MySql server ALWAYS has a "root" user. Think about him like the "administrator" or "superuser" (su) of some OSes

It has usually ALL privileges on all the DB elements (databases, tables, columns, users, etc)

It's the one user the DBA uses for administering the DB

If you receive the error above, it's because your user "abd" doesn't have the FILE privilege. I suggest you ask the DBA to either give you that privilege, or to perform the operation himself, as 'root'

Of course, if it's your own server and you forgot the 'root' password :
-first make sure you defined one ;-)
try : mysql --user=root
if it connects, you forgot to set a root password. Do it (see manual : "update mysql.user set Password=PASSWORD('yourpassword') where User='root';" )

-if it fails, and you completely lost that password, restart the server with option --skip-grant-tables (mysqld --skip-grant-tables &) and chnge it with the above command once logged in as "root"

-then : flush privileges;

-and last : quit;

log in again and grant "abd" the FILE privilege : GRANT FILE ON *.* TO 'abd';
By: dresdena1 Date: 07/10/2003 22:29:00 English  Type : Comment
VGR,
Thanks.
I got it working.

dresdena1

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 350.763800 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page