[RESOLVED] PHP parser for CSV to mySql

[electricdonkey]

EDITED: For change of code and issue...

I've been testing the following code and working to modify it for a small project I have...

Works great except for one thing...

If the CSV file has less delimited data than there are columns in the table I get the SQL error: Column count doesn't match value count at row "x"

Is there any way to input a 0 for the missing data sets?

I've done the same import using phpMyAdmin and had the correct results stored...

Any advice is welcomed...

// Database server host
$sql_db['host'] = "localhost";

// Database name
$sql_db['name'] = "test";

// Database user name
$sql_db['user'] = "root";

// Database password
$sql_db['pass'] = "";

// Database table name
$sql_db['tble'] = "mydata";


// Absolute path or URL (e.g. http://...) to the CSV file
$csv_file['path'] = "/path/to/csvfile/csvdata.csv";

// Value Separator used in CSV file
$csv_file['sepa'] = ";";


// ======================================================================
//  Connect to MySQL database
// ======================================================================

$connect = @mysql_connect($sql_db['host'], $sql_db['user'], $sql_db['pass'])
	or die(mysql_error());

$db = @mysql_select_db($sql_db['name'], $connect)
	or die(mysql_error());


// ======================================================================
//  Create the SQL Insert Query
// ======================================================================

$file_contents_line = @file($csv_file['path'])
	or die ("CSV file not found.");

foreach ($file_contents_line as $key => $val)
{
		// Skip empty lines
	if (empty($val)) continue;

$inserts .= (($key >= 1) ? ", " : "")."('";

$values = explode($csv_file['sepa'], $val);

for ($j = 0; $j < count($values); $j++)
{
	$inserts .= addslashes(utf8_decode($values[$j])).(($j != (count($values) - 1)) ? "', '" : "");
}

$inserts .= "')";
}


// ======================================================================
//  Store all data into our database
// ======================================================================

$sql = "INSERT INTO ".$sql_db['tble']."
		VALUES ".$inserts;

$res = mysql_query($sql)
	or die(mysql_error());


echo "Convert successful.\n";

[electricdonkey]

that is to say phpMyAdmin will import it correctly and insert 0 for all missing row values...

using the

Insert data from a text file into the table option

[cahva]

EDIT:
Sorry I didnt understand or misread your post.. Well anyway I left this snippet if you want to use it anyway 🙂

--

Heres a little code that I sometimes use to import csv file to database. Its easy to modify it if you want to put zero to empty fields or something.:

$skipfirstline = true;
$delimiter = ';';

$handle = fopen("csvfile.csv", "r");

while (($data = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
	if ($skipfirstline == true) {
		$skipfirstline = false;
		continue;
	}

// prepare for inserting to database
foreach (array_keys($data) as $v)
	$data[$v] = mysql_real_escape_string($data[$v]);

$values = implode("','",$data);
$query = "INSERT INTO tablename VALUES ('".$values."')";
mysql_query($query);
}

[electricdonkey]

cahva,

Thanks for the reply and the sniplet... it runs fast... but it's left me at the same place I began...

Rows in the CSV file that do not match the count of columns in the SQL table are ignored all together...

What I'm after is a way to check on the fly the count of columns in the SQL table and check that versus the row I am parsing into the table from the CSV... If the CSV is short on a row, just insert 0's for the remaining columns in that row...

That makes sence right?

[electricdonkey]

$sql = 'LOAD DATA LOCAL INFILE \'./Azgalor-Alliance.csv\' INTO TABLE `Azgalor_AllianceTmp` FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' LINES TERMINATED BY \'\\r\\n\'';

mysql_query($sql)or die(mysql_error());;

Well... I should have tried this to begin with...

the above works perfect...