Importing data from Excel spreadsheets into MySQL using PHP is a commonly encountered task for developers. This task can be easily accomplished using CSV (Comma Separated Values) files and PHP’s MySQLi extension API. With just a few lines of code, developers can import data from multiple sheets, tables, and columns in Excel files and format them as rows in a MySQL database. In this article, we will discuss the step-by-step process of importing Excel data into MySQL using PHP and CSV files.
Cara Import File Excel ke Database MySQL dengan PHP | CSV Upload
The following is a step-by-step guide to importing Excel data into MySQL using PHP and CSV files:
Step 1: Extracting Data from Excel Spreadsheets
Before we begin the import process, we need to extract data from Excel spreadsheet into CSV files. CSV files can be easily created by saving the data in Excel spreadsheets as CSV format. In Excel, data is usually stored in tables that have rows and columns. Each row represents a record, while each column represents a data field. When saving Excel files in CSV format, this table structure is preserved. However, the data is saved as plain text using commas as separators.
Step 2: Creating MySQL Database and Table
Once we have the CSV files, the next step is to create a MySQL database and table where we will import the data. We can create the database and table using phpMyAdmin or any other MySQL management tool. The table should have the same structure as the table in the Excel spreadsheet. Each column in the table should correspond to a data field in the CSV file.
Step 3: Importing Data from CSV Files into MySQL
Next, we need to write PHP code that will read the data from the CSV files and import it into the MySQL database. We will use the PHP MySQLi extension API to accomplish this. The code below illustrates the basic structure of the script:
“`php
connect_error)
die(“Connection failed: ” . $conn->connect_error);
// Loop through the CSV files and insert the data into the MySQL table
if (($handle = fopen(“data.csv”, “r”)) !== FALSE)
while (($data = fgetcsv($handle, 1000, “,”)) !== FALSE)
$sql = “INSERT INTO mytable (column1, column2, column3) VALUES (‘”.$data[0].”‘, ‘”.$data[1].”‘, ‘”.$data[2].”‘)”;
$conn->query($sql);
fclose($handle);
// Close connection
$conn->close();
?>
“`
In the example above, we first define the MySQL server address, username, password, and database name. We then create a connection to the MySQL server. If the connection fails, an error message is displayed. Next, we loop through each row in the CSV file and insert the data into the MySQL table using a SQL INSERT statement. In this example, we’re assuming the CSV file has three columns. The SQL statement inserts the values of each column into three corresponding columns in the MySQL table. Finally, we close the connection to the MySQL server.
Step 4: Formatting and Validating Data
Before importing the data into MySQL, it’s important to format and validate the data. This ensures that the data is correctly entered into the MySQL table, and that no errors or inconsistencies are introduced. For example, you can check for data types, data length and format, and remove any unwanted characters or whitespace. You can also check for duplicates, null values, and empty strings. In addition, you can format the data for consistency, such as converting dates and text to a standard format.
Rajaputramedia.com
Another website that provides a guide to importing data from Excel into MySQL using PHP is rajaputramedia.com. The website provides a step-by-step guide that is similar to the one we provided above. However, rajaputramedia.com also includes some additional tips and tricks, such as how to handle special characters, how to import multiple sheets from Excel, and how to use mysqli_real_escape_string to prevent SQL injection attacks.
Cara Import File Excel ke Database MySQL dengan PHP | CSV Upload
Rajaputramedia.com recommends using the following code to import data from Excel into MySQL:
“`php
connect_error)
die(“Connection failed: ” . $conn->connect_error);
// Import all CSV files in a directory into MySQL
$dir_path = “csv_files/”; // Path to directory containing CSV files
if (is_dir($dir_path))
foreach (scandir($dir_path) as $csv_file)
if (strpos($csv_file, ‘.csv’) !== false)
$handle = fopen($dir_path.$csv_file, “r”);
if ($handle !== FALSE)
while (($data = fgetcsv($handle, 1000, “,”)) !== FALSE)
foreach ($data as &$value)
// Clean up the data by removing unwanted characters and whitespace
$value = preg_replace(‘/[^A-Za-z0-9\s\-\(\)\,\/\.]/’, ”, $value);
$value = trim($value);
$value = mysqli_real_escape_string($conn, $value);
$sql = “INSERT INTO mytable (column1, column2, column3) VALUES (‘”.$data[0].”‘, ‘”.$data[1].”‘, ‘”.$data[2].”‘)”;
$conn->query($sql);
fclose($handle);
// Close connection
$conn->close();
?>
“`
In the example above, we first define the same MySQL server address, username, password, and database name as before. We then create a connection to the MySQL server. If the connection fails, an error message is displayed. Next, we scan a directory containing CSV files and import each file into MySQL. We clean up the data by removing unwanted characters and whitespace, and then validate the data using mysqli_real_escape_string. Finally, we loop through each row in the CSV file and insert the data into the MySQL table using a SQL INSERT statement. In this example, we’re assuming the CSV file has three columns. The SQL statement inserts the values of each column into three corresponding columns in the MySQL table. Finally, we close the connection to the MySQL server.
FAQ – Frequently Asked Questions
Q: Can I import data from multiple sheets in an Excel file?
A: Yes, you can import data from multiple sheets in an Excel file by saving each sheet as a separate CSV file. Then, you can import each CSV file into MySQL using the PHP code provided in this article.
Q: What is SQL injection attack and how can I prevent it?
A: SQL injection is a type of security exploit in which an attacker injects malicious SQL code into a web form input or URL query string to gain access to a database or execute unauthorized SQL commands. To prevent SQL injection, developers should use prepared statements or real escape strings to sanitize user input and validate form fields. Prepared statements provide a convenient way to execute SQL commands with parameters that can be safely replaced by user input. Real escape strings sanitize user input by escaping special SQL characters that could be used to execute malicious SQL commands.
Conclusion
Importing data from Excel spreadsheets into MySQL using PHP is a straightforward task that can be accomplished using CSV files and PHP’s MySQLi extension API. With just a few lines of code, developers can import data from multiple sheets, tables, and columns in Excel files and format them as rows in a MySQL database. By following the steps outlined in this article, you can easily import and manage data from Excel files in your MySQL database.
