shaun — 2010-04-09T20:45:59-04:00 — #1
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?
jake_arkinstall — 2010-04-09T20:52:11-04:00 — #2
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.
shaun — 2010-04-09T21:15:25-04:00 — #3
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?
jake_arkinstall — 2010-04-09T21:22:34-04:00 — #4
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.
shaun — 2010-04-09T21:30:20-04:00 — #5
Awe man, Jake you rule!
That did it.
Thanks a lot, dude :tup:
jake_arkinstall — 2010-04-09T21:34:42-04:00 — #6