如何使用PHP CodeIgniter解析CSV数据并插入数据库

Let's fix and complete your existing code with proper validation, parsing logic, and database integration—here's a step-by-step solution tailored to your setup:

1. Polish the View (Add Feedback & Clarity) #

Your existing upload form works, but we can add user-friendly feedback messages:

<form method="post" action="<?php echo site_url('Welcome/csvfile'); ?>" enctype="multipart/form-data">
    <input type="file" name="csvfileread" required accept=".csv">
    <p><small>Only CSV files are supported (max 1MB)</small></p>
    <input type="submit" name="submit" value="Upload & Import">
</form>

<?php if($this->session->flashdata('status')): ?>
    <div class="alert <?php echo $this->session->flashdata('status_type'); ?>">
        <?php echo $this->session->flashdata('status'); ?>
    </div>
<?php endif; ?>

2. Complete the Controller Logic #

Here's the full controller code with secure file handling, CSV parsing, and database insertion. We'll use native PHP CSV functions (since CodeIgniter doesn't include a built-in CSVReader library by default—we'll cover a custom library option later):

public function csvfile() {
    // Load required tools
    $this->load->library('upload');
    $this->load->model('Csv_import_model'); // We'll build this model next
    $this->load->library('session');

    if(isset($_POST["submit"])) {
        // Configure upload settings
        $upload_config = [
            'upload_path' => './uploads/', // Create this folder and set permissions to 755
            'allowed_types' => 'csv',
            'max_size' => 1024, // 1MB limit
            'overwrite' => TRUE
        ];

        $this->upload->initialize($upload_config);

        // Check if upload succeeded
        if (!$this->upload->do_upload('csvfileread')) {
            $error_msg = $this->upload->display_errors();
            $this->session->set_flashdata([
                'status' => $error_msg,
                'status_type' => 'alert-danger'
            ]);
            redirect('Welcome'); // Send user back to upload page
        } else {
            // Get uploaded file details
            $file_info = $this->upload->data();
            $csv_file_path = $file_info['full_path'];

            // Open CSV file
            $csv_handle = fopen($csv_file_path, "r");

            // Skip header row (remove this line if your CSV has no column names)
            fgetcsv($csv_handle);

            $imported_rows = 0;
            $bulk_data = []; // For faster bulk inserts

            // Parse each row
            while (($row = fgetcsv($csv_handle, 10000, ",")) !== FALSE) {
                // Validate row data (adjust to match your database requirements)
                if(!empty($row[0]) && !empty($row[1])) {
                    // Map CSV columns to your database table columns
                    $row_data = [
                        'name' => $row[0],
                        'email' => $row[1],
                        'phone' => $row[2] ?? '', // Use null coalescing for optional columns
                        // Add more fields as needed
                    ];

                    $bulk_data[] = $row_data;
                    $imported_rows++;
                }
            }

            fclose($csv_handle);

            // Insert bulk data into database
            if(!empty($bulk_data)) {
                $this->Csv_import_model->insert_bulk($bulk_data);
            }

            // Send success feedback
            $this->session->set_flashdata([
                'status' => "Successfully imported $imported_rows rows!",
                'status_type' => 'alert-success'
            ]);
            redirect('Welcome');
        }
    } else {
        // Load the upload view if form wasn't submitted
        $this->load->view('csv_upload_view');
    }
}

3. Create the Database Model #

Separate database logic into a model (application/models/Csv_import_model.php):

class Csv_import_model extends CI_Model {
    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    // Bulk insert for better performance with large CSVs
    public function insert_bulk($data) {
        // Replace 'your_table_name' with your actual database table
        return $this->db->insert_batch('your_table_name', $data);
    }
}

4. Optional: Custom CSVReader Library #

If you want to use a dedicated CSVReader library instead of native functions, create application/libraries/CSVReader.php:

class CSVReader {
    public function read_csv($file_path, $delimiter = ',') {
        $rows = [];
        if (($handle = fopen($file_path, 'r')) !== FALSE) {
            while (($row = fgetcsv($handle, 10000, $delimiter)) !== FALSE) {
                $rows[] = $row;
            }
            fclose($handle);
        }
        return $rows;
    }
}

Then adjust the controller's parsing section to use this library:

$this->load->library('CSVReader');
$csv_rows = $this->csvreader->read_csv($csv_file_path);
array_shift($csv_rows); // Skip header

foreach($csv_rows as $row) {
    // Same validation and bulk data logic as before
}

Key Tips for Success: #

• Folder Permissions: Ensure the uploads folder in your CodeIgniter root has write permissions (chmod 755 uploads on Linux/macOS).
• CSV Format: Double-check your CSV uses commas as delimiters (adjust the delimiter in fgetcsv if using tabs or semicolons).
• Validation: Add more checks (like email format, numeric values) based on your database schema to avoid invalid data.
• Large Files: For very large CSVs, consider processing rows in chunks to avoid memory issues.

内容的提问来源于stack exchange，提问作者aakanksha khandelwal