update db from a csv file

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

update db from a csv file

Post by bouncer »

hi there.

i need a script that will be updating my database from a CSV file. i'm already able to import a CSV file into a table in my db but it only add's info, and if i change something in my CSV file it won't update my db to do that i have to truncate that table and re-import the file but i just need to update existing records or the records that come with the new CSV and keep the oder in the table, one more problem is that in the CSV file my dates come in this format dd-mm-yyyy, and i want to store in the db like this yyyy-mm-dd, maybe i'm doing something wrong in the query.

Code: Select all

 
@mysql_query("TRUNCATE TABLE $databasetable");
@mysql_query("LOAD DATA INFILE '$csvfile' INTO TABLE `content`
                FIELDS TERMINATED BY ';'
                OPTIONALLY ENCLOSED BY '\"'
                LINES TERMINATED BY '\\n'
                (`ref`, `conta_interna`, @datafield1, `iniciativa`, `escalao`, `tarifario`, `nome`, `nif`, @datafield2, `morada`, `localidade`, `cod_postal`, `cod_postal1`, `sexo`, `situacao_iva`, `nome_beneficiario`, `nif_beneficiario`, `morada_beneficiario`, `localidade_beneficiario`, `cod_postal_beneficiario`, `cod_postal_beneficiario1`, `telefone_contacto`, `e-mail`, `sexo_beneficiario`, `estado`, @datafield3, `ext_garantia`, `exp_memoria`, @datafield4, `imei`, `telefone_dados`, `info_estado`, `produto_ean`, `n_serie_pc`, `chave_parceiro`)
                SET 
                `data_registo` = STR_TO_DATE(@datafield1, '%Y/%m/%d'),
                `data_nascimento` = STR_TO_DATE(@datafield2, '%Y/%m/%d'),
                `data_estado` = STR_TO_DATE(@datafield3, '%Y/%m/%Yd),
                `data_aux` = STR_TO_DATE(@datafield4, '%Y/%m/%d')");
 
i hope you can help me. :roll:

thanks in advance
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: update db from a csv file

Post by Christopher »

STR_TO_DATE() takes the format of the existing date:

Code: Select all

                SET 
                `data_registo` = STR_TO_DATE(@datafield1, '%m/%d/%Y'),
                `data_nascimento` = STR_TO_DATE(@datafield2, '%m/%d/%Y'),
                `data_estado` = STR_TO_DATE(@datafield3, '%m/%d/%Y'),
                `data_aux` = STR_TO_DATE(@datafield4, '%m/%d/%Y)");
You are also missing an ending quote on your third STR_TO_DATE().
(#10850)
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Re: update db from a csv file

Post by bouncer »

i've rewrited my initial script and now i'm end up with this, with this i can update or insert. :)

Code: Select all

 
while( ( $data = fgetcsv( $file, 1000, ';', '"' ) ) !== false ) {
    $ref = $data[0];
    $conta_interna = $data[1];
    $data_registo = date( 'Y-m-d', strtotime( $data[2] ) );
    $iniciativa = $data[3];
    $escalao = $data[4];
    $tarifario = $data[5];
    $nome = $data[6];
    $nif = $data[7];
    $data_nascimento = date( 'Y-m-d', strtotime( $data[8] ) );
    $morada = $data[9];
    $localidade = $data[10];
    $cod_postal = $data[11];
    $cod_postal1 = $data[12];
    $sexo = $data[13];
    $situacao_iva = $data[14];
    $nome_beneficiario = $data[15];
    $nif_beneficiario = $data[16];
    $morada_beneficiario = $data[17];
    $localidade_beneficiario = $data[18];
    $cod_postal_beneficiario = $data[19];
    $cod_postal_beneficiario1 = $data[20];
    $telefone_contacto = $data[21];
    $email = $data[22];
    $sexo_beneficiario = $data[23];
 
    $query = "SELECT `ref` FROM `content`";
    if( ! $result = mysql_query( $query ) ) {
      continue;
    }
 
    if( $line = mysql_fetch_array($result, MYSQL_ASSOC) ) {    // <------- something wrong with this if, never enter here
      $query = "UPDATE `content` SET `conta_interna`='$conta_interna', `data_registo`='$data_registo', 
        `iniciativa`='$iniciativa', `escalao`='$escalao', `tarifario`='$tarifario', `nome`='$nome', `nif`='$nif', 
        `data_nascimento`='$data_nascimento', `morada`='$morada', `localidade`='$localidade', `cod_postal`='$cod_postal', 
        `cod_postal1`='$cod_postal1', `sexo`='$sexo', `situacao_iva`='$situacao_iva', 
        `nome_beneficiario`='$nome_beneficiario', `nif_beneficiario`='$nif_beneficiario', 
        `morada_beneficiario`='$morada_beneficiario', `localidade_beneficiario`='$localidade_beneficiario', 
        `cod_postal_beneficiario`='$cod_postal_beneficiario', ``cod_postal_beneficiario1`='$cod_postal_beneficiario1', 
        `telefone_contacto`='$telefone_contacto', `e-mail`='$email', `sexo_beneficiario`='$sexo_beneficiario' 
        WHERE `ref`='$ref'";
                
    mysql_query( $query );
 
    if( mysql_affected_rows() <= 0 ) {
      // no rows where affected by update query
    } else {
                    $query = "INSERT INTO `content` (`ref`, `conta_interna`, `data_registo`, `iniciativa` ,`escalao`,
        `tarifario`, `nome`, `nif`, `data_nascimento`, `morada`, `localidade`, `cod_postal`, `cod_postal1`, 
        `sexo`, `situacao_iva`, `nome_beneficiario`, `nif_beneficiario`, `morada_beneficiario`, 
        `localidade_beneficiario`, `cod_postal_beneficiario`, `cod_postal_beneficiario1`, `telefone_contacto`, 
        `e-mail`, `sexo_beneficiario`, `estado`, `data_estado`, `ext_garantia`, `exp_memoria`, `data_aux`, `imei`, 
        `telefone_dados`, `info_estado`, `produto_ean`, `n_serie_pc`, `chave_parceiro`) VALUES ('$ref', 
        '$conta_interna', '$data_registo', '$iniciativa', '$escalao', '$tarifario', '$nome', '$nif', 
        '$data_nascimento', '$morada', '$localidade', '$cod_postal', '$cod_postal1', '$sexo', '$situacao_iva', 
        '$nome_beneficiario', '$nif_beneficiario', '$morada_beneficiario', '$localidade_beneficiario', 
        '$cod_postal_beneficiario', '$cod_postal_beneficiario1', '$telefone_contacto', '$email', 
        '$sexo_beneficiario', '', '', '', '', '', '', '', '', '', '', '')";
            
        mysql_query( $query );
 
               }
 
    mysql_free_result( $result );
              }
    
}
 
fclose( $file );
mysql_close( $con );
 
but i need some help to make it work.

thanks in advance
Last edited by bouncer on Fri Mar 21, 2008 4:29 am, edited 1 time in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: update db from a csv file

Post by Christopher »

That will work, but remember that it will be much slower than LOAD DATA INFILE.
(#10850)
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Re: update db from a csv file

Post by bouncer »

arborint wrote:That will work, but remember that it will be much slower than LOAD DATA INFILE.
each CSV file will have 100 lines at the must. but i dont know what's the problem with this

Code: Select all

 
if( $line = mysql_fetch_array($result, MYSQL_ASSOC) ) // it will never enter here, dont know why
 
any idea, maybe i'm doing something wrong ?

regards
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Re: update db from a csv file

Post by Luke »

You really should just use LOAD DATA dude... much faster... much easier.
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Re: update db from a csv file

Post by bouncer »

and how can i update existing rows and add new rows, with LOAD DATA ?

regards
Post Reply