Upload from CSV files

For quick adding 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 is presented in the form of lines where the values are divided by a special character usually ";". So 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 in model's interface

First we need to create a button on the page of model to redirect to the page of uploader as described in Model settings section. Create "customs/models/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 data" onclick="<? echo $update_path; ?>" />
</div>

2. Create a page of uploader

We create a special page for our future uploader, read more in Additional notes to admin panel section. Let’s create "customs/adminpanel/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 it into array
    $data = file($_FILES['price']['tmp_name']);

    //Data processing
    //Creation and updating 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)
                {
                    //Upload is successful 
                    //Display results 
                    ...
                }
                //Errors processing 
                else if(isset($_FILES['price']))
                {
                    $error = "Data was not updated";

                    if(isset($_GET['error']) && $_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 placing data into $data array, we need to process every element of array which is a string with data divided by ";".

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

//Select all products ids, after that only missing products will remain in this 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["code"]],
       $fields[$columns["name"]], $fields[$columns["price"]], 
       $fields[$columns["quantity"]],
       $fields[$columns["description"]]))
        continue;
 
    foreach($fields as $key => $value) //Delete extra symbols from all 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 here goes additional transformations and data validation 
    //before data will be inserted into record object
 
    $new_product = true; //By default, the product is new (not in database)
 
    //Trying to find a product in database by the passed vendor code
    $product = $products -> findRecord(array("code" => $fields[$columns["code"]]));
 
    if($product) //Product is found, so it is already in the table
    {
        $new_product = false;
        $id_index = array_search($product -> id, $missed_products);
        unset($missed_products[$id_index]); //Delete its id from missing products list
    }
    else //The product is new – create a new empty record
    {
        $product = $products -> getEmptyRecord();
        $product -> code = $fields[$columns["code"]];
    }
 
    //Activate the 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 upload time
    $registry -> setDatabaseSetting("products_update", I18n :: getCurrentDateTime());
}

4. Display results

In this sample only a quantity of products will be displayed, for example, it is possible to create arrays of vendor codes instead of counters and display data of products in more 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.