CODEWITHSUNDEEP
bashsedsqlite
lollan
lollan

Reputation: 81

Create sqlite3 database from multi-line sed output

I am running this:

sed -ne '/###SECTION-1###/,/###SECTION-2###/p file

And it outputs this:

###SECTION-1###

ColumnA: 1a
ColumnB: 2b
ColumnC: 3c
ColumnA: Z9
ColumnB: Y8
ColumnC: X7

###SECTION-2###

I want to create a sqlite database using the above output so that it looks like this:

ColumnA  ColumnB  ColumnC
-------  -------  -------
1a       2b       3c
Z9       Y8       X7

I figure the sqlite insert command will look something like the below, but I am having trouble assigning $columna, $columnb, $columnc from the original sed command.

sqlite3 /tmp/test.db "INSERT OR IGNORE INTO data (ColumnA, ColumnB, ColumnC) values ('$columna', '$columnb', '$columnc');"

How do I assign $columna, $columnb, $columnc ?

Upvotes: 0

Views: 442

Answers (2)

Mark Setchell
Mark Setchell

Reputation: 208003

I would do that with awk ,it is far easier to read (and write) than sed IMHO:

awk '/^ColumnA:/{a=$2}
     /^ColumnB:/{b=$2}
     /^ColumnC:/{c=$2; printf("sqlite2 /tmp/test/db \"INSERT (ColumnA,ColumnB,ColumnC) values ('\''%s'\'','\''%s'\'','\''%s'\'');\"\n",a,b,c)}' file.txt

So, when we see a line starting with "ColumnA:", we save the second field as a. When we see a line starting with "ColumnB:", we save the second field as b. When we see a line starting with "ColumnC:", we save the second field as c and print the SQL stuff (which I have abbreviated for clarity.

Sample Output

sqlite2 /tmp/test/db "INSERT (ColumnA,ColumnB,ColumnC) values ('1a','2b','3c');"
sqlite2 /tmp/test/db "INSERT (ColumnA,ColumnB,ColumnC) values ('Z9','Y8','X7');"

Or as a one-liner:

awk '/^ColumnA:/{a=$2} /^ColumnB:/{b=$2} /^ColumnC:/{c=$2; printf("sqlite2 /tmp/test/db \"INSERT (ColumnA,ColumnB,ColumnC) values ('\''%s'\'','\''%s'\'','\''%s'\'');\"\n",a,b,c)}' file.txt

If that looks correct, you can send the output to bash for actual execution:

awk '{...}' file.txt | bash

Upvotes: 1

CL.
CL.

Reputation: 180260

You did not say you wanted to use sed for further processing, and multiline replacements are not easy, but it's possible.

First, remove the SECTION lines. Then replace the ColumnX: texts with the appropriate parts of the SQL statement:

sed -ze "s/ColumnA: /INSERT OR IGNORE INTO data(ColumnA, ColumnB, ColumnC) VALUES('/g;s/\nColumnB: /', '/g;s/\nColumnC: \([^\n]*\)\n/', '\1');\n/g"

Upvotes: 1

Related Questions