Hi all… I am trying to get File Upload (csv) to parse out and update my records in the Database.
Does anyone know of a tutorial or a Sample application?
One option is to add a file uploader and apply processing code in the onvalidate event. A sample of an uploader is
// Clean field message
{message} = ‘’;
// Starts array of File
$arr_file = array();
// Address Directory's uploader of Application
$path = $this->Ini->path_doc;
// Absolute path of the file
$fileid = $path . '/uploads/' . {fileid};
$row = 0;
if(is_file($fileid)) {
$tran = 0;
sc_begin_trans();
if (($handle = fopen($fileid, "r")) !== FALSE) {
// (re)start transaction
$tran++;
if ($tran==5000) {
sc_commit_trans();javascript:void(0);
sc_begin_trans();
$tran=1;
}
// read header record
$data=fgetcsv($handle, 1000, ",");
// does it start with 'val-ref'?
if (strtolower($data[0]) != 'val_ref') {
{message} .= '<br>this is not a valid data file';
} else {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
if ((isset($data[4])) && ($data[4] != '')) {
if(!isset($data[16])) {
$data[16]='';
}
// prevent that quotes will cause mysql syntax errors
$data[1]=addslashes($data[1]);
$data[2]=addslashes($data[2]);
$data[3]=addslashes($data[3]);
$data[4]=addslashes($data[4]);
$data[5]=addslashes($data[5]);
$data[12]=addslashes($data[12]);
$data[15]=addslashes($data[15]);
$data[16]=addslashes($data[16]);
$data[8] = str_replace('/', '-', $data[8]);
$data[8] = date('Y-m-d', strtotime($data[8]));
// process property
$insert_sql = "INSERT INTO property (val_ref, property_address, suburb, town, district,".
"land_area, floor_area, bedrooms, building_age, category, zone,".
"capital_value, land_value, improvement_value, valuation_date) values (".
"'$data[0]','$data[1]','$data[2]','$data[3]','$data[4]',".
"$data[9],$data[10],$data[11],'$data[12]','$data[15]','$data[16]',".
"$data[7], $data[6], $data[5], '$data[8]') on duplicate key update ".
"val_ref='$data[0]', property_address='$data[1]', suburb='$data[2]', town='$data[3]', district='$data[4]',".
"land_area=$data[9], floor_area=$data[10], bedrooms=$data[11], building_age='$data[12]', category='$data[15]', zone='$data[16]',".
"capital_value=$data[7], land_value=$data[6], improvement_value=$data[5], valuation_date='$data[8]'" ;
sc_exec_sql($insert_sql);
// process valuation
$insert_sql = "REPLACE INTO valuation (val_ref, valuation_date, improvement_value,".
"land_value, capital_value) values ('$data[0]','$data[8]',$data[5],".
"$data[6],$data[7])";
sc_exec_sql($insert_sql);
// process sales record
$check_sql = "SELECT max(property_sale_seq) as cnt, max(sale_date) as last_sale, count(*) as c from property_sales where ".
"property_val_ref='$data[0]'";
sc_lookup(rs, $check_sql);
// if the current sale date > max sale_date then there's a new sale.
// but if the count is 0 then there is no record at all
if($data[13]=='') {
$data[13]='null';
} else {
$data[13] = str_replace('/', '-', $data[13]);
$data[13] = date('Y-m-d', strtotime($data[13]));
}
$mx=({rs[0][0]}); // latest sale seq
$sd=({rs[0][1]}); // sale date from database
$c=({rs[0][2]}); // # records
if($c==0) { // no records
$mx=1;
} else {
if(($data[13]!=$sd)&&($data[13]!='null')) { // saledates are not equal.
if($data[13]>$sd) { // sale date is newer (is ok)
$mx++;
} else { // sale date is older which is not ok.
}
}
}
if($data[14]==null) {$data[14]='null';}
$insert_sql = "INSERT INTO property_sales (property_val_ref, property_sale_seq, sale_date,".
"sales_person, last_sale_date, last_sale_price) values ('$data[0]', $mx, '$data[13]',".
"0,'$data[13]',$data[14]) on duplicate key update ".
"property_val_ref='$data[0]', property_sale_seq=$mx, sale_date='$data[13]',".
"sales_person=0, last_sale_date='$data[13]', last_sale_price=$data[14]";
sc_exec_sql($insert_sql);
$row++;
}
}
}
sc_commit_trans();
fclose($handle);
}
} else {
{message} .= "Please, check the document path of the application!";
}
// Now reset null to 0. That is needed to be able to make the view';
$update_sql = "UPDATE property SET correct_suburb=0 WHERE correct_suburb IS NULL";
sc_exec_sql($update_sql);
$update_sql = "UPDATE property SET complex_id=0 WHERE complex_id IS null";
sc_exec_sql($update_sql);
{message}.='<br>number of records processed: '.$row;
if($row>0) {
{message}.='<br><b>processing done.</b>';
}
3 Likes
Thanks so much!
In this example is the form field name that contains the Uploader called: {fileid} ?
Yes, formfields are always between the curly brackets.
How can I execute above code for the attached excel fileALLINONE.csv (967 Bytes)
and please if possible in steps
I’m not opening files like these. I think that the example should be clear enough.
Since I purchased a program two months ago, I have been searching for a solution to a problem that no one can answer unfortunately, namely, how can I update a table in the database through a form or a grid from Excel or CSV file.
I paid $ 30 for that and unfortunately, I couldn’t find a solution.
All the support teams have tried but unsuccessfully
Actually, there are a number of videos on SC site, but it has errors and the examples attached to these videos have errors and do not work.
I want a tested example.
If any body has a tested project compatible with Arabic language, I will be glad to him even if it is paid project.
I really appreciate your support in this endeavor.
First of all I have no knowledge about Arabic, but I assume that this can be solved in UTF8? The csv does not contain Arabic and the sample I have above should work find, although you have to adapt it to the fields in your csv and the structure of the database. In general, add a file uploader component and use the onvalidatevent. It’s a sample of our production environment, so I wonder what your problem is.
One thing though, if you use MySQL, the date format is yyyy-mm-dd and you might need to convert your dates to that format.
If you need to import the data into a form or grid and update from there, then the best approach is to create a work-table where you put the upload into and where you can apply your updates. Create your grid/form based upon this work-table. Then in a separate process you can move the data over to the real tables and clean the work-table if applicable. Would that work?
And another tip is follow Aducom’s advice to create a work table to import into.
But what I find easiest and best for auditing the result is to NOT parse the data into columns on import.
Do that as a separate operation using SQL queries to parse the column data. (
So Import all the fields into a varchar import field. Here the data is Pipe separated. And it is easy to use MySQL substring_index to select the parts and map to the relevant fields you need. It’s also 10x easier sorting out date string issues using DATE_FORMAT() to correct all ways that dates can be exported.
Good luck.