SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Keep Moving Forward gold trophysilver trophybronze trophy
    Shaun(OfTheDead)'s Avatar
    Join Date
    Nov 2005
    Location
    Trinidad
    Posts
    3,746
    Mentioned
    45 Post(s)
    Tagged
    0 Thread(s)

    LOAD DATA INFILE - Importing Data from an Excel Spreadsheet

    Hi.

    I have an Excel Spreadsheet with 25,000+ records to be imported into a database table.

    I already re-sorted the columns to match my database fields, however when I tried exporting as a CSV to import via PHPMyAdmin, I got a puzzling error, "Invalid field count in CSV input on line 1."

    Puzzling because I had the correct number of columns! So I'm chalking that up to some hidden Excel formatting.



    Anyways...

    So some googling later I found myself here, and learnt about "LOAD DATA INFILE".

    How exactly do I use that function?

    I saved and uploaded the data as a tab-delimited txt file, then ran a file called "load-data.php" with the following;


    Code PHP:
    <?php
    // Connect to Database
    	@ include($_SERVER['DOCUMENT_ROOT'] .'/Hidden/DatabaseInfo.php');
    	@ mysql_connect($db_Host,$db_Username,$db_Password);
    	@ mysql_select_db($db_Database) or die("Error.");
     
    // Load Data from Text File
    	LOAD DATA INFILE '/home/username/public_html/Database.txt';
     
    // Dis-connect from Database
    	@ mysql_close();
    ?>

    This gave me a syntax error though;

    Parse error: syntax error, unexpected T_STRING in /home/username/public_html/load-data.php on line 8
    What am I doing wrong?

    Trying to fill the unforgiving minute
    with sixty seconds' worth of distance run.

    Update on Sitepoint's Migration to Discourse

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The load file command is a MySQL command - so it needs to be in a MySQL Query:
    PHP Code:
    <?php
    // Connect to Database
    include($_SERVER['DOCUMENT_ROOT'] .'/DatabaseInfo.php');
    mysql_connect($db_Host,$db_Username,$db_Password) or die("MySQL - Connection Error");
    mysql_select_db($db_Database) or die("MySQL - Cannot Select Database");
     
    mysql_query("LOAD DATA INFILE '/home/username/public_html/Database.txt' INTO TABLE yourtablename") or die("MySQL - Query Error - " MySQL_Error());

    //MySQL is automatically disconnected from when PHP ends.
    ?>
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    Keep Moving Forward gold trophysilver trophybronze trophy
    Shaun(OfTheDead)'s Avatar
    Join Date
    Nov 2005
    Location
    Trinidad
    Posts
    3,746
    Mentioned
    45 Post(s)
    Tagged
    0 Thread(s)
    MySQL - Query Error - Access denied for user 'username'@'localhost' (using password: YES)
    I tried emptying the table.

    I'm hoping the cause isn't this;


    If you are able to connect to the MySQL server, but get an Access denied message whenever you issue a SELECT ... INTO OUTFILE or LOAD DATA INFILE statement, your entry in the user table does not have the FILE privilege enabled.
    http://dev.mysql.com/doc/refman/5.1/...ss-denied.html



    I didn't see anything in cpanel to grant "FILE" permissions for a database user.

    Is there another way to set that, or would I have to e-mail the web-host?

    Trying to fill the unforgiving minute
    with sixty seconds' worth of distance run.

    Update on Sitepoint's Migration to Discourse

  4. #4
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    MySQL databases have a table for users, which contains privileges etc. It appears your user doesn't have that FILE privilege - if you don't have access to that table, emailing the host may be your only option.

    Before you try that, try using the LOCAL command:
    PHP Code:
    mysql_query("LOAD DATA LOCAL INFILE '/home/username/public_html/Database.txt' INTO TABLE yourtablename") or die("MySQL - Query Error - " MySQL_Error()); 
    The local command means that the file is retrieved by the client - e.g. PHP - rather than the database server. I'm not sure whether this will have an effect, but it's a possibility.

    If all else fails, you could perhaps load the data into a local mysql database, then export the table (with IF NOT EXISTS on the creation), compressed, through PHPMyAdmin. You can then import said table with PHPMyAdmin on your server.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  5. #5
    Keep Moving Forward gold trophysilver trophybronze trophy
    Shaun(OfTheDead)'s Avatar
    Join Date
    Nov 2005
    Location
    Trinidad
    Posts
    3,746
    Mentioned
    45 Post(s)
    Tagged
    0 Thread(s)
    Awe man, Jake you rule!

    Quote Originally Posted by Jake Arkinstall
    Before you try that, try using the LOCAL command:
    PHP Code:
    mysql_query("LOAD DATA LOCAL INFILE '/home/username/public_html/Database.txt' INTO TABLE yourtablename") or die("MySQL - Query Error - " MySQL_Error()); 
    That did it.

    Thanks a lot, dude

    Trying to fill the unforgiving minute
    with sixty seconds' worth of distance run.

    Update on Sitepoint's Migration to Discourse

  6. #6
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Any time
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •