CSV data upload issue

Friends,

now I have move to work on new things..

I have a below code where it suppose to help me upload the data from CSV file to get uploaded in to the DB.

when I am trying to execute it give me error as a below

Deprecated: Function split() is deprecated in C:\xampp\htdocs\test\upload\upload_data.php on line 55


<?php
/********************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************/
include_once "C:/xampp/htdocs/test/include/database.php";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "filename.csv";
/********************************/
/* 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 = 1;
$outputfile = "output.sql";
/********************************/


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();
$sucess= "";
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 $tbl_name0 values('','$linemysql');";

else
$query = "insert into $tbl_name0 values('$linemysql');";

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

@mysql_query($query);
}
echo "Found a total of $lines records in this csv file.\n";

@mysql_close($con);

?>



my requirement is :

I have a table with 21 columns ...but when user upload the data he will only need to insert 4 field data so, I need to restrict him uploading more field column

And this 4 fields should exact match column when user upload in the csv comma deliminator format.....

Also, User should able to upload this file from his desktop.

please help me learn this.

regards,
nani

As of PHP 5.3, split() (http://php.net/manual/en/function.split.php) is deprecated. Use Regular expressions and preg_split() (http://www.php.net/manual/en/function.preg-split.php) to split the CSV by line mate.

Or just use explode() by "\n". It'll still do. Although you'll have to change to your code logic.

As of PHP 5.3, split() (http://php.net/manual/en/function.split.php) is deprecated. Use Regular expressions and preg_split() (http://www.php.net/manual/en/function.preg-split.php) to split the CSV by line mate.

Or just use explode() by "\n". It'll still do. Although you'll have to change to your code logic.

So, can irequest you to provide me any script that can help me ...you belive or not I have wasted my full day today just searching ways to get this thing function ...as a last chance I have posted here...

I am not some urgent assignment which I need to deliver 2marrow but kept pending from long time.

Regards,
nani

Or just use fgetcsv of course.
Why are you reinventing the wheel? fgetcsv has existed since like 4.0.

Or just use fgetcsv of course.
Why are you reinventing the wheel? fgetcsv has existed since like 4.0.

Fou-lu & redcoder,

Sorry for posting late update ...but In reality after applying preg_split(), It worked but now I am stuck that this things are declared in array and insterting in to the DB but as a requirement. I should able to do below things.

In the feilds of the data I have a column for email and also in DB but while inserting, it should check if the same email Id exist in the db already ?, if it is then it should ignore that row and go to another row ....

on the second things i have a column with the dd-mm-yyyy h:i:s format but while inserting it to db it should get converted in to yyyy-mm-dd H:i:s (this should require because in CSV format condition will never work nice and then user end up with duplicate records, to just avoid that if i can set up a server scripting if user, enter this date&time in any style it will get uploaded as yyyy-mm-dd h:i:s)

I am expecting above 2 conditions because most of my data would be a email unique so if multiple user uploading different data and having same email Id it should avoid doing that or different user uploading same data it should IGNORE.

Any thoughts...

Regards,
Nani

Insert using an INSERT IGNORE. If there's conflict on a unique or primary key, it will reject it and keep going instead of failing. Use either a bind and execute for the batch, or issue in groups of say 100.
This date format is fine for GNU. Run it through a dateTime object and output with the ::format method, or use strtotime and date to convert it to the proper string.

Insert using an INSERT IGNORE. If there's conflict on a unique or primary key, it will reject it and keep going instead of failing. Use either a bind and execute for the batch, or issue in groups of say 100.
This date format is fine for GNU. Run it through a dateTime object and output with the ::format method, or use strtotime and date to convert it to the proper string.

can you give me any example as i am bit confused to do it on above script.....

$dt = new DateTime($dateInCSV);
print $dt->format('Y-m-d h:i:s');

For dates.


INSERT IGNORE INTO table (field1, field2) VALUES (val1_1, val2_1), (val1_2, val2_2), ...

for insert ignore. Insert ignore is not necessary with prepared statements as you can ignore the execute result.

and for the datetime I have tried to do but in the above script it is extracting all the data by using ',' and defined in a single variable '$linemysql', that is making me confused to put format or strtotime methods so, I am expecting some though or example for this ...

However i have rectified email issue ....

$dt = new DateTime($dateInCSV);
print $dt->format('Y-m-d h:i:s');

For dates.


INSERT IGNORE INTO table (field1, field2) VALUES (val1_1, val2_1), (val1_2, val2_2), ...

for insert ignore. Insert ignore is not necessary with prepared statements as you can ignore the execute result.

Agreed on insert Ignore rather I have assigned unique key in my table for email ....I hope that solves my problem.....any suggestions on this?

If your variable is an entire line, it would cause the dateTime construction to fail as it won't consider it a valid date string. You need to separate that line up into the "columns" it originally had. Of course, using fgetcsv would perform this by itself and give you an array of each field per fetch.

As for insert ignore, you require unique or primary keys to make use of it. These should be set up as you see fit anyways, otherwise duplicates are not considered duplicates. If that doesn't work, you need to go back all the way to the db design phase as something isn't set up properly.

If your variable is an entire line, it would cause the dateTime construction to fail as it won't consider it a valid date string. You need to separate that line up into the "columns" it originally had. Of course, using fgetcsv would perform this by itself and give you an array of each field per fetch.


Fou-Lu,

I know I am troubling you lot but I have tried fgetcsv since yestarday on the above script and could not achieve the success so, can i ask you to please take some time to help me change on the above script so that I can understand how really it works...

As I always say I am yet newbie and trying to learn lot from different sources....please help me

Regards,
Nani