Importing data in mysql?

[fvs]

I've been trying to import data from a spreadsheet into mysql database,I have it in a cvs form with the right amount of fields as my database, but each time I import into mysql I get an error message,

mysql> load data infile '/home/frank/OpenOffice_Backup/Cus.cvs' into table Custome rs;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/home /frank/OpenOffice_Backup/Cus.cvs into table Customers' at line 1
mysql> load data infile '/home/frank/OpenOffice_Backup/Cus.cvs' into table Custo mers;
ERROR 13 (HY000): Can't get stat of '/home/frank/OpenOffice_Backup/Cus.cvs' (Err code: 2)
Any help appreciated. Thanks

[bbfuller]

My MySQL manual tells me that the form of the load data statement is

load data infile '/home/frank/etc' into table Customers fields terminated by ',' (list of fields in the table separated by commas);

[dshaw256]

Ran a test on my system. I was able to make it work with:

load data infile 'full/path/name' into table table_name fields separated by ',';

But I had to be the root user (e.g., "mysql -u root") in order to use the load data command at all.

So. I'd recommend you try again as a high-privilage mysql user, or make sure that the account you are using had load data privileges. And check the file name; that inability to stat the file bothers me.

[dshaw256]

Correction.

Solved both the security problem and the full path requirement by using "load data local infile './filename.cvs' into table table-name fields terminated by ',';". Key is to include the keyword "local" so mysql knows it's loading a client file, not a server-based file.

Hope this makes sense. I'll stop now.

[fvs]

Not sure, I did try this out no happinesss,
mysql> load data infile '/home/frank/Cus.cvs' into table Customers fields termin ated by '.' (Cus_id,Date,First_name,Last_name,Address,City,Sta te,Zip,Home_phone, Cell_phone,Email,Notes);
ERROR 13 (HY000): Can't get stat of '/home/frank/Cus.cvs' (Errcode: 2)
mysql>

[bbfuller]

Well, we've got the MySql syntax sorted out at least.

I see you've changed the location of the file to input.

I've got a note that I got the same sort of error message when sending data from mysql to an outfile.

Annoyingly the solution that I've noted is "due to permissions setup on the folders" and nothing more.

I get the feeling that the users you set up in mysql, even if you use the same identities in mysql and linux, are not equivalent, and when you try to access data the database does it in the name of the 'mysql' user, and in Fedora, it would only be the Fedora frank user who had access to your home folder unless you altered it.

I also notice that you haven't tried load data local infile as suggested by dshaw256

[pete_1967]

Quick script to convert CSV files to sql:

#!/usr/bin/env perl
# Read in comma-separated list of data
# and export to a sql script
#
use strict;
use warnings;

my $datasheet = "[path_to_csv_file]
my $output = "[flename_for_sql]
my %data = ();
my $column1
my $column2
my $column3

unless (open DATAINP, "<$datasheet") {
    die "Could not read file: $!\n";
}

unless (open DATAOUT, ">$output") {
    die "Could not write to: $!\n";
}

while (<DATAINP>) {
    ($column1, $column2, $column3(/\,/, $_);
    $data{"column1"}= $column1;
    $data{"column2"} = $column2;
    $data{"column3"} = $column3;

    print DATAOUT "
    INSERT into [table_name] values(",
        $data{"column1"}, ", ",
        $data{"column2"}, ", ",
        $data{"column3"},
        ");";

}

close DATAOUT;
close DATAINP;

Modify to your liking.