import a txt file into mysql

Asked

Viewed 103 times

0

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?

  • This link should help you: https://stackoverflow.com/questions/13579810/how-to-import-datafrom-text-file-to-mysql-database

1 answer

0

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'
  • The problem with my file is that it is in several lines for the same record and then repeats the fields. Example: IP: dads1 Country: dads1 State: dads1 City: dads1 Latitude:dads1 Longitude:dads1 IP:dads2 Country:dads2 State:dados2 City:dados2 Latitude:dados2 Longitude:dados2

  • Would need some sample lines of the file and however these lines remain after converted

  • IP: 103.145.12.23 Country: Netherlands State: City: Latitude: 52.3824 Longitude: 4.8995 IP: 103.145.13.124 Country: Netherlands State: City: Latitude: 52.3824 Longitude: 4.8995 IP: 103.145.13.192 Country: Netherlands State: City: Latitude: 52.3824 Longitude: 4.8995

  • My file is in the list and each field is on a different line, as I can put the fields on the same line?

  • uses tr e sed: cat file | tr -d ' n' | sed’s/IP:/ n IP:/g' You’ll be able to organize the file in lines. It’s easier to manipulate

Browser other questions tagged

You are not signed in. Login or sign up in order to post.