I’m trying to import an excel file with 15,000 record in a local php and mysql system, but it’s always stop inserting in after 3000 records and ignoring the rest of records. Even in the hosted copy of the system it’s insert only 3027 record.
Can I get some help please?
Import csv php script
if ($this->form_validation->run() == true) {
if (isset($_FILES['userfile'])) {
$this->load->library('upload');
$config['upload_path'] = $this->digital_upload_path;
$config['allowed_types'] = 'csv';
$config['max_size'] = $this->allowed_file_size;
$config['overwrite'] = true;
$config['encrypt_name'] = true;
$config['max_filename'] = 25;
$this->upload->initialize($config);
if (!$this->upload->do_upload()) {
$error = $this->upload->display_errors();
$this->session->set_flashdata('error', $error);
admin_redirect('products/import_csv');
}
$csv = $this->upload->file_name;
$arrResult = [];
$handle = fopen($this->digital_upload_path . $csv, 'r');
if ($handle) {
while (($row = fgetcsv($handle, 15000, ',')) !== false) {
$arrResult[] = $row;
}
fclose($handle);
}
$titles = array_shift($arrResult);
$updated = 0;
$items = [];
foreach ($arrResult as $key => $value) {
$supplier_name = isset($value[24]) ? trim($value[24]) : '';
$supplier = $supplier_name ? $this->products_model->getSupplierByName($supplier_name) : false;
$item = [
'name' => isset($value[0]) ? trim($value[0]) : '',
'code' => isset($value[1]) ? trim($value[1]) : '',
'barcode_symbology' => isset($value[2]) ? mb_strtolower(trim($value[2]), 'UTF-8') : '',
'brand' => isset($value[3]) ? trim($value[3]) : '',
'category_code' => isset($value[4]) ? trim($value[4]) : '',
'unit' => isset($value[5]) ? trim($value[5]) : '',
'sale_unit' => isset($value[6]) ? trim($value[6]) : '',
'purchase_unit' => isset($value[7]) ? trim($value[7]) : '',
'cost' => isset($value[8]) ? trim($value[8]) : '',
'price' => isset($value[9]) ? trim($value[9]) : '',
'alert_quantity' => isset($value[10]) ? trim($value[10]) : '',
'tax_rate' => isset($value[11]) ? trim($value[11]) : '',
'tax_method' => isset($value[12]) ? (trim($value[12]) == 'exclusive' ? 1 : 0) : '',
'image' => isset($value[13]) ? trim($value[13]) : '',
'subcategory_code' => isset($value[14]) ? trim($value[14]) : '',
'variants' => isset($value[15]) ? trim($value[15]) : '',
'cf1' => isset($value[16]) ? trim($value[16]) : '',
'cf2' => isset($value[17]) ? trim($value[17]) : '',
'cf3' => isset($value[18]) ? trim($value[18]) : '',
'cf4' => isset($value[19]) ? trim($value[19]) : '',
'cf5' => isset($value[20]) ? trim($value[20]) : '',
'cf6' => isset($value[21]) ? trim($value[21]) : '',
'hsn_code' => isset($value[22]) ? trim($value[22]) : '',
'second_name' => isset($value[23]) ? trim($value[23]) : '',
'supplier1' => $supplier ? $supplier->id : null,
'supplier1_part_no' => isset($value[25]) ? trim($value[25]) : '',
'supplier1price' => isset($value[26]) ? trim($value[26]) : '',
'slug' => $this->sma->slug($value[0]),
];
if ($catd = $this->products_model->getCategoryByCode($item['category_code'])) {
$tax_details = $this->products_model->getTaxRateByName($item['tax_rate']);
$prsubcat = $this->products_model->getCategoryByCode($item['subcategory_code']);
$brand = $this->products_model->getBrandByName($item['brand']);
$unit = $this->products_model->getUnitByCode($item['unit']);
$base_unit = $unit ? $unit->id : null;
$sale_unit = $base_unit;
$purcahse_unit = $base_unit;
if ($base_unit) {
$units = $this->site->getUnitsByBUID($base_unit);
foreach ($units as $u) {
if ($u->code == $item['sale_unit']) {
$sale_unit = $u->id;
}
if ($u->code == $item['purchase_unit']) {
$purcahse_unit = $u->id;
}
}
} else {
$this->session->set_flashdata('error', lang('check_unit') . ' (' . $item['unit'] . '). ' . lang('unit_code_x_exist') . ' ' . lang('line_no') . ' ' . ($key + 1));
admin_redirect('products/import_csv');
}
unset($item['category_code'], $item['subcategory_code']);
$item['unit'] = $base_unit;
$item['sale_unit'] = $sale_unit;
$item['category_id'] = $catd->id;
$item['purchase_unit'] = $purcahse_unit;
$item['brand'] = $brand ? $brand->id : null;
$item['tax_rate'] = $tax_details ? $tax_details->id : null;
$item['subcategory_id'] = $prsubcat ? $prsubcat->id : null;
if ($product = $this->products_model->getProductByCode($item['code'])) {
if ($product->type == 'standard') {
if ($item['variants']) {
$vs = explode('|', $item['variants']);
foreach ($vs as $v) {
if (!empty(trim($v))) {
$variants[] = ['product_id' => $product->id, 'name' => trim($v)];
}
}
}
unset($item['variants']);
if ($this->products_model->updateProduct($product->id, $item, null, null, null, null, $variants)) {
$updated++;
}
}
$item = false;
}
} else {
$this->session->set_flashdata('error', lang('check_category_code') . ' (' . $item['category_code'] . '). ' . lang('category_code_x_exist') . ' ' . lang('line_no') . ' ' . ($key + 1));
admin_redirect('products/import_csv');
}
if ($item) {
$items[] = $item;
}
}
}
// $this->sma->print_arrays($items);
}
Insert script
public function add_products($products = [])
{
if (!empty($products)) {
foreach ($products as $product) {
$variants = explode('|', $product['variants']);
unset($product['variants']);
if ($this->db->insert('products', $product)) {
$product_id = $this->db->insert_id();
foreach ($variants as $variant) {
if ($variant && trim($variant) != '') {
$vat = ['product_id' => $product_id, 'name' => trim($variant)];
$this->db->insert('product_variants', $vat);
}
}
}
}
return true;
}
return false;
}
Advertisement
Answer
I think you’re using codeigniter, so i recommend you to use yield (generator in php) and insert_bulk to handle some big data or some huge records to optimize your code and keep your cpu/memory not to leaks or timeout.
Here’s some logic :
- Import your csv and store it in generator.
- Process your titles, key values from csv in one process,DO NOT store it in array variable except your titles only.
- Convert the generator to array, and do your another process.
- Do your logic to insert the data and Store your inserted data to generator.
- Convert the generator to array and insert it using
insert_batchfunction from codeigniter.
Actually you can combine poin 4 to the poin 2 so you have one process do all the logic and the result is the data you want to insert to the database.
Here’s my simple code how you implement the logic :
<?php
// I think you're using codeigniter as your framework here, so i recommend you using yield and insert_bulk to insert the data
$filename = "testdata.csv";
$titles = [];
$readcsv = function($filename)use(&$titles){
$keys = ['name','code','barcode_symbology','brand','category_code','unit','sale_unit','purchase_unit','cost','price','alert_quantity','tax_rate','tax_method','image','subcategory_code','variants','cf1','cf2','cf3','cf4','cf5','cf6','hsn_code','second_name','supplier1','supplier1_part_no','supplier1price','slug'];
$handle = fopen($filename,"r");
$first = true;
while (($data = fgetcsv($handle, 15000, ",")) !== FALSE) {
if($first){
$titles = $data;
$first = false;
}else{
if(count($keys) == count($data)){
yield array_combine($keys,$data);
}
}
}
fclose($handle);
};
// Your csv data will be in this variable and your $titles now have value
$yourdata = iterator_to_array($readcsv($filename));
// This function store your inserted data to the yield (generator in php) if the conditions true
$list_insert_data = function($yourdata){
// Loop your csv data and do your logic here...
$break = false;
$cnt = 1;
foreach($yourdata as $v){
if(!empty($v) && !$break){
yield $v;
}
// Just simple logic to store 3 records from the csv
if($cnt == 3){
$break = true;
}else if($break){
break;
}
$cnt++;
}
};
// This variable contains 3 array from the csv data
$inserted = iterator_to_array($list_insert_data($yourdata));
// Then insert the data to the database.
$this->db->insert_batch('product_variants', $inserted);
UPDATED:
If you’re using mysql, you need to check your max_allowed_packet and check it in your mysql SHOW VARIABLES WHERE Variable_name LIKE '%max_allowed_packet%' and increase or double the value.
Change in the my.ini file by including the single line under [mysqld] or [client] section in your file:
max_allowed_packet=1024M
then restart the MySQL service and you are done. Hope this simple logic help you out of trouble.