csv import woes...

[remmingtonshowdown]

I did search and I found a whole bunch of code, none of which worked..sigh.

 

So before I get kicked off the forum please help me out a bit. 

 

My problem is a super simple one; I need to upload a csv to a mySql database.

 

The first row of the csv should be discarded.  The file is comma separated... 

 

Every piece of code I've found either only inserts the first row or merges the last value on one line with the first value on the next, or simply doesn't work... 

 

I'm quite frustrated and I'm sure one of you aces knows how simple this is and can help...

 

I've tried methods with fgetcsv and with foreach and split... double sigh.

 

I appreciate it a lot.

[Mchl]

Is it a one time operation, or you need to do it on regular basis?

 

Did you try LOAD DATA INFILE ?

[remmingtonshowdown]

Hello,

 

I need this to operate though a php script for regular use...

 

This is the code that I've been using... It seems to work save a few issues:

 

1.) on my local testing server (mac 10.5, apache 2.2.9, php 5.2.6 ) only the first row of the csv is inserted into the table...

2.) Because my csv file was saved on a mac I have to set the line separator as a carriage return "\r" and don't know if that will cause problems with pc files (wasn't this supposed to be fixed in php 5?)

3.) I still haven't figured out how to ignore the first row...

 

 

there's a lot of other stuff in here to address the Zikula CMS I'm working with and some of the modules included...that stuff works fine..

This bit properly increases according to the number of lines in the csv but only one is actually stored in the db...

//($insertedPubs = $insertedPubs ++) 
$insertedPubs = $insertedPubs + 1;

//($existingPubs = $existingPubs ++) 
$existingPubs = $existingPubs + 1;

 

 

<?php

//Begin upload process
$target = "files/";
$target = $target . basename( $_FILES['uploaded']['name']) ;
$ok=1;

//Here we check that $ok was not set to 0 by an error
if ($ok==0)
{
Echo "Sorry your file was not uploaded";
}

//If everything is ok we try to upload it
else
{
if (move_uploaded_file($_FILES['uploaded']['tmp_name'], $target))
{
echo "The file ". basename( $_FILES['uploaded']['name']). " has been uploaded<br/>";
}
else
{
echo "Sorry, there was a problem uploading your file.";
}
}

//Connect to the DB (will use Zikula here)
$databasehost = "localhost";
$databasename = "ica";
$databasetable = "zk_pagesetter_pubdata1";
$databaseusername ="ica";
$databasepassword = "sesame";
$fieldseparator = ",";
$lineseparator = "\r"; //how to check if the file was from a mac or windows wasn't this fixed in php 5??
$csvfile = $target;

//create var $timestamp with current time
$timestamp = date('Y-m-d H:i:s');

//create var $userid and get the user's id: Zikula AIP?
$userid = "2"; //currently set to admin but might be better set to anon?

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

//create var $exitstingPubs and get total number of existing pubs from pagesetter_counters for given tid X: pagesetter_getPubCount plugin
$pubCount = "SELECT `pg_count` FROM `zk_pagesetter_counters` WHERE `pg_name` = 'tid1'";

$existingPubsRaw = mysql_query($pubCount);
$row2 = mysql_fetch_row($existingPubsRaw);
$existingPubs = $row2[0];
$totalPubs = $row2[0];

/********************************/
/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************/
$addauto = 0;
/********************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************/
$save = 0;
$outputfile = "output.sql";
/********************************/


//Look for uploaded file
if(!file_exists($csvfile)) {
echo "File not found. Make sure you specified the correct path.\n";
exit;
}

$file = fopen($csvfile,"r");

if(!$file) {
echo "Error opening data file.\n";
exit;
}

$size = filesize($csvfile);

if(!$size) {
echo "File is empty.\n";
exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

$lines++;

$line = trim($line," \t");

$line = str_replace("\r","",$line);

/************************************
This line escapes the special character. remove it if entries are already escaped in the csv file
************************************/
//$line = str_replace("'","\'",$line);
/*************************************/

$linearray = explode($fieldseparator,$line);

$linemysql = implode("','",$linearray);

if($addauto)
	$query = "insert into $databasetable values('','$totalPubs','$totalPubs','approved','1','0','1','-1','1','1','admin','2','$timestamp','$timestamp','$timestamp','NULL','x_all','$linemysql');";
else
	$query = "INSERT INTO $databasetable values('$totalPubs','$totalPubs','approved','1','0','1','-1','1','1','admin','2','$timestamp','$timestamp','$timestamp','NULL','x_all','$linemysql');";

$queries .= $query . "\n";

@mysql_query($query);

echo $linemysql."<br>";


//($insertedPubs = $insertedPubs ++) 
$insertedPubs = $insertedPubs + 1;

//($existingPubs = $existingPubs ++) 
$existingPubs = $existingPubs + 1;

//for each item inserted into the db insert a new line into pagesetter_pubheader with the data: "pg_tid (X), pg_pid ($existingPubs), pg_hitcount (0), pg_onlineid ($existingPubs), pg_deleted (0)"
$pubheader = "INSERT INTO zk_pagesetter_pubheader values('1','$existingPubs','0','$existingPubs','0')";
mysql_query($pubheader)  or die(mysql_error());

//for each line inserted into the db insert a new line into pagesetter_revisions with the data: "pg_tid (X), pg_id (1), pg_pid ($existingPubs), pg_prevversion (0), pg_user ($userid), pg_timestamp ($timestamp)"
$revisions = "INSERT INTO zk_pagesetter_revisions values('1','$existingPubs','$existingPubs','0','2','$timestamp')";
mysql_query($revisions)  or die(mysql_error());

}


if($save) {

if(!is_writable($outputfile)) {
	echo "File is not writable, check permissions.\n";
}

else {
	$file2 = fopen($outputfile,"w");

	if(!$file2) {
		echo "Error writing to the output file.\n";
	}
	else {
		fwrite($file2,$queries);
		fclose($file2);
	}
}

}

echo "Found a total of $lines records in this csv file.<br/>";

//actions preformed after all the results have been stored in the database
//insert $insertedPubs into pagesetter_counter for the given tid X
$counters = "UPDATE zk_pagesetter_counters SET pg_count = $existingPubs WHERE pg_name = 'tid1'";
mysql_query($counters)  or die(mysql_error());
echo "<br/>total number of pubs now: $existingPubs";


//Close DB connection
@mysql_close($con);

?>