Parsing SQLite output

Question

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!

Answer 1

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;' 

Answer 2

It appears you simply want to enable CSV output from SQLite:

sqlite> .schema CREATE TABLE mytable ( field1 text, field2 integer ); 

sqlite> .mode box sqlite> SELECT * FROM mytable; ┌───────────┬────────┐ │ field1 │ field2 │ ├───────────┼────────┤ │ some text │ 1 │ │ more text │ 2 │ └───────────┴────────┘ 

sqlite> .mode line sqlite> SELECT * FROM mytable; field1 = some text field2 = 1 field1 = more text field2 = 2 

sqlite> .mode csv sqlite> SELECT * FROM mytable; "some text",1 "more text",2 

From the command line:

$ sqlite3 mydatabase.db '.mode csv' 'SELECT * FROM mytable' "some text",1 "more text",2 

CSV output mode can also be set with the -csv command line option to the sqlite3 command.