Tags

,

Follwoing BASH script will loop trough all the files and import them to mysql database.

#!/bin/bash

# show commands being executed, per debug
#set -x

# define database connectivity
_db="db_name"
_db_user="user_name"
_db_password="pass_word"

# define directory containing CSV files
_csv_directory="/home/fakrul/cymru_log"

# go into directory
cd $_csv_directory

# get a list of CSV files in directory
_csv_files=`ls -1 *.txt`

# loop through csv files
for _csv_file in ${_csv_files[@]}
do
echo $_csv_file

mysql -u $_db_user -p$_db_password -h localhost -D $_db --local-infile <<QUERY_INPUT
LOAD DATA local INFILE '$_csv_file'
INTO TABLE asn_data
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 2 LINES
(report, ip, asn, time, comments, asn_name)
SET slno = NULL;

INSERT INTO file_name VALUES ('$_csv_file')
QUERY_INPUT
done