Importing Large Text , CSV Files into MySQL with Chunked Approach
If you want to upload large CSV files and efficiently import them into a MySQL database, you may encounter server restrictions or configurations that prevent the direct upload or insertion of large datasets into MySQL. To overcome this limitation, we can adopt a chunked approach. In this method, data is sent and received in chunks of 1 MB per request handled by the controller. Each 1 MB chunk of data is stored temporarily in a server directory. When all chunks have been received, they are combined into a single CSV file using calculations such as total requests and current requests. This combined CSV file is then imported into the MySQL database using the LOAD DATA LOCAL INFILE command.
To enable this functionality, the allow_local_infile setting must be enabled in both MySQL and PHP configurations. Additionally, routes and constants need to be configured in the respective route and constant files. These settings can be adjusted according to your specific requirements.
The controller function also includes Role-Based Access Control (RBAC) and permission functionality. If these features are not needed, you can remove the relevant imports and lines of code before testing the solution according to your needs.
Step 1: Setup CodeIgniter 4 Project
Prerequisites
Before starting, ensure you have the following:
- PHP 8.0 or later installed
- MySQL 8.0 installed
- CodeIgniter 4 framework set up
- BootStrap 5 included in your project.
- JavaScript (preferably jQuery) included in your project
Setting Up the Project
- Create a New CodeIgniter 4 Project If you haven't set up a CodeIgniter 4 project yet, you can follow the official installation guide. Using this command you can easily create project composer create-project codeigniter4/appstarter Your-Project-Name. Make sure you are inside your project folder in terminal or console or command line.
Step 2: Create Database Table
We need a table to store chunk data. Create a table named evo_upload_master with the following schema:
-- -- Table structure for table `evo_upload_master` -- CREATE TABLE `evo_upload_master` ( `id` int NOT NULL, `cocode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `profit_ctr` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `account` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `inv_ref` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `document_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `clrng_doc` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `t_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `skey` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `pbk` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `gl_acct` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `sg` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `dc` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `syear` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `pm` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `payt` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `assignment` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `reference` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `tx` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `stext` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `document_header_text` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `amount_in_doc_curr` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `crcy` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `amount_in_local_currency` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0.00', `withholding_tax_amt` decimal(15,2) NOT NULL DEFAULT '0.00', `withhldg_tax_base_amount` decimal(15,2) NOT NULL DEFAULT '0.00', `doc_date` date NOT NULL, `pstng_date` date NOT NULL, `entry_date` date NOT NULL, `bline_date` date NOT NULL, `net_due_dt` date NOT NULL, `clearing` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `arrear` int NOT NULL, `tax_number_3` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, `fiscal_year` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL, `pan` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL, `reference_original` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL, `type_of_report` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `status` tinyint(1) NOT NULL DEFAULT '0', `custom_unique_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `custom_group_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `up_file_id` varchar(50) COLLATE utf8mb4_general_ci NOT NULL, `created_by` int NOT NULL, `updated_by` int DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `updated_at` datetime DEFAULT NULL, `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Indexes for table `evo_upload_master` -- ALTER TABLE `evo_upload_master` ADD PRIMARY KEY (`id`), ADD KEY `custom_group_id` (`custom_group_id`), ADD KEY `custom_group_id_2` (`custom_group_id`); -- -- AUTO_INCREMENT for table `evo_upload_master` -- ALTER TABLE `evo_upload_master` MODIFY `id` int NOT NULL AUTO_INCREMENT; COMMIT;
Step 3: Create Model
Create a new model named FrontIrSapEvoUploadMaster.php inside the app/Models directory.Open Terminal or Command Prompt: Navigate to your CodeIgniter 4 project directory using the terminal or command prompt. php spark make:model FrontIrSapEvoUploadMaster After running the command, CodeIgniter will create a new model file under the app/Models directory with the name FrontIrSapEvoUploadMaster.php
Your new model will have a basic structure like this:
<?php
namespace App\Models;
use CodeIgniter\Model;
class FrontIrSapEvoUploadMaster extends Model
{
protected $DBGroup = 'default';
protected $table = 'evo_upload_master';
protected $primaryKey = 'id';
protected $useAutoIncrement = true;
protected $returnType = 'array';
protected $useSoftDeletes = false;
protected $protectFields = true;
protected $allowedFields = ['id', 'cocode', 'profit_ctr', 'account', 'inv_ref', 'document_no', 'clrng_doc', 't_type', 'skey', 'pbk', 'gl_acct', 'sg', 'dc', 'syear', 'pm', 'payt','assignment', 'reference', 'tx', 'stext','amount_in_doc_curr', 'crcy', 'amount_in_local_currency', 'withholding_tax_amt', 'withhldg_tax_base_amount', 'doc_date', 'pstng_date', 'entry_date', 'bline_date', 'net_due_dt', 'clearing', 'arrear', 'document_header_text', 'user_name', 'status', 'created_by', 'updated_by', 'updated_at', 'deleted_at','type_of_report','custom_unique_id','custom_group_id','tax_number_3','fiscal_year','name','pan'];
protected $uploadeFields = ['cocode', 'profit_ctr', 'account', 'inv_ref', 'document_no', 'clrng_doc', 't_type', 'skey', 'pbk', 'gl_acct', 'sg', 'dc', 'syear', 'pm', 'payt','assignment', 'reference', 'tx', 'stext','document_header_text', 'user_name','amount_in_doc_curr', 'crcy', 'amount_in_local_currency', 'withholding_tax_amt', 'withhldg_tax_base_amount', 'doc_date', 'pstng_date', 'entry_date', 'bline_date', 'net_due_dt', 'clearing', 'arrear','tax_number_3','fiscal_year','name','pan','created_by'];
protected $reportFields = ['cocode', 'profit_ctr', 'account', 'inv_ref', 'document_no', 'clrng_doc', 't_type', 'skey', 'pbk', 'gl_acct', 'sg', 'dc', 'syear', 'pm', 'payt','assignment', 'reference', 'tx', 'stext','document_header_text', 'user_name','amount_in_doc_curr', 'crcy', 'amount_in_local_currency', 'withholding_tax_amt', 'withhldg_tax_base_amount', 'doc_date', 'pstng_date', 'entry_date', 'bline_date', 'net_due_dt', 'clearing', 'arrear'];
protected $searchFields = ['id','cocode', 'profit_ctr', 'account', 'inv_ref', 'document_no', 'clrng_doc', 't_type', 'skey', 'pbk', 'gl_acct', 'sg', 'dc', 'syear', 'pm', 'payt','assignment', 'reference', 'tx', 'stext','document_header_text', 'user_name','amount_in_doc_curr', 'crcy', 'amount_in_local_currency', 'withholding_tax_amt', 'withhldg_tax_base_amount', 'doc_date', 'pstng_date', 'entry_date', 'bline_date', 'net_due_dt', 'clearing', 'arrear','tax_number_3','fiscal_year','name','pan','created_by'];
// Dates
protected $useTimestamps = false;
protected $dateFormat = 'datetime';
protected $createdField = 'created_at';
protected $updatedField = 'updated_at';
protected $deletedField = 'deleted_at';
// Validation
protected $validationRules = [];
protected $validationMessages = [];
protected $skipValidation = false;
protected $cleanValidationRules = true;
// Callbacks
protected $allowCallbacks = true;
protected $beforeInsert = [];
protected $afterInsert = [];
protected $beforeUpdate = [];
protected $afterUpdate = [];
protected $beforeFind = [];
protected $afterFind = [];
protected $beforeDelete = [];
protected $afterDelete = [];
function uuid_generate($query){
$db = \Config\Database::connect();
$queryResult = $db->query('SELECT UUID() as uuid')->getRow();
if ($queryResult) {
$uuid = $queryResult->uuid;
return $uuid;
} else {
return null;
}
}
function save_csv($query){
$db = \Config\Database::connect();
$result=$db->query($query);
if ($db->affectedRows() > 0) {
return $db->insertID();
}
return $result;
}
}
Step 4: Create Controller
Create a new controller named MyImport.php inside the app/Controllers directory. Just like you created model and verify that same will continue like you go to terminal and in command line point to your project folder and run the command php spark make:controller MyImport After running the command, CodeIgniter will create a new controller file under the app/Controllers directory with the name MyImport.php.
Your new controller will have a basic structure like this:
<?php
namespace App\Controllers;
use App\Controllers\BaseController;
use App\Models\FrontRoleToPermissionModel;
use App\Models\FrontIrSapEvoUploadMaster;
use Exception;
use CodeIgniter\I18n\Time;
use Ramsey\Uuid\Uuid;
use DateTime;
class MyImport extends BaseController
{
protected $session;
public $today;
public $totalError;
protected $validtor;
public function __construct()
{
$this->session = session();
$this->today = date('Y-m-d H:i:s');
}
public function index($id = 0, $category = NULL)
{
$request = service('request');
$nav_id = $request->getGet('nav_id');
$frontroles = new FrontRoleToPermissionModel();
$permission = $frontroles->get_premission($this->session->frontuser['role_id']);
$hasPermission = $frontroles->hasPrivilege("ImportInputKpi", $permission);
$data['title'] = getenv('site_title') . " | Import New";
$data['breadcrumb_title'] = "Import New";
$breadcrumb = array(
array(
'title' => 'Import New',
'link' => null
)
);
$data['role_permission'] = [
'role_id' => $this->session->frontuser['role_id'],
'role_name' => $this->session->frontuser['role_name'],
'hasPermission' => $hasPermission,
];
$data['nav_id'] = $nav_id;
$data['role_id'] = $this->session->frontuser['role_id'];
$data['breadcrumb'] = $breadcrumb;
$data['total_error'] = $this->totalError;
$data["validation"] = $this->validator;
return view('evo_upload_new', $data);
}
public function evo_upload(){
ini_set('mysql.allow_local_infile', 1);
ini_set('memory_limit', '2048M');
$request = service('request');
$postData = $request->getPost();
$nav_id = $request->getVar('nav_id');
$frontroles = new FrontRoleToPermissionModel();
$permission = $frontroles->get_premission($this->session->frontuser['role_id']);
$hasPermission = $frontroles->hasPrivilege("ImportKpi", $permission);
$response = [];
if (strtolower($request->getMethod()) === 'post') {
$rules = [
'csvFile' => [
'uploaded[csvFile]',
'mime_in[csvFile,text/csv,application/csv,text/blob,blob]',
'max_size[csvFile,100000]',
'errors' => [
'mime_in' => 'Invalid file mime.',
'ext_in' => 'Invalid file extension.',
'max_size' => 'File is too large.'
]
]
];
$validate = $this->validate($rules);
$this->totalError = $validate;
if (!$validate) {
$response = array("jsonrpc" => "2.0", "error" => ["code" => 100, "message" => alertmessage($message_type = 'alert-danger', $message = $message = \Config\Services::validation()->listErrors('list_custom'))], "id" => "id");
} else {
$FrontIrSapEvoUploadMaster = new FrontIrSapEvoUploadMaster();
$dbfields = $FrontIrSapEvoUploadMaster->uploadeFields;
$commasepFields=implode(',',$dbfields);
$totalChunks = intval(urldecode($postData['totalChunks']));
$currentChunk = intval(urldecode($postData['currentChunk']));
$file = $request->getFile('csvFile');
$name = $file->getName();
$originalName = $file->getClientName();
$fileSize=$file->getSizeByUnit('k');
$tempfile = $file->getTempName();
$ext = $file->getExtension();
$allowedExtensions = ['csv'];
$csvData = array_map('str_getcsv', file($file->getPathname()));
$is_header_verified=$this->get_VerifyHeaders($csvData,$currentChunk);
if (!in_array($ext, $allowedExtensions) || $is_header_verified === true) {
$response = array("jsonrpc" => "2.0", "error" => ["code" => 100, "message" => alertmessage($message_type = 'alert-danger', $message ='Invlid File, Only csv allowed OR The file you are trying to upload has not valid header for this upload.')], "id" => "id");
}else{
$chunk = $file->getTempName();
$js_timestamp=$this->extractNumbers(urldecode($postData['ufileName']));
$fileName=$this->session->frontuser['user_id'].'_'.urldecode($postData['ufileName']);
$orgfileName=urldecode($postData['orgfileName']);
$mytimestamp='new_'.$this->session->frontuser['user_id'].'_'.urldecode($postData['mytimestamp']);
$up_file_id=urldecode($postData['mytimestamp']);
$toURl = base_url() . 'myimport/index/?nav_id=' . $nav_id;
$targetDir = UPLOAD_PATH_IR_SAP_FRONT;
$targetFile = $targetDir.$fileName;
$targetFilePrimaryKey = $targetDir. str_replace('.csv','.txt',$fileName);
$lines = file($chunk, FILE_IGNORE_NEW_LINES);
$mcontent = '';
foreach ($lines as $key => &$line) {
if (!empty($line)) {
$fields = explode(',', $line);
$cocode=$this->cleanIdFields(isset($fields[0]) ? $fields[0] : NULL);
$account=$this->cleanIdFields(isset($fields[2]) ? $fields[2] : NULL);
$document_no = $this->cleanFieldsText(isset($fields[4]) ? $fields[4] : NULL);
$mathchRecord = $FrontIrSapEvoUploadMaster->ignoreDuplicateRecord($account,$cocode,$document_no);
if(count($mathchRecord) <= 1){
$fields[0] = $cocode;
$fields[1] = $this->cleanIdFields(isset($fields[1]) ? $fields[1] : NULL);
$account=$this->cleanIdFields(isset($fields[2]) ? $fields[2] : NULL);
$fields[2] = $account;
$fields[3] = $this->cleanIdFields(isset($fields[3]) ? $fields[3] : NULL);
$fields[4] = $this->cleanFieldsText(isset($fields[4]) ? $fields[4] : NULL);
$fields[5] = $this->cleanFieldsText(isset($fields[5]) ? $fields[5] : NULL);
$fields[6] = $this->cleanFieldsText(isset($fields[6]) ? $fields[6] : NULL);
$fields[7] = $this->cleanFieldsText(isset($fields[7]) ? $fields[7] : NULL);
$fields[8] = $this->cleanFieldsText(isset($fields[8]) ? $fields[8] : NULL);
$fields[9] = $this->cleanFieldsText(isset($fields[9]) ? $fields[9] : NULL);
$fields[10] = $this->cleanFieldsText(isset($fields[10]) ? $fields[10] : NULL);
$fields[11] = $this->cleanFieldsText(isset($fields[11]) ? $fields[11] : NULL);
$fields[12] = $this->cleanFieldsText(isset($fields[12]) ? $fields[12] : NULL);
$fields[13] = $this->cleanFieldsText(isset($fields[13]) ? $fields[13] : NULL);
$fields[14] = $this->cleanFieldsText(isset($fields[14]) ? $fields[14] : NULL);
$fields[15] = $this->cleanFieldsText(isset($fields[15]) ? $fields[15] : NULL);
$fields[16] = $this->cleanFieldsText(isset($fields[16]) ? $fields[16] : NULL);
$fields[17] = $this->cleanFieldsText(isset($fields[17]) ? $fields[17] : NULL);
$fields[18] = $this->cleanFieldsText(isset($fields[18]) ? $fields[18] : NULL);
$fields[19] = $this->cleanFieldsText(isset($fields[19]) ? $fields[19] : NULL);
$fields[20] = $this->cleanFieldsText(isset($fields[20]) ? $fields[20] : NULL);
$fields[21] = $this->cleanFieldsText(isset($fields[21]) ? $fields[21] : NULL);
$fields[22] = $this->cleanFieldsText(isset($fields[22]) ? $fields[22] : NULL);
$fields[23] = $this->cleanFieldsText(isset($fields[23]) ? $fields[23] : NULL);
$fields[24] = $this->cleanFieldsText(isset($fields[24]) ? $fields[24] : NULL);
$fields[25] = $this->cleanFieldsText(isset($fields[25]) ? $fields[25] : NULL);
//date
$doc_date=$this->changeDateFormat(isset($fields[26]) ? $fields[26] : NULL);
$fields[26] = $doc_date;
$fields[27] = $this->changeDateFormat(isset($fields[27]) ? $fields[27] : NULL);
$fields[28] = $this->changeDateFormat(isset($fields[28]) ? $fields[28] : NULL);
$fields[29] = $this->changeDateFormat(isset($fields[29]) ? $fields[29] : NULL);
$fields[30] = $this->changeDateFormat(isset($fields[30]) ? $fields[30] : NULL);
$fields[31] = $this->changeDateFormat(isset($fields[31]) ? $fields[31] : NULL);
//end
$fields[32] = $this->cleanFieldsText(isset($fields[32]) ? $fields[32] : NULL);
$fields[33] =$this->cleanFieldsText(isset($fields[33]) ? $fields[33] : NULL);
$fields[34] = $this->calculateFiscalYear29($doc_date);
$fields[35] = $this->cleanFieldsText(isset($fields[35]) ? $fields[35] : NULL);
$fields[36] = $this->cleanFieldsText(isset($fields[35]) ? $fields[35] : NULL);
$liness = implode(',', $fields);
$mcontent .= $liness;
$mcontent .= "\n";
}
}
}
file_put_contents($chunk,$mcontent);
if (is_uploaded_file($chunk)) {
move_uploaded_file($chunk,$targetFile.'.part'.$currentChunk);
$response = array("jsonrpc" => "2.0", "result" => true, "id" => "id", 'toUrl' => $toURl, "error" => ["code" => 200,"message" => alertmessage($message_type = 'alert-success',$message = "Saved successfully.")]);
}
if ($currentChunk == $totalChunks - 1) {
$outputFile = fopen($targetFile, 'wb');
for ($i = 0; $i < $totalChunks; $i++) {
$inputFile = fopen($targetFile . '.part' . $i, 'rb');
if ($inputFile) {
$modifiedContent = fread($inputFile, filesize($targetFile . '.part' . $i));
fwrite($outputFile, $modifiedContent);
fclose($inputFile);
unlink($targetFile . '.part' . $i);
}
}
fclose($outputFile);
$filePath=$targetFile;
$linecount = count(file($filePath));
$query_load = "LOAD DATA LOCAL INFILE '" . esc($filePath) . "' INTO TABLE evo_upload_master CHARACTER SET latin1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (" . get_comma_seperator_single_comma($dbfields) . ") SET created_by = ". $this->session->frontuser['user_id']." , custom_group_id='".$mytimestamp."', custom_unique_id = REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(account,'_',document_no,'_',syear),'[^a-zA-Z0-9_]',''),' ', '_'),fiscal_year = CONCAT(YEAR(doc_date) - IF(MONTH(doc_date) < 4 OR (MONTH(doc_date) = 4 AND DAY(doc_date) < 1), 1, 0),'-',RIGHT(YEAR(doc_date) + IF(MONTH(doc_date) >= 4 OR (MONTH(doc_date) = 4 AND DAY(doc_date) >= 1), 1, 0), 2)),up_file_id=".$up_file_id."";
$FrontIrSapEvoUploadMaster->save_csv($query_load);
if (file_exists($filePath)) {
unlink($filePath);
unlink($targetFilePrimaryKey);
}
$response = array("jsonrpc" => "2.0", "result" => true, "id" => "id", 'toUrl' => $toURl, "error" => ["code" => 200,"message" => alertmessage($message_type = 'alert-success',$message = "Saved successfully.wait refreshing ...")]);
}
}
}
}
$response[csrf_token()] = csrf_hash();
return $this->response->setJSON($response);
exit;
}
function isDateFormat($value) {
$trimmedValue = trim($value);
$pattern1 = '/^\d{2}\/\d{2}\/\d{2}$/';
$pattern2 = '/^\d{2}-\d{2}-\d{2}$/';
return preg_match($pattern1, $trimmedValue) || preg_match($pattern2, $trimmedValue);
}
function changeDateFormat($field){
if ($this->isDateFormat($field)) {
return date('Y-m-d',strtotime($field));
}
return $field;
}
function isScientificNotation($number){
$pattern = '/^[+-]?(\d+(\.\d*)?|\.\d+)([eE][+-]?\d+)?$/';
return preg_match($pattern, $number) === 1;
}
function cleanIdFields($field){
return preg_replace('/[^a-zA-Z0-9\_-]/', '', $field);
}
function cleanFieldsText($field){
return preg_replace('/[^a-zA-Z0-9\/_ +|-]/', '', $field);
}
function extractNumbers($string) {
preg_match_all('/\d+/', $string, $matches);
return $matches[0];
}
function removeBOM($str) {
$bom = pack('H*','EFBBBF');
return preg_replace("/^$bom/", '', $str);
}
function removeBlankLinesFromCSV($filePath) {
$content = file_get_contents($filePath);
$lines = explode("\n", $content);
$nonEmptyLines = array_filter($lines, function ($line) {
return !empty(trim($line));
});
$newContent = implode("\n", $nonEmptyLines);
file_put_contents($filePath, $newContent);
}
function calculateFiscalYear29($date) {
$dateTime = DateTime::createFromFormat('d/m/Y', $date);
if (!$dateTime) {
$dateTime = DateTime::createFromFormat('d-m-Y', $date);
}
if (!$dateTime) {
return "Invalid date format";
}
$year = (int)$dateTime->format('Y');
$month = (int)$dateTime->format('n');
$fiscalYearString = ($month >= 10) ? $year . '-' . substr($year + 1, -2) : ($year - 1) . '-' . substr($year, -2);
return $fiscalYearString;
}
function calculateFiscalYear($date, $fiscalYearStartMonth = 4) {
$timestamp = strtotime($date);
$startMonth = $fiscalYearStartMonth;
$month = (int)date('m', $timestamp);
if ($month >= $startMonth) {
$fiscalYearStart = (int)date('Y', $timestamp);
$fiscalYearEnd = $fiscalYearStart % 100 + 1; // Get the next year's last two digits
$fiscalYear = $fiscalYearStart . '-' . sprintf('%02d', $fiscalYearEnd);
} else {
$fiscalYearEnd = (int)date('y', $timestamp);
$fiscalYearStart = $fiscalYearEnd - 1;
$fiscalYear = $fiscalYearStart . '-' . sprintf('%02d', $fiscalYearEnd);
}
return $fiscalYear;
}
function get_VerifyHeaders($csvData,$currentChunk){
$expectedHeaders = ['CoCode', 'Profit Ctr', 'Account','Inv. Ref.','DocumentNo','Clrng doc.','Type','Key','PBk','G/L Acct','SG','D/C','Year','PM','PayT','Assignment','Reference','Tx','Text','Document Header Text','User Name','Amount in Doc. Curr.','Crcy','Amount in Local Currency','Withholding Tax Amt','Withhldg Tax Base Amount','Doc. Date','Pstng Date','Entry Date','Bline Date','Net Due Dt','Clearing','Arrear'];
$firstRow = reset($csvData);
$expectedHeadersLower = array_map('strtolower', $expectedHeaders);
$firstRowHeaders = array_map('strtolower', array_values($firstRow));
if (($firstRowHeaders !== $expectedHeadersLower) && $currentChunk===0) {
return true;
}
return false;
}
}
Step 5: Create View
Create a new view file named evo_upload_new.php inside the app/Views directory. Navigate to your CodeIgniter 4 project directory using the terminal or command prompt and run the command php spark make:view evo_upload_new Your new view will be an empty PHP file where you can write your HTML, CSS, and JavaScript code as needed.
<?= $this->extend('front/layouts/master') ?>
<?php if (!empty($role_permission['hasPermission'])) { ?>
<?= $this->section('head') ?>
<?= link_tag('frontassets/libs/choices.js/public/assets/styles/choices.min.css') ?>
<?= link_tag('frontassets/css/dataTables.dateTime.min.css') ?>
<?= link_tag('frontassets/libs/admin-resources/rwd-table/rwd-table.min.css') ?>
<?= $this->endSection() ?>
<?php } ?>
<?= $this->section('content') ?>
<?php if (!empty($role_permission['hasPermission'])) { ?>
<?php $attributes = ['class' => 'custom-form', 'method' => "post", 'id' => 'fromkpiimport', 'enctype' => "multipart/form-data"];
echo form_open(base_url() . 'import/evo-upload/', $attributes); ?>
<div class="row">
<div class="col-md-12" id="container">
<div class="mb-3"><label for="file" class="form-label">Browse<span class="pristine-error text-help">*</span> <span class="badge bg-info font-size-11">Allowed File Extension: csv ,100MB</span> <span class="badge bg-danger font-size-11">Do not Refresh/Reload your browser when uploading in process.</span></label>
<input type="file" name="csvFile" id="csvFile" class="form-control" accept=".csv" />
</div>
<input type="hidden" id="file_ext" name="file_ext" value="<?= substr(md5(rand(10, 100)), 0, 10) ?>">
</div>
<div id="filelist" class="font-size-12"></div>
</div>
<div class="row">
<div class="table-rep-plugin">
<div class="table-responsive mb-0 modelcsvhtml" data-pattern="priority-columns">
</div>
</div>
</div>
<div class="modal-footer">
<div class="justify-content-between px-2">
<input type="hidden" id="model_id" name="model_id" value="import-kpi-data">
<input type="hidden" id="activity" name="activity" value="0">
<textarea id="sdata" name="sdata" style="display:none;"></textarea>
<input type="hidden" id="type_of_grade_score" name="type_of_grade_score" value="p1">
<input type="hidden" id="financial_year" name="financial_year" />
<input type="hidden" id="kpiids" name="kpiids" />
<button type="submit" class="gap-2 uploadsubmitButton btn btn-primary btn-sm waves-effect waves-light btn-submit" id="upload">Upload Document</button>
</div>
</div>
<?= form_close() ?>
<?php } else {
$this->include("front/unauthorize");
}
?>
<?= $this->endSection() ?>
<?php if (!empty($role_permission['hasPermission'])) { ?>
<?= $this->section('foot') ?>
<script src="<?= base_url('moment.min.js'); ?>"></script>
<script src="<?= base_url('jquery.validate.min.js'); ?>"></script>
<script src="<?= base_url('dataTables.dateTime.min.js'); ?>"></script>
<script src="<?= base_url('import.min.js'); ?>"></script>
<?= $this->endSection() ?>
<?php } ?>
Step 7: Javascript
Create a new javascript file named import.min.js inside the public directory. When you click on btnUpload (button) it will run the $("#fromkpiimport").valid(); validator function. jQuery Validation Plugin. If your form has no validation errors it will call uploadFile(); function. uploadFile function will send data as chunks 1 * 1024 * 1024 (1 MB) to controller.
var date = new Date();
var timestamp = date.getTime();
var delaytime = 2000;
var base_url = (document.querySelector("base") || {}).href;
var base_url_origin = window.location.origin + "/";
var urlParams = new URLSearchParams(window.location.search);
function showalert(message, container_id) {
$("#" + container_id)
.find(".popup_message")
.html("");
$("#" + container_id)
.find(".popup_message")
.css("display", "block");
$("#" + container_id)
.find(".popup_message")
.html('<div id="gobal_alert_message">' + message + "</div>");
$("#gobal_alert_message").delay(1000).fadeOut("slow");
}
function showalertdashboard(message, form_id, hide = "yes") {
$("#" + form_id).html(
'<div class="help-block-ajax" id="gobal_popup_message_dashboard">' +
message +
"</div>"
);
if (hide == "yes") {
$("#gobal_popup_message_dashboard").delay(1000).fadeOut("slow");
}
}
function showalertfromjs(message, form_id, hide = "No", message_type) {
var showhide = hide != "No" ? "showmessage_jscbf" : "";
$("#" + form_id).html(
'<div class="help-block-ajax" id="gobal_popup_message_dashboard"><div class="alert alert-dismissible ' +
message_type +
" " +
showhide +
' role="alert"><button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>' +
message +
"</div></div></div>"
);
if (hide == "yes") {
$("#gobal_popup_message_dashboard").delay(1000).fadeOut("slow");
}
}
function showJsalert(message, form_id, message_type, hide = 0) {
var showhide = hide === 1 ? "showmessage_jscbf" : "";
$("#" + form_id).html(
'<div class="help-block-ajax" id="gobal_popup_message_dashboard">' +
'<div class="alert alert-dismissible ' +
message_type +
" " +
showhide +
'" role="alert"><button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>' +
message +
"</div></div>"
);
$("#gobal_popup_message_dashboard").delay(1000).fadeOut("slow");
}
function showalert_mpop(message, container_id) {
$("#" + container_id).html("");
$("#" + container_id).css("display", "block");
$("#" + container_id).html(
'<div id="gobal_alert_message" class="alert-content">' + message + "</div>"
);
setTimeout(function () {
$("#gobal_alert_message").remove();
}, 10000);
}
function redirectDelay(delaytime, RUrl) {
setTimeout(function () {
window.location.href = "" + RUrl + "";
}, delaytime);
}
$(document).ready(function () {
$("#btnUpload").on("click", function () {
$("#fromkpiimport").valid();
});
$("#btnReset").on("click", function () {
window.location.reload();
});
$("#fromkpiimport").validate({
ignore: [],
rules: {
file: {
required: true,
},
},
messages: {
file: {
required: "is required",
},
},
submitHandler: function (form, e) {
e.preventDefault();
uploadFile();
},
errorElement: "em",
errorPlacement: function (error, element) {
error.addClass("help-block");
if (element.prop("type") === "checkbox") {
error.insertAfter(element.parent("label"));
} else {
error.insertAfter(element);
}
},
highlight: function (element, errorClass, validClass) {
$(element)
.parents(".col-sm-5")
.addClass("has-error")
.removeClass("has-success");
},
unhighlight: function (element, errorClass, validClass) {
$(element)
.parents(".col-sm-5")
.addClass("has-success")
.removeClass("has-error");
},
});
});
function uploadFile() {
var fileInput = document.getElementById("csvFile");
var file = fileInput.files[0];
document.getElementById("filelist").innerText = "Processing wait...";
if (file) {
var orgfilename = file.name;
var chunkSize = 1 * 1024 * 1024;
var totalChunks = Math.ceil(file.size / chunkSize);
var currentChunk = 0;
$("#upload").prop("disabled", true);
$("#csvFile").prop("disabled", true);
function uploadChunk() {
var formData = new FormData();
formData.append(
"csvFile",
file.slice(currentChunk * chunkSize, (currentChunk + 1) * chunkSize)
);
var token_value = $("#appcsrf").attr("content");
var MyTimestamp = timestamp;
var ufileName = "IRSAP_EVE_NEW_" + MyTimestamp + ".csv";
formData.append("currentChunk", encodeURIComponent(currentChunk));
formData.append("totalChunks", encodeURIComponent(totalChunks));
formData.append("cname", token_value);
formData.append("ufileName", encodeURIComponent(ufileName));
formData.append("orgfileName", encodeURIComponent(orgfilename));
formData.append("mytimestamp", encodeURIComponent(MyTimestamp));
var xhr = new XMLHttpRequest();
xhr.open(
"POST",
base_url + "import/evo-upload?nocache=" + new Date().getTime(),
true
);
xhr.onload = function () {
let responseObj = JSON.parse(xhr.response);
if (xhr.status === 200 && responseObj.error.code == 200) {
var progress = ((currentChunk + 1) / totalChunks) * 100;
document.getElementById("filelist").innerHTML =
"<h6 class='me-2 font-size-13 fw-bold loading'>Uploading in progress: " +
progress.toFixed(2) +
"%</h6>";
if (currentChunk < totalChunks - 1) {
currentChunk++;
uploadChunk();
} else {
$("#csvFile").prop("disabled", false);
showalertdashboard(responseObj.error.message, "globalAlertjs");
redirectDelay(500, responseObj.toUrl);
}
} else {
$("#csvFile").prop("disabled", false);
document.getElementById("filelist").innerHTML = "";
showalertdashboard(responseObj.error.message, "globalAlertjs", "no");
}
};
xhr.send(formData);
}
uploadChunk();
} else {
showalertfromjs(
"Kindly browse your csv file to process.",
"globalAlertjs",
"Yes",
(message_type = "alert-danger")
);
}
}
Conclusion & Flow Process
- Initialization and Setup:
- The class
MyImportextendsBaseControllerand initializes various properties, such as$session,$today,$totalError, and$validator. - In the constructor (
__construct), the session is started, and the current date-time is set.
- The class
- Index Method (
index):- This method handles the rendering of the import view.
- It fetches the current user's permissions, checks if the user has permission to import, and prepares the necessary data for the view.
- Evo Upload Method (
evo_upload):- This method handles the actual CSV file upload and processing.
- It sets the required PHP configurations, retrieves the POST data, and checks if the user has the necessary permissions.
- Validates the uploaded CSV file based on size, MIME type, and other criteria.
- Processes the uploaded CSV file:
- Reads the CSV file line by line.
- Cleans and validates each line's data.
- Reconstructs the CSV file with cleaned data.
- Handles file chunking for large files.
- If the last chunk is uploaded:
- Combines all chunks to recreate the original file.
- Processes the CSV file data to prepare it for database insertion.
- Executes a MySQL
LOAD DATA LOCAL INFILEcommand to insert the CSV data into the database. - Deletes the uploaded CSV file and its primary key file.
- Sends a JSON response with the result of the upload process.
- Utility Methods:
isDateFormat: Checks if a given value matches a date format.changeDateFormat: Converts a given date field to 'Y-m-d' format.isScientificNotation: Checks if a number is in scientific notation.cleanIdFieldsandcleanFieldsText: Cleans and sanitizes input fields.extractNumbers: Extracts numbers from a string.removeBOM: Removes Byte Order Mark (BOM) from a string.removeBlankLinesFromCSV: Removes blank lines from a CSV file.calculateFiscalYear29andcalculateFiscalYear: Calculate fiscal years based on given dates.get_VerifyHeaders: Verifies if the CSV file headers match the expected headers.