Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Perl Monk,
I want to load the csv file from windows to mysql (which is avilable in linux server). it is not updating the csv data in to mysql server.
here is my code 
use strict;
use Net::Mysql;
my $mysql = Net::MySQL->new(
        hostname => 'linuxserver',
        database => 'ADMIN',
        user => 'user',
        password => 'password'
);
$mysql->query(qq{LOAD DATA LOCAL INFILE 'C:\\1.csv' INTO TABLE `ADMIN`
+.`temp` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' });
if ($mysql->is_error) {
    my $DBError = $mysql->get_error_message;
    print "MySQL Error : $DBError    \n";
}
$mysql->close;

[download]

Replies are listed 'Best First'.
Re: Load csv file to mysql server
by FalseVinylShrub (Chaplain) on Jul 26, 2009 at 15:21 UTC

    Hi Anonymous Monk...

    I'm not set up to test this, but I think your problem here is that you're trying to load a LOCAL file (i.e. on the server) but your file is on your windows box. It would help if you provided the error message, if any.

    Solutions could be:

    • copy the file to the server then use the local loading command,
    • use Perl to split the file and load the data with INSERTs (could be slow, and error prone...)
    • or see if you can load from a stream - e.g. STDIN and print the file down the stream to the server. This is what I would do with Postgres, but I don't know MySQL so well.

    Hope this helps. You will probably get a better reply, but since no-one has replied and I think I can see the problem at least, I thought I would make my first post on here.

    F. V. Shrub

[reply]
      how i can load my file from windows with out copying the file to my sql server.
[reply]

        Hi

        Sorry, I don't know the answer beyond the general approaches that I mentioned in my post. Someone who knows mysql better than me will have to answer.

        If you don't get answers on here, try searching some mysql sites for how to load a remote file, or how to load from STDIN.

        hth. F.V.S.

[reply]
Re: Load csv file to mysql server
by jethro (Monsignor) on Jul 26, 2009 at 15:35 UTC
    The syntax 'admin'.'temp' instead of just 'admin.temp' looks fishy to me, but that might be just my ignorance.

    Are you really not getting any error message? You might rename 1.csv for a test to check if the file is accessed at all.

    Also you might add a sleep(100); before the query and use a second mysql session with 'show processlist' to see if your script is logged in at all. After running the script you should also see in the mysql logs whether your script was logged in and what command your script was trying to execute

[reply]
Re: Load csv file to mysql server
by bichonfrise74 (Vicar) on Jul 26, 2009 at 15:36 UTC
    What is the exact error that you are getting? Also, have you tried to run the 'load data' command from the command-line of the Windows box and did it work?
[reply]

      The error message would really be useful. The LOAD DATA command has several security restrictions, so it may be simply a permissions problem.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
        hi, if i use heidiSQL client it is upload from local system to mysql server using same user id and password .then how it is permission problem
[reply]

Back to Seekers of Perl Wisdom