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.
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;
// Connect to Database
@ include($_SERVER['DOCUMENT_ROOT'] .'/Hidden/DatabaseInfo.php');
@ 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
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?
The load file command is a MySQL command - so it needs to be in a MySQL Query:
// Connect to Database
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.
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.
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?
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:
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.
Awe man, Jake you rule!
That did it.
Thanks a lot, dude :tup: