Uploads from CSV files

For quick inserting and updating records in tables of models it is possible to use data uploading from files in ".csv" format, which can be received from exporting of MS Excel file or other system. In CSV the file data are presented in the form of lines where the values are divided by a special character usually ";". Thus all first values before a separator belong to the first column, the second - to the second, etc.

Below you will find an example of step by step creation of data uploader from .csv file for model of products, the class "Products".

1. Create a Button

To create a button on the page of model to redirect to the page of uploader as described in Model Setting. Create "models/includes/products-index-bottom.php" file with the following content.

if($system -> user -> checkModelRights($system -> model -> getModelClass(), "update"))
    $update_path = "location.href='".$system -> registry -> getSetting("AdminPanelPath")."custom/?view=upload'";
else
    $update_path = "dialogs.showAlertMessage('{no_rights}')";
?>

<div class="extra-admin-buttons">
   <input type="button" class="button-light" value="Update products database " onclick="<? echo $update_path; ?>" />
</div>

2. Create a page of uploader

We create a special page four our future loader ,read more in Additional notes to Admin Panel section. Let’s create "models/customs/upload.php" file.

<?
$products = new Products();
$done = false;
$back_url = $registry -> getSetting('AdminPanelPath')."model/?model=products";

//Search for the passed CSV file
if(isset($_GET['action'], $_FILES['price']) && $_GET['action'] == "update")
{
    //If wrong file format - show an error
    if(Service :: getExtension($_FILES['price']['name']) != "csv")
        $system -> reload("custom/?view=upload&error=wrong-file");

    //Count data from the file and put into an array
    $data = file($_FILES['price']['tmp_name']);

    //Data processing 
    //Creation and update of model records

    ...
}

include $registry -> getSetting("IncludeAdminPath")."includes/header.php";
?>
<div id="columns-wrapper">
    <div id="model-form">
        <div class="column-inner">
            <h3 class="column-header">Update of products databse</h3>
            <? 
                if($done)
                {
                    //Load is successful 
                    //Display results 
                    ...
                }
                //Errors processing 
                else if(isset($_FILES['price'], $_GET['error']) || !$done) 
                {
                    $error = "Data wasn’t updated";

                    if($_GET['error'] == "wrong-file")
                        $error .= " Wrong file format.";

                    echo '<div class="form-errors"><p>'.$error.'</p></div>';
                }
            ?>

            <div class="clear"></div>
            <p>Date of last update:
            <? echo $registry -> getDatabaseSetting("products_update"); ?></p>
            <p>Select .csv file of products database and click "Send" button.</p>
            <form method="post" enctype="multipart/form-data" action="?view=upload&action=update">
               <div>
                  <input type="file" name="price" />
                  <input type="submit" class="button-light" value="Send" />
                  <input type="button" class="button-dark" value="Back" onclick="location.href='<? echo $back_url; ?>'" />
               </div>
            </form>
        </div>
    </div>
</div>
<?
include $registry -> getSetting("IncludeAdminPath")."includes/footer.php";
?>

3. Data processing

After counting data into $data array, we need to count every element of array which is a string with data divided by ";".

//Columns numbers: 0 – articul  (key), 1 – name etc.
$columns = array("articul" => 0, "name" => 1, "price" => 2, "quantity" => 3 "description" => 4);

//Select all products id, after that only missing products will remain in an array
$missed_products = $products -> selectColumn(array("fields->" => "id"));

foreach($data as $key => $string) //Process each string 
{
    $fields = explode(";", $string); //Divide the string into an array of fields

    //If all fields are not found in the string, skip it
    //We may add an additional data validation in a string
    if(!isset($fields[$columns["articul"]],
       $fields[$columns["name"]], $fields[$columns["price"]], 
       $fields[$columns["quantity"]],
       $fields[$columns["description"]]))
        continue;
 
    foreach($fields as $key => $value) //Delete extra symbols from the fields 
        $fields[$key] = trim($value);
 
    //Delete extra symbols from integer fields
    $fields[$columns["price"]] = preg_replace("/[^d]/ui", "", $fields[$columns["price"]]);
    $fields[$columns["number"]] = preg_replace("/[^d]/ui", "", $fields[$columns["quantiy"]]);
 
    //Usually additional transformations and/or data validation 
    //would be performed to insert data into a record 
 
    $new_product = true; //By default, the product is new (not in database)
 
    //Trying to find a product in database by articul
    $product = $products -> findRecord(array("articul" => $fields[$columns["articul"]]));
 
    if($product) //Product found, it is already in a table
    {
        $new_product = false;
        $id_index = array_search($product -> id, $missed_products);
        unset($missed_products[$id_index]); //Delete it’s id from missing products
    }
    else //The product is new – create a new empty record
    {
        $product = $products -> getEmptyRecord();
        $product -> articul = $fields[$columns["articul"]];
    }
 
    //Activate a product and assign the values of other fields to it
    $product -> active = 1;
    $product -> name = $fields[$columns["name"]];
    $product -> price = $fields[$columns["price"]];
    $product -> number = $fields[$columns["quantity"]];
    $product -> parameters = $fields[$columns["description"]];
 
    if($new_product) //Create a product
    {
        $product -> create();
        $created_products ++;
    }
    else //Update the product
    {
        $product -> update();
        $updated_products ++;
    }
 
    //If there are missing products in .csv, turn them off
    if(count($missed_products))
    {
        $products -> updateManyRecords(array("active" => 0), 
                                       array("id->in" => implode(",", $missed_products)));

        $missed_products = count($missed_products);
    }
 
    $done = true; //flag of successful load
 
    //Update last load time
    $registry -> setDatabaseSetting("products_update", I18n :: getCurrentDateTime());
}

4. Display results

In this case only quantity of products will be displayed, for example, it is possible to create arrays of articuls instead of counters and display data of products in details.

if($done)
{
    echo '<div class="form-no-errors"><p>Data is successfully updated</p></div>';
    echo "<p>Updated products: ".$updated_products."</p>";
    echo "<p>Added products: ".$created_products."</p>";
    echo "<p>Missing products in CSV file:".$missed_products."</p>";
}

5. Popular errors

  1. Presence of ";" symbol in CSV file as not a column separator.
  2. The size of uploaded file is too large. You can define PHP settings and "MaxFileSize" option in "config/settings.php" file also you may need to enlarge max size of uploaded file in "php.ini" file.
  3. Limit on the maximum run time of PHP script.
  4. Data in CSV file contain too much "garbage" (not allowed symbols), which you need to clean by using string functions or regular expressions.