2

Using -line Option I get this output from SQLite:

row1_column1 = valueA
row1_column2 = valueB

row2_column1 = valueC
row2_column2 = valueD

So one line for each column value and result rows are separated by a blank line. I need to get this output in a array or list containing

valueA,valueB
valueC,valueD

Additionally non-numeric (!) values shall be enclosed by ' and '. Any simple way to do this? Thanks for any hint!

3

The following awk script does what you want, and even escapes quotes in strings:

BEGIN   { RS="\n\n"
          FS="\n"
        }

function field(s)
{
        sub(/^[^=]*= /, "", s)
        if (match(s, /^[-][0-9]+$/) == 0) {
                gsub(/'/, "''", s)
                s = "'" s "'"
        }
        return s
}

        { print field($1) "," field($2) }

(The numeric regular expression does not handle floating-point values.)


But the simplest way to do this does not use -line but SQLite's built-in SQL quoting:

sqlite3 -separator , -list my.db 'select quote(col1),quote(col2) from mytab;'
  • the quote function is exactly what i was looking for, i had found the list option, but didn't know there is an sql statement for quoting. thank you very much! – stefan.at.wpf Sep 9 '12 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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