upload csv and import to database

poltergeist · May 7, 2020, 7:48pm

Hello,

I am currently facing some trouble with .csv import using a form. I am trying to use a code after uploading .csv, but it looks like OPTIONALLY ENCLOSED BY ‘"’ does not work proper since I am missing 1st value of each line…import just skips the first value of each line. Since I am only allowed to use OPTIONALLY ENCLOSED BY ‘"’ with SC, i will need a solution that will work with any .csv that has the following format:

CSV:

“line1-value1”,“line1-value2”
“line2-value1”,“line2-value2”

Code:

$sql = "LOAD DATA LOCAL INFILE '$my_csv'
	INTO TABLE `data_csv`
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY ''

(`value1`,
`value2`)";

sc_exec_sql($sql);

Another question: Is it possible to check if there are any duplicates being imported during import (in case one dataset has already been imported) and skip those lines. I would like to count those duplicates during import as well and fetch the number of duplicates into a variable.

poltergeist · May 7, 2020, 8:23pm

Ok…import works when i add an extra sign at the end of each line:

CSV:

“line1-value1”,“line1-value2”|
“line2-value1”,“line2-value2”

Code:


$sql = "LOAD DATA LOCAL INFILE '$my_csv'
	INTO TABLE `data_csv`
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '|'

(`value1`,
`value2`)";

sc_exec_sql($sql);

I would still like to solve this without 1 extra step…

jaimeflores · May 7, 2020, 8:24pm

Hey you know I need your help.

I need to load an Excel or CSV file and then add the information to the database and do not know how to do.

What happens is that users have to load that file have it in excel and csv save saves them as such:

“value1”, “value2”, “value3”
“value1”, “value2”, “value3”
“value1”, “value2”, “value3”

and i was looking and found this query

LOAD DATA INFILE “/ home / jaime / Desktop / ensayo.csv” INTO TABLE test FIELDS TERMINATED BY “,”;

and I have two problems
one load some values and the other is that nose as I can do when I publish to the server address put

please help

if you could send me the form where you do it please
I’ll be waiting for your answer
thanks

aducom · May 7, 2020, 8:24pm

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;

poltergeist · May 7, 2020, 8:24pm

I would LOAD DATA in a tmp table and then INSERT IGNORE to final table, drop the tmp table…much faster

aducom · May 7, 2020, 8:24pm

Yes, but than you cannot count the records, which was a demand?

poltergeist · May 7, 2020, 8:24pm

@ 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);

aducom · May 7, 2020, 8:24pm

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.

poltergeist · May 7, 2020, 8:24pm

well,

import to tmp
count records
INSERT IGNORE to final table (one col needs to be Unique in final table)
count again

aducom · May 7, 2020, 8:24pm

you win

poltergeist · May 7, 2020, 8:24pm

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

jamieO · May 7, 2020, 8:27pm

[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!

aducom · May 7, 2020, 8:27pm

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

jamieO · May 7, 2020, 8:27pm

[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!

RHS · May 7, 2020, 8:27pm

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.

aducom · May 7, 2020, 8:27pm

That’s the used function in my code snippet

RHS · May 7, 2020, 8:27pm

No, the second not …

aducom · May 7, 2020, 8:27pm

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.

jamieO · May 7, 2020, 8:27pm

thats really cool thanks guys!

jamieO · May 7, 2020, 8:28pm

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;