import a txt file into mysql

Question

need a little help, someone knows how to import a txt file into mysql in such a way

IP: data city: data state: data Latitude: data Longitude: data IP: data city: data state: data Latitude: data Longitude: data

are more than 10000 lines in this way

the name is fulldata-Day-q-was-generated.csv This file is generated in csv but can be txt. I need a script that generates a file . sql with mysql Inserts in bash, can anyone help me?

I’ve tried bash with a script but it doesn’t generate with the right format for Insert and it’s not connecting to mysql database.

The . sql file must be in this format:

INSERT INTO nometabela (id, host, country, state, city, latitude, longitude) VALUES (92,'46.105.108.104','France','None','None','488.582','23.387');

I tried the script:

#!/bin/bash

$1 = file with data already formatted

A '.SQL file' will be generated with the SQL commands

IFS=:

while read ip country city state longitude;do echo "INSERT INTO table name VALUES('$ip','$country','$state','$city', '$latitude', '$longitude');" >> .SQL file done < $1

But returned me error, the generated file brings me only the longitude field, which can be?

Answer 1

It would take a few lines of your txt file to check, but you can use a script like:

#!/bin/bash

while IFS= read -r line
do
        ip=$(echo "$line"|cut -f 1 -d ':')
        country=$(echo "$line"|cut -f 2 -d ':')
        state=$(echo "$line"|cut -f 3 -d ':')
        city=$(echo "$line"|cut -f 4 -d ':')
        latitude=$(echo "$line"|cut -f 5 -d ':')
        longitude=$(echo "$line"|cut -f 6 -d ':')
        echo "INSERT INTO nometabela (id, host, country, state, city, latitude, longitude)  VALUES ('$ip','$country','$state','$city', '$latitude', '$longitude');" >> arquivo.SQL
done < $1

source file:

192.168.1.1: 'france' : 'fdfsd' : '1.1.1' : '2.2.2.2'
192.168.6.1: 'xxxfrance' : 'fzzzsd' : '1.21.1' : '2.222.2.2'
192.168.1.1: 'yyyfrance' : 'fdjjjfsd' : '1.51.1' : '2.2.32.2'