How can I append comma except lastline?

Question

With following shell script,

• I read file
• and for each line
• generates SQL fragments

#!/bin/bash
CURRDATE=$(date +'%Y-%m-%d %H:%M:%S')
echo $CURRDATE
echo $OUTPUT

echo "SET SESSION AUTOCOMMIT = 0;" > $OUTPUT
echo 'START TRANSACTION;' >> $OUTPUT

echo $'INSERT INTO ...' >> $OUTPUT
echo 'VALUES' >> $OUTPUT
cat $INPUT | tr -d '\r' | while IFS= read -r LINE; do
echo $"(..., '$LINE', ...)," >> $OUTPUT
done

echo "COMMIT;" >> $OUTPUT

The output look like this,

SET SESSION AUTOCOMMIT = 0;
START TRANSACTION;
SET @created_at = '2024-08-09 09:30:45';
INSERT INTO ...
VALUES
(...),
(...),
(...), -- <<<<<<<<<<<<<<<< should be a semi-colon
COMMIT;

How can suppress the last comma? So that I put semi-colon?

Answer 1

This is something that's pretty simple to do with awk once you wend your way to the how of it. I use this to "sqlify" any list of items into an SQL-friendly list. You can adapt the BEGIN and END sections to add the extra statements and other preamble that you need before (and after) your list of values:

BEGIN {
  printf "( "
}

NF > 0 {
  gsub( /'/, "''", $0 )
  printf( "%s'%s'", comma, $0 )
}

NR==1 {
  comma=","
}

END {
  printf( " )\n" )
}

Example usage:

$ seq 3 | awk -f sqlify.awk
( '1','2','3' )

The logic is:

• Print whatever we need before the comma-separated list, and set the "comma placeholder" to an empty string.
• For each value:
• Print the comma-placeholder, followed by the "SQLified" value
• If this is the first value we just printed, set the comma-placeholer to a ,
• Done with the list of values? Cool, print whatever we need after the list.

The upshot of this is, because we're printing the commas on the lines following each value, no comma is printed for the last value.

In my example, I take in a list of values and output a quoted, comma-separated list. If you don't need to add quotes, alter the printf line accordingly. (The gsub line will correctly escape any strong quotes in your values so that the SQL will do what is intended/expected)

Answer 2

Perl equivalent for DopeGhoti's sqlifier:

perl -l -0777 -F'\n' -e "
    print('( ' .
        join(
            ', ',
            map {
                q(') . s/'/''/gr . q(')
            } @F)
    . ' )')
"

One-liner version:

perl -l -0777 -F'\n' -e "print('( ' . join(', ', map { q(') . s/'/''/gr . q(') } @F) . ' )')"

• -l: autochomp / autoappend the input record separator (\n)
• -0777: read the whole file / STDIN at once
• -F'\n': set the input field separator to a newline character and autosplit the whole file / STDIN on the input field separator; turn autolooping on
• -e "[...]": for each field, replace single quotes with double single quotes, wrapping the resulting string in single quotes; join all fields using , as a separator and print the resulting string, wrapped in parenthesis

Note: In older versions of Perl, you may need to add the -a and -n switches (to turn on autosplitting and autolooping); in Perl 5.20.0 or newer, both are implied by -F.

% printf "Foo\nBar\nString with spaces and 'single quotes'\n" | perl -l -0777 -F'\n' -e "print('( ' . join(', ', map { q(') . s/'/''/gr . q(') } @F) . ' )')"
( 'Foo', 'Bar', 'String with spaces and ''single quotes''' )

Answer 3

sed

sed 's/\r$//; $!s/$/,/' "$INPUT"

1. s/\r$// removes the carriage return from the end of line
2. $!s/$/,/ on the last line ($) NOT (!), replace end of line with a comma.

Answer 4

I believe this will do what you want:

CURRDATE=$(date +'%Y-%m-%d %H:%M:%S')
echo $CURRDATE
echo $OUTPUT

echo "SET SESSION AUTOCOMMIT = 0;" > $OUTPUT
echo 'START TRANSACTION;' >> $OUTPUT

echo $'INSERT INTO ...' >> $OUTPUT
echo -n 'VALUES' >> $OUTPUT
comma=
cat $INPUT | tr -d '\r' | while IFS= read -r LINE; do
    echo $comma                    >> $OUTPUT
    echo -n $"(..., '$LINE', ...)" >> $OUTPUT
    comma=,
done
echo ';' >> $OUTPUT

echo "COMMIT;" >> $OUTPUT

The -n for the echo says don't include a newline after the output.