Fedora Linux Support Community & Resources Center
  #1  
Old 20th August 2007, 09:37 PM
fvs Offline
Registered User
 
Join Date: Apr 2006
Location: Scranton, Pa
Posts: 143
Importing data in mysql?

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
Reply With Quote
  #2  
Old 20th August 2007, 09:46 PM
bbfuller Offline
Registered User
 
Join Date: Jun 2005
Location: UK
Posts: 4,359
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);
Reply With Quote
  #3  
Old 20th August 2007, 10:01 PM
dshaw256 Offline
Registered User
 
Join Date: Mar 2006
Location: Virginia
Age: 60
Posts: 246
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.
__________________
Dave Shaw
Registered Linux user #412525
Dell Latitude D610 / FC13
dshaw256@centurylink.net

no problem is so bad that a moron with root can't make it much worse
Reply With Quote
  #4  
Old 20th August 2007, 10:13 PM
dshaw256 Offline
Registered User
 
Join Date: Mar 2006
Location: Virginia
Age: 60
Posts: 246
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.
__________________
Dave Shaw
Registered Linux user #412525
Dell Latitude D610 / FC13
dshaw256@centurylink.net

no problem is so bad that a moron with root can't make it much worse
Reply With Quote
  #5  
Old 20th August 2007, 11:41 PM
fvs Offline
Registered User
 
Join Date: Apr 2006
Location: Scranton, Pa
Posts: 143
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>
Reply With Quote
  #6  
Old 21st August 2007, 12:07 AM
bbfuller Offline
Registered User
 
Join Date: Jun 2005
Location: UK
Posts: 4,359
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

Last edited by bbfuller; 21st August 2007 at 12:10 AM.
Reply With Quote
  #7  
Old 21st August 2007, 01:33 AM
pete_1967 Offline
Clueless in a Cuckooland
 
Join Date: Mar 2006
Location: Here now, elsewhere tomorrow.
Posts: 4,303
Quick script to convert CSV files to sql:
Code:
#!/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.
Reply With Quote
Reply

Tags
data, importing, mysql

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problems moving MySQL data dir KhaiPi Using Fedora 1 15th February 2009 01:44 AM
Problem importing MySQL db with PHPMyAdmin TheOlster Servers & Networking 6 6th March 2007 06:02 PM
How do I move the MySQL data directory? KhaiPi Using Fedora 0 7th December 2006 02:11 PM
Importing data into Base? StephenT Using Fedora 0 26th May 2006 06:27 AM


Current GMT-time: 14:11 (Tuesday, 02-09-2014)

TopSubscribe to XML RSS for all Threads in all ForumsFedoraForumDotOrg Archive
logo

All trademarks, and forum posts in this site are property of their respective owner(s).
FedoraForum.org is privately owned and is not directly sponsored by the Fedora Project or Red Hat, Inc.

Privacy Policy | Term of Use | Posting Guidelines | Archive | Contact Us | Founding Members

Powered by vBulletin® Copyright ©2000 - 2012, vBulletin Solutions, Inc.

FedoraForum is Powered by RedHat