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

• me.at.coding: 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!

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.