Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

hope someone can help me! I do not know what's wrong with this script (Bash / Ubuntu 12.04 / Mysql / XAMPP 1.8.3). Try to read an input file with a list of csv-filenames (449-R11, 449-R12 ..) in order to do some sql-statements....

    #!/bin/bash

     OUTPath="/home/bono/RD/BV-OUT/tmp/distinct_"
     OUTPath1="/home/bono/RD/BV-OUT/tmp/load_"
     INPath="/home/bono/RD/BV-OUT/"

     while read line do
     RD="$line"
     bv_out_distinct=$OUTPath$RD
     bv_out_load_new_bvs=$OUTPath1$RD
     RDDATEI=$INPath$RD

     ###MYSQLs:
     /opt/lampp/bin/mysql --local-infile --user=root RD << EOF 
     LOAD DATA LOCAL INFILE $RDDATEI 
       INTO TABLE bv_tmp_all FIELDS TERMINATED BY ';';
     SELECT * FROM bv_tmp_all GROUP BY BAN 
       INTO OUTFILE $bv_out_distinct FIELDS TERMINATED BY ';' ;
     LOAD DATA LOCAL INFILE $bv_out_distinct 
       INTO TABLE bv_tmp_distinct FIELDS TERMINATED BY ';';
     UPDATE bv, bv_tmp_distinct 
       SET bv.RD=bv_tmp_distinct.RD,bv.LEV21=bv_tmp_distinct.LEV21 
       WHERE bv.BAN=bv_tmp_distinct.BAN;
     SELECT * FROM bv_tmp_distinct 
       WHERE BAN NOT IN (SELECT BAN FROM bv) 
       INTO OUTFILE $bv_out_load_new_bvs FIELDS TERMINATED BY ';';
     LOAD DATA LOCAL INFILE $bv_out_load_new_bvs 
       INTO TABLE bv FIELDS TERMINATED BY ';';
     TRUNCATE TABLE bv_tmp_distinct;
     TRUNCATE TABLE bv_tmp_all;
     EOF
     ##### EOSQL
     done < /home/bono/RD/BV-OUT/allto468

I tried different versions :( , nothing worked.... I get this mysql-output:

bono@bono-S3210SH:~/RD/procs$ ./load_RD_out_tmp

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; 
  check the manual that corresponds to your MyS INTO TABLE bv_tmp_all FIEL' 
  at line 1 to use near '"LOAD DATA LOCAL INFILE /home/bono/RD/BV-OUT/449-R11
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; 
  check the manual that corresponds to your MyS INTO TABLE bv_tmp_all FIEL' 
  at line 1 to use near '"LOAD DATA LOCAL INFILE /home/bono/RD/BV-OUT/449-R12
share|improve this question

1 Answer 1

Your file names should be escaped with quotes - MySQL considers them strings, so your MySQL block should be:

LOAD DATA LOCAL INFILE '$RDDATEI' 
  INTO TABLE bv_tmp_all FIELDS TERMINATED BY ';';
SELECT * FROM bv_tmp_all GROUP BY BAN 
  INTO OUTFILE '$bv_out_distinct' FIELDS TERMINATED BY ';';
LOAD DATA LOCAL INFILE '$bv_out_distinct'
  INTO TABLE bv_tmp_distinct FIELDS TERMINATED BY ';';
UPDATE bv, bv_tmp_distinct 
  SET bv.RD=bv_tmp_distinct.RD,bv.LEV21=bv_tmp_distinct.LEV21 
  WHERE bv.BAN=bv_tmp_distinct.BAN;
SELECT * FROM bv_tmp_distinct 
  WHERE BAN NOT IN (SELECT BAN FROM bv) 
  INTO OUTFILE '$bv_out_load_new_bvs' FIELDS TERMINATED BY ';';
LOAD DATA LOCAL INFILE '$bv_out_load_new_bvs' 
  INTO TABLE bv FIELDS TERMINATED BY ';';
TRUNCATE TABLE bv_tmp_distinct;
TRUNCATE TABLE bv_tmp_all;
share|improve this answer
    
Thanks Rudu. But pity it does not work with the quotes. I get the message ' not found (Errcode: 2 - File or Directory not found). But when I put the content of the var into the SQLs directly it works. The files exist.... :( –  Bono Feb 7 at 16:19
    
Now I tried this (but it does not work either): #!/bin/bash while read line do RD="$line" RDDATEI="/home/bono/RD/BV-OUT/" RDDATEI+=$RD echo -e $RDDATEI ###MYSQLs: mysql --local-infile -u root -D RD << EOF LOAD DATA LOCAL INFILE '$RDDATEI' INTO TABLE bv_tmp_all FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n'; EOF ##### EOSQL done < /home/bono/RD/BV-OUT/allto468 –  Bono Feb 7 at 16:38
    
The quotes were one problem. But it seems the var concat is an other!!! RDDATEI=$INPath$RD ---It seems that something is added you cannot see? If I type the full path and filename like RDDATEI="/home/bono/RD/BV-OUT/449-R11" --> works! –  Bono Feb 7 at 16:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.