Yes, but than you cannot count the records, which was a demand?
@ jaimeflores: could you please write in a way so we can understand?
Count Record before importing…
$server_path = ‘path_to_csv_file’;
$csv_filename = ‘xyz.csv’;
$csv_path = $server_path . ‘/’ . $csv_filename;
$fields_terminated = ‘,’; // MYSQL STATEMENT
$fields_enclosed = ‘"’; // MYSQL STATEMENT
$lines_terminated = ’
'; // MYSQL STATEMENT
if(file_exists($csv_path)){
$CSVLineCount = count(file($csv_path));
echo 'Number of records in CSV: ’ . $CSVLineCount;
}
$sqlLoadData_CSV2TMP = “LOAD DATA LOCAL INFILE '” . $csv_path . “’
INTO TABLE tmp
FIELDS TERMINATED BY '” . $fields_terminated . “’
ENCLOSED BY '” . $fields_enclosed . “’
LINES TERMINATED BY '” . $lines_terminated . "’
IGNORE 1 LINES
(a,
b,
c)
SET
b = ‘Hello World’;
sc_exec_sql($sqlLoadData_CSV2TMP);
The questions was to count duplicates:
I would like to count those duplicates during import as well and fetch the number of duplicates into a variable.
well,
- import to tmp
- count records
- INSERT IGNORE to final table (one col needs to be Unique in final table)
- count again
you win 
actually it took me 2 days to figure out the best way to delete and count duplicates, since a JOIN, with my amount of data, on the same table would take long even indexed Thx…next time it’s your turn
[QUOTE=aducom;11580]If you need to check for duplicates then you have to work record-by-record. Importing the record will generate a database error. You can trap this error and adjust count. This is an example (without duplicates check) of a csvimport I used in an application
$cnt=0;
$handle = fopen(‘D:/Program Files (x86)/NetMake/v6/wwwroot/scriptcase/file/doc/csvupload/’.{csv}, ‘r’);
if ($handle)
{
set_time_limit(0);
//the top line is the field names
$fields = fgetcsv($handle, 4096, ';');
$empty='';
//loop through one row at a time
while (($data = fgetcsv($handle, 4096, ';')) !== FALSE)
{
$data = array_combine($fields, $data);
if($data['Geboortedatum']=='') {$data['Geboortedatum']='01-01-1900';}
$stm ='replace into lid (LidId,Voornaam,Voorletters,Voorvoegsels,Achternaam,'
. 'Straat,Huisnummer,Postcode,Woonplaats,Geboortedatum,Geslacht,'
. 'EMail,TelefoonVast,TelefoonMobiel,LidSinds) values ('
. '"'.$data['Lid-nr'].'",'
. '"'.$data['Voornaam'].'",'
. '"'.$empty.'",'
. '"'.$empty.'",'
. '"'.$data['Naam'].'",'
. '"'.$data['Straat'].'",'
. '"'.$empty.'",'
. '"'.$data['Postcode'].'",'
. '"'.$data['Woonplaats'].'",'
. 'STR_TO_DATE("'.$data['Geboortedatum'].'","%e-%m-%Y"),'
. '"'.$data['Geslacht'].'",'
. '"'.$data['E-mail'].'",'
. '"'.$empty.'",'
. '"'.$data['GSM'].'",'
. 'STR_TO_DATE("'.$data['Lid vanaf'].'","%e-%m-%Y"))';
sc_exec_sql($stm);
$cnt++;
}
fclose($handle);
}
{reccnt}=$cnt;[/QUOTE]
Albert can I ask please if you used a control or blank app for this? reason being is I am wanting to add csv import to my app and this is only example I can find.
Also what would the file path be if not local? as you do have a local program files path stated…?
Thanks in advance for your help! You been a real star so far!
You need a database connection and thus a control application. This file is local on the server and you can upload it to a certain directory. Other solutions are to make your own upload routine. You can find some instruction on the next link:
[QUOTE=aducom;15063]You need a database connection and thus a control application. This file is local on the server and you can upload it to a certain directory. Other solutions are to make your own upload routine. You can find some instruction on the next link:
http://www.w3schools.com/php/php_file_upload.asp[/QUOTE]
yup upload is not a problem thats the easy part 
so control app thx!
Look at php function fgetcsv. Examples: http://www.w3schools.com/php/func_filesystem_fgetcsv.asp and http://www.bin-co.com/php/scripts/csv_import_export.
That’s the used function in my code snippet
No, the second not …
Oh yes, even the second
But they give a full code snippet like mine, but still the same function. But the export sample is nice too.
thats really cool thanks guys!
hey guys hoping I can get some help on this.
so have been trying to get this import working… have the form, upload and file opening, but failing on insert…
table index key is auto inc and not inserted or included, but the error is:
Error
Undefined index: mbrDOB
Undefined index: mbrNo
Undefined index: mbrFname
Undefined index: mbrLname
The code is as follows:
$cnt=0;
$handle = fopen(‘C:/Program Files (x86)/NetMake/v7/wwwroot/scriptcase/file/doc/csvupload/’.{csv}, ‘r’);
if(!$handle) die(‘Cannot open uploaded file.’);
if ($handle)
{
set_time_limit(0);
//the top line is the field names
$fields = fgetcsv($handle, 4096, ‘;’);
$empty=’’;
// session variable - owner
$clubID = [usr_club];
//loop through one row at a time
while (($data = fgetcsv($handle, 4096, ‘;’)) !== FALSE)
{
$data = array_combine($fields, $data);
if($data[‘mbrDOB’]==’’) {$data[‘mbrDOB’]=‘01-01-1900’;}
$stm =‘INSERT INTO mbr (mbrClubID,mbrNo,mbrFname,mbrLname,mbrDOB)
values
(’
. ‘"’.$clubID.’",’
. ‘"’.$data[‘mbrNo’].’",’
. ‘"’.$data[‘mbrFname’].’",’
. ‘"’.$data[‘mbrLname’].’",’
. ‘STR_TO_DATE("’.$data[‘mbrDOB’].’","%e-%m-%Y"),’;
sc_exec_sql($stm);
$cnt++;
}
fclose($handle);
}
{reccnt}=$cnt;
Guys???
Help please! anybody…
Test the generated sql statement. It looks to me as if the quotes are not used and the data is seen as a field name.
thx will look into that
I am having problem in using load local data infile… the error is:
“Error while accessing the database:
The used command is not allowed with this MySQL version”
anyone have encountered and solved this problem? thanks!
looks likes a compatibility problem. Please check your MySQL version and the version required for Scriptcase.