How to Import Data from a Text File into a MySQL Database Using a Bash Script?

How to Insert Values from a Text File into a MySQL Database Using a Bash Script

This guide demonstrates how to construct a Bash script that establishes a connection with your MySQL server and inserts values stored in a text file into a database named 'test'.

Step 1: Creating the Shell Script

In your preferred text editor, create a new file and include the following code:

#!/bin/bash
inputfile="test.txt"
cat $inputfile | while read ip mac server; do
    echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('$ip', '$mac', '$server');"
done | mysql -uroot -ptest test;

Step 2: Understanding the Script

• inputfile="test.txt": This line specifies the name of the text file containing the data to be inserted.
• cat $inputfile | while read ip mac server; do: This command reads each line of the text file and assigns the fields to the variables ip, mac, and server.
• echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('$ip', '$mac', '$server');": This line constructs the SQL statement for inserting the values into the 'test' table.
• done | mysql -uroot -ptest test;: This command executes the SQL statement and inserts the values into the 'test' database.

Troubleshooting: Column Count Mismatch Error

If you encounter an error stating "Column count doesn't match value count," it likely means that the number of columns specified in the SQL statement does not match the number of values in the text file. Ensure that the following conditions are met:

• Each line in the text file contains three values separated by spaces (e.g., 10.16.54.29 00:f8:e5:33:22:3f marsara).
• The test table in the MySQL database has three columns named IP, MAC, and SERVER.

By following these steps, you can create a Bash script that successfully inserts values from a text file into a MySQL database.

The above is the detailed content of How to Import Data from a Text File into a MySQL Database Using a Bash Script?. For more information, please follow other related articles on the PHP Chinese website!