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;[/FONT]
<?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
The load file command is a MySQL command - so it needs to be in a MySQL Query:
<?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.
?>
MySQL - Query Error - Access denied for user ‘username’@‘localhost’ (using password: YES)
[FONT=“Georgia”]I tried emptying the table.
I’m hoping the cause isn’t this;[/FONT]
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.
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.
