Hello all,
I have a database in my project that has close to 30,000 records in it. I want to refresh this data once a month from a CSV file. I just want to update what has changed in all the (7) fields.
Has anyone ever done this before and have some sample code, or have any idea how to proceed?
Any help would be greatly appreciated.
Thanks in advance,
Dr. Tim
Dr Tim,
for imports from .cvs * I use this php code. But it is in php. If you find a way to write this in the Scriptcase I also recommend the solution.
<?php
$con=mysqli_connect(“localhost”,“xxxxxxx”,"xxxxxxx ",“xxxxxxx”);
// preveri povezavo z bazo
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$handle = @fopen(“register_za_test.csv”, “r”);
fgetcsv($handle); //preskoci prvo vrstico
$values=’’;
while (!feof($handle)) // Loop til end of file.
{
$buffer = fgets($handle, 4096); // Read a line.
$ID= 0;
$priimek_in_ime = 0;
$naslov = 0;
$posta = 0;
$spol = 0;
$telefon = 0;
$mobitel = 0;
$datum_rojstva = 0;
$tip = 0;
$clan_je = 0;
$izkaznica = 0;
$prejem_glasila = 0;
$cl_2012 = 0;
$obcina = 0;
$cl_2013 = 0;
$cl_2014 = 0;
$cl_2015 = 0;
$cl_2016 = 0;
$status = 0;
list($ID, $priimek_in_ime, $naslov, $posta, $spol, $telefon, $mobitel, $datum_rojstva, $tip, $clan_je, $izkaznica, $prejem_glasila, $cl_2012, $obcina, $cl_2013, $cl_2014, $cl_2015, $cl_2016, $status)=explode(";",$buffer);//Separate string by the means of ;
//values.=($a,$b,$c);// save values and use insert query at last or
// operacija z bazo - preveri, ce clan ze obstaja
$sql = “SELECT * FROM clani WHERE clani.ID = $ID”;
$result = $con->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$sql = “UPDATE clani
SET ID = ‘$ID’,
priimek_in_ime = ‘$priimek_in_ime’,
naslov = ‘$naslov’,
posta = ‘$posta’,
spol = ‘$spol’,
telefon = ‘$telefon’,
mobitel = ‘$mobitel’,
datum_rojstva = ‘$datum_rojstva’,
tip = ‘$tip’,
clan_je = ‘$clan_je’,
izkaznica = ‘$izkaznica’,
prejem_glasila = ‘$prejem_glasila’,
cl_2015 = ‘$cl_2012’,
obcina = ‘$obcina’,
cl_2013 = ‘$cl_2013’,
cl_2014 = ‘$cl_2014’,
cl_2015 = ‘$cl_2015’,
cl_2016 = ‘$cl_2016’,
status = ‘$status’
WHERE Id = $ID”;
$con->query($sql);
}
} else {
$result = mysqli_query($con,“insert into clani VALUES ($ID, ‘$priimek_in_ime’, ‘$naslov’, ‘$posta’, ‘$spol’, ‘$telefon’, ‘$mobitel’, ‘$datum_rojstva’, ‘$tip’, ‘$clan_je’, ‘$izkaznica’, ‘$prejem_glasila’, ‘$cl_2012’, ‘$obcina’, ‘$cl_2013’, ‘$cl_2014’, ‘$cl_2015’, ‘$cl_2016’, ‘$status’)”);
}
}
$con->close();
?>
[HR][/HR]
you can also use Mysql’s Load Data command to import the data into your table.
More info is available at http://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table
This is only a partial answer to your question but will get you up and going.