CODEWITHSUNDEEP
mysqlperlbashawk
Deano
Deano

Reputation: 12200

bash how to print for loop output into one line?

I wish if someone can help me with this,

how can I print the output of for loop into single line? 

for i in `cat file.csv`
do
echo $i 
done

what I'm trying to achieve here is to get a list of numbers from file.csv to generate a mysql bulk delete statement. 

delete FROM Recordtable WHERE DataID IN ('93041', '93031' ...etc);

and the goal here is to load every 1000 records into one single delete statement

Your help is highly appreciated

Upvotes: 3

Views: 6133

Answers (6)

hobbs
hobbs

Reputation: 239990

From the "just because" school of answering:

tr $'\n' ' ' < file.csv

Upvotes: 1

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

BULK_DELETE_SQL=/tmp/bulkdelete.sql
rm -f ${BULK_DELETE_SQL}
COMMIT_COUNT=0
COMMIT_LIMIT=1000
NUMLIST=""
COMMA=""
for NUM in `cat file.csv` 
do
    NUMLIST="${NUMLIST}${COMMA}${NUM}"
    COMMA=","
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        SQLSTMT="delete FROM Recordtable WHERE DataID IN (${NUMLIST});"
        echo ${SQLSTMT} >> ${BULK_DELETE_SQL}
        NUMLIST=""
        COMMA=""
        COMMIT_COUNT=0
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    SQLSTMT="delete FROM Recordtable WHERE DataID IN (${NUMLIST});"
    echo ${SQLSTMT} >> ${BULK_DELETE_SQL}
fi
mysql -u... -p... < ${BULK_DELETE_SQL}
rm -f ${BULK_DELETE_SQL}

This will write one SQL script that will create 1000 DataIDs per DELETE Statement.

It will only connect to mysql once and perform all the DELETEs, 1000 DataIDs at a time.

If there are 6247 DataIDs in file.csv, then the last line should have 247 DataIDs

If there are 247 DataIDs in file.csv, then the only line should have 247 DataIDs

Give it a Try !!!

Upvotes: 1

TLP
TLP

Reputation: 67918

You can use perl. 

perl -lne '$a .= $_ }{ print $a' file.csv

-l will remove newlines, and will add a newline to the print. -n will read argument file contents. Be aware that if no added whitespace exists in the file, you may with to add it, e.g. $a .= " $_".

I assume you are going to pipe this input somewhere to perform the SQL query. I can't help but think this could be better done with DBI.

Upvotes: 1

Adam Liss
Adam Liss

Reputation: 48310

If you want the loop, you can use

for i in ($<file.csv)
do
  echo -n "$i "
done

The -n option to echo suppresses the newline.

You can print the entire file in a single line with echo $(<file.csv).

But this may suit your needs better:

awk '{ printf $0 " " } NR%1000 == 0 { print "" }' file

This will print each line followed by a space. It will print a newline whenever the line number is divisible by 1000; that is, after every 1000th line.

Added: To print each line within parentheses, you can use

awk '{ printf $0 " " } NR%1000 == 0 { print "" }' file | sed 's/.*/(&)/'

The sed command searches for any characters (.*) and replaces them with an open-paren, the characters it found (that is, the entire line), and a close-paren.

Upvotes: 5

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57660

It seems file.csv contains DataID in a single column. If thats true, you can generate the query like this,

echo delete FROM Recordtable WHERE DataID IN \(0$(cat file.csv | tr -cs '[:digit:]' ',')0\)\;

As the file contains only integers (DataID seems int) so there is no need to enclose it by single quote.

$ cat data
123
241114
43243
35745
656
346   456


$echo delete FROM Recordtable WHERE DataID IN \(0$(cat data | tr -cs '[:digit:]' ',')0\)\;
delete FROM Recordtable WHERE DataID IN (0,123,241114,43243,35745,656,346,456,0);

Here 0 deals with leading and trailing ,s.

Upvotes: 2

Gilles Qu&#233;not
Gilles Qu&#233;not

Reputation: 185219

You can do :

echo $(cat file.csv)

or 

cat file.csv | xargs

or finally :

$ cat /tmp/l.csv
1;2;3;4;5;6
7;8;9;10;11
$ echo $(< /tmp/l.csv)
1;2;3;4;5;6 7;8;9;10;11
$ echo $(cat /tmp/l.csv) | perl -pe 's@(;|\n)@,@g'
1,2,3,4,5,6,7,8,9,10,11,
$

Upvotes: 1

Related Questions