CODEWITHSUNDEEP
mysqlshell
Nisha
Nisha

Reputation: 158

How to insert content of a file in different fields in mysql database using shell script?

I am trying to scan a folder for new files and reading those files and inserting its content into database and then delete file from folder.Till here its working but the issue that the whole content is getting inserted in one field in database. Below is the code:

   inotifywait -m /home/a/b/c -e create -e moved_to |
   while read path action file; do
     for filename in `ls -1 /home/a/b/c/*.txt`  
     do
        while read line
        do
           echo $filename $line
           mysql -uroot -p -Bse "use datatable; INSERT INTO 
           table_entries (file,data ) VALUES ('$filename','$line'); "
           done <$filename     
           done
       find /home/a/b/c -type f -name "*.txt" -delete
        done

Basically the files contains:name,address,contact_no,email. I want to insert name from file to name field in database,address in address. In php we use explode to split data,what do i use in shell script ?

Upvotes: 1

Views: 1144

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

This would be far easier if you use LOAD DATA INFILE (see the manual for full explanation of syntax and options).

Something like this (though I have not tested it):

inotifywait -m /home/a/b/c -e create -e moved_to |
while read path action file; do
  for filename in `ls -1 /home/a/b/c/*.txt`  
  do
     mysql datatable -e "LOAD DATA LOCAL INFILE '$filename' 
       INTO TABLE table_entries (name, address, contact_no, email) 
       SET file='$filename'"
  done
  find /home/a/b/c -type f -name "*.txt" -delete
done

edit: I specified mysql datatable which is like using USE datatable; to set the default database. This should resolve the error about "no database selected."

The columns you list as (name, address, contact_no, email) name the columns in the table, and they must match the columns in the input file. If you have another column in your table that you want to set, but not from data in the input file, you use the extra clause SET file='$filename'.

You should also use some error checking to make sure the import was successful before you delete your *.txt files.

Note that LOAD DATA INFILE assumes lines end in newline (\n), and fields are separated by tab (\t). If your text file uses commas or some other separator, you can add syntax to the LOAD DATA INFILE statement to customize how it reads your file. The documentation shows how to do this, with many examples: https://dev.mysql.com/doc/refman/5.7/en/load-data.html I recommend you spend some time and read it. It's really not very long.

Upvotes: 1

Related Questions