Shell script to append single quotes and commas

Question

I have a file named input.txt:

$cat input.txt
This is sample
Input file
To execute sql statement

I need output like below assigned to a variable:

X=('This is sample', 'Input file', 'To execute sql statement')

So that I can use the above string X as input to the SQL query in the IN condition.

select * from table where columnname in X

Please help me here.

Answer 1

With bash:

mapfile -t a < input.txt      # read input file into array a
printf -v x "'%s'," "${a[@]}" # add single quotes and commas, save result in variable x
printf -v query 'select * from table where columnname in (%s);' "${x:0:-1}" # strip the last comma from x
printf '%s\n' "$query"        # print query

Output:

select * from table where columnname in ('This is sample','Input file','To execute sql statement');

Answer 2

Split file up line by line into array:

# Tell bash that "lines" is an array
declare -a lines

# Read each line of the file
while read line; do
    lines+=("$line")
done <input.txt

Print results:

# Show the first line
echo "${lines[0]}"

# Show all lines
echo "${lines[@]}"

Keep in mind that most database CLI tools let you run files as input. Example for postgres: psql -f input.txt

Answer 3

One Liner:

IFS=$'\n'; (read -r L && echo -n "X=('${L//\'/\"}'"; while read -r L; do echo -n ",'${L//\'/\"}'"; done; echo ")";) < input.txt; IFS=' '

(all single quotes symbol ' inside replaced by double quote symbol ").

Output:

X=('This is sample','Input file','To execute sql statement')

Or to assign to a variable:

$ IFS=$'\n'; X=$( (read -r L && echo -n "('${L//\'/\"}'"; while read -r L; do echo -n ",'${L//\'/\"}'"; done; echo ")";) < input.txt); IFS=' '; 
$ echo $X
('This is sample','Input file','To execute sql statement')

Updated. First one liner explanation:

#
# redefining IFS to read line by line ignoring spaces and tabs
# you can read more about at
# https://unix.stackexchange.com/questions/184863/what-is-the-meaning-of-ifs-n-in-bash-scripting
#
IFS=$'\n';
#
# next actions united inside of round brackets
# to read data from input.txt file
#
# first line from the file read separately,
# because should be printed with opening brackets
#
# between read and echo we use && to make sure
# that data are printed only if reading succeeded
# (that means that the file is not empty and we have reading permissions)
#
# also we print not just "X=($L
# but variable modified in the way to replace ' with "
# more details about it you can find at
# https://devhints.io/bash in Substitution section
# also quotes symbols inside of quoted text are backslash'ed
#
(read -r L && echo -n "X=('${L//\'/\"}'";
#
# now we read lines one by one as long as reading returns data
# (which is until we reach end of file)
# and each time printing: ,'LINE-DATA'
# I mean coma and data in single quotes like requested
# where the data are the lines where single quotes replaced
# with double quotes, the same as we did for the first line
while read -r L; do
    echo -n ",'${L//\'/\"}'";
done;
#
# finally we print closing brackets for the data from file
#
# also we close round brackets uniting actions to read data from file
# and specifying file name from where we read data
echo ")";) < input.txt; 
#
# at the end we change IFS back to original.
# actually for 100% accuracy it should be IFS=$' \t\n'
IFS=' '

Answer 4

Using portable shell features only:

oIFS=$IFS                         # Store the IFS for later;
IFS=                              # Empty the IFS, to ensure blanks at the
                                  # beginning/end of lines are preserved
while read -r line; do            # Read the file line by line in a loop,
    set -- "$@" "'""$line""'"     # adding each line to the array of positional
done < input                      # parameters, flanked by single quotes
IFS=,                             # Set "," as the first character of IFS, which
                                  # is used to separate elements of the array
                                  # of positional parameters when expanded as
                                  # "$*" (within double quotes)
X=\("$*"\)                        # Add parentheses while assigning to "X"
IFS=$oIFS                         # Restore the IFS - to avoid polluting later
                                  # commands' environment

Note that using shell loops for processing text is problematic, but it may make sense here because we want the file content to end up in a shell variable.

The output:

$ printf "%s %s\n" 'select * from table where columnname in' "$X"
select * from table where columnname in ('This is sample','Input file','To execute sql statement')

Alternatively, you may process your input file with a tool like sed. This script adds flanking single quotes to each input line, appends all the lines to the hold space and, once the last line has been appended, puts the content of the hold space into the pattern space, replaces any <newline> with a comma, adds parentheses at the beginning/end of the text and prints the whole thing:

X=$(sed -n '
  s/^/'"'"'/
  s/$/'"'"'/
  H
  1h
  ${
    g
    s/\n/,/g
    s/^/(/
    s/$/)/
    p
  }
  ' input)

Of course, these approaches don't take care of single quotes that may appear in your input lines, which will eventually make for a broken SQL statement or, at least, one that won't yield the expected result.