2

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?

Improve this question
6
  • 5
    What database engine are you using? What is the format of the input file? If it's CSV, can't you just import the data straight into the table instead of creating SQL to do it? This would also deal with the issues you would otherwise have if any field contains single quotes. Commented Aug 9, 2024 at 5:22
  • I'm also noticing that your scrip definitely does not produce the output that you say it does. Ut would be good if you could ensure that the provided code corresponds with the given output. Commented Aug 9, 2024 at 7:30
  • It's a comma because your outputline echo $"(..., '$LINE', ...)," >> $OUTPUT makes it a comma. Commented Aug 9, 2024 at 13:59
  • @Wastrel I know, right? Commented Aug 9, 2024 at 14:34
  • @JinKwon I don't know much about SQL, but I guess you want only the last line of output to have a semicolon instead of a comma, not all of them. That was my misunderstanding. I wouldn't input the file with cat and read in the first place. I'd make an array, from which I could find the last line and treat it differently. As others suggest, a shell script might not be the best tool for this, but it can be done. Commented Aug 9, 2024 at 15:14

4 Answers 4

Reset to default
7

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)

Improve this answer
1
  • 1
    An unintentional quirk since I just copied the script I use day-in and day-out and I know I am providing one item per line (with no whitespace) to myself. This is a good catch though for general distribution. Commented Aug 9, 2024 at 6:17
5

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''' )
Improve this answer
0
5 

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.
Improve this answer
2

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.

Improve this answer
3
  • 4
    Note that $'...' is quite different from $"..." in the bash shell. In this code, neither of these types of strings seems to be useful. Commented Aug 9, 2024 at 5:17
  • Also, this would replace any embedded whitespace with single spaces, would perform filename globbing with any data that looks like a globbing pattern, and would break if the data contains single quotes. Commented Aug 9, 2024 at 7:38
  • @Kusalananda, yeah, I was a bit pained by the $" and $'. I tried to keep the original syntax since the OP was not asking about quotes. I know $'...\n' can give you the actual new line, etc as requested but I've never been clear on $"...". It seems like that is for international character sets or something but I never spent the time to find out. Commented Aug 10, 2024 at 0:40

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.