How To Insert Multiple data in the database from CSV

To insert multiple data into a database from a CSV file, you can use a programming language such as Python, PHP, or Java to read the CSV file and insert the data into the database. Here is a general process for doing this:

  1. Connect to the database: Establish a connection to your database using the appropriate credentials.
  2. Read the CSV file: Use a CSV reader library to read the data from the CSV file. Some examples of CSV reader libraries are csv module in Python or fgetcsv in PHP.
  3. Parse the data: Parse the data from the CSV file and format it in a way that can be inserted into the database.
  4. Insert the data: Use an SQL statement to insert the data into the database. You can use a loop to insert the data one row at a time or use a bulk insert statement to insert multiple rows at once.

Here is an example PHP code snippet that demonstrates how to insert data from a CSV file into a MySQL database:

<?php

function file_get_contents_chunked($link, $file, $chunk_size, $queryValuePrefix, $callback)
{
    try {
        $handle = fopen($file, "r");
        $i = 0;
        while (! feof($handle)) {
            call_user_func_array($callback, array(
                fread($handle, $chunk_size),
                &$handle,
                $i,
                &$queryValuePrefix,
                $link
            ));
            $i ++;
        }
        fclose($handle);
    } catch (Exception $e) {
        trigger_error("file_get_contents_chunked::" . $e->getMessage(), E_USER_NOTICE);
        return false;
    }

    return true;
}
$link = mysqli_connect("localhost", "root", "pass", "huge-csv");
$success = file_get_contents_chunked($link, "sample-dataset.csv", 2048, '', function ($chunk, &$handle, $iteration, &$queryValuePrefix, $link) {
    $TABLENAME = 'tbl_lead';
    $chunk = $queryValuePrefix . $chunk;

    // split the chunk of string by newline. Not using PHP's EOF
    // as it may not work for content stored on external sources
    $lineArray = preg_split("/\r\n|\n|\r/", $chunk);
    $query = 'INSERT INTO ' . $TABLENAME . '(id, name, email) VALUES ';
    $numberOfRecords = count($lineArray);
    for ($i = 0; $i < $numberOfRecords - 2; $i ++) {
        // split single CSV row to columns
        $colArray = explode(',', $lineArray[$i]);
        $query = $query . '(' . $colArray[0] . ',"' . $colArray[1] . '","' . $colArray[2] . '"),';
    }
    // last row without a comma
    $colArray = explode(',', $lineArray[$i]);
    $query = $query . '(' . $colArray[0] . ',"' . $colArray[1] . '","' . $colArray[2] . '")';
    $i = $i + 1;

    // storing the last truncated record and this will become the
    // prefix in the next run
    $queryValuePrefix = $lineArray[$i];
    mysqli_query($link, $query) or die(mysqli_error($link));

    /*
     * {$handle} is passed in case you want to seek to different parts of the file
     * {$iteration} is the section of the file that has been read so
     * ($i * 4096) is your current offset within the file.
     */
});

if (! $success) {
    // It Failed
}

Here is an example PHP code snippet that demonstrates how to insert data from a CSV file into a MySQL database:

import csv
import mysql.connector

# Connect to the database
mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="database_name"
)

# Create a cursor object
mycursor = mydb.cursor()

# Read the CSV file
with open('data.csv') as csvfile:
    reader = csv.reader(csvfile)

    # Loop through each row in the CSV file and insert it into the database
    for row in reader:
        sql = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"
        values = (row[0], row[1], row[2])
        mycursor.execute(sql, values)

# Commit changes to the database
mydb.commit()

# Close the database connection
mydb.close()

In this example, we assume that the CSV file has three columns (column1, column2, and column3) and that the table in the database also has these three columns. You may need to adjust the code to match the structure of your CSV file and database table.

Leave a Reply

Your email address will not be published. Required fields are marked *