Home > Database > Mysql Tutorial > How to Import Data from a Text File into a MySQL Database Using a Bash Script?

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

DDD
Release: 2024-12-19 05:41:14
Original
310 people have browsed it

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:

1

2

3

4

5

#!/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;

Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template