update db from a csv file
Posted: Thu Mar 20, 2008 9:58 am
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.
i hope you can help me.
thanks in advance
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')");
thanks in advance