Hi Bruce, On 27 November 2015 at 10:59, Bruce Hohl <brucehohl at gmail.com> wrote:
> Thanks to suggestions on this list I explored the sed post processing > avenue and found a solution. There are quite a few sed docs at > http://sed.sourceforge.net For my needs I adapted an example from > http://www-rohan.sdsu.edu/doc/sed.html (These docs are like ancient > scrolls.) > > # add commas to interger strings, changing "1234567" to "1,234,567" > gsed ':a;s/\B[0-9]\{3\}\>/,&/;ta' > > # add commas to numbers with decimal points and minus signs > gsed ':a;s/\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\1\2,\3/g;ta' > > I adjusted the 2nd as follows to remove a space for each comma added which > preserves column layout: > sed ':a;s/\( \)\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\2\3,\4/g;ta' > This modification is the reason it doesn't match numbers in column 1. But also it will only insert as many commas as it has spaces to eat. eg. if the number 1234567.89012 appears four times with zero, one, two, and then three leading spaces, we get: 1234567.89012 1234567.89012 1234,567.89012 1,234,567.89012 You can improve it by moving the leading space inside the second group and making it optional, so that it eats spaces when it can but still inserts commas when it can't: sed ':a;s/\(^\| \?\([^0-9.]\)\)\([0-9]\+\)\([0-9]\{3\}\)/\2\3,\4/g;ta'; The output becomes: 1,234,567.89012 1,234,567.89012 1,234,567.89012 1,234,567.89012 The date problem is harder to solve, especially with sed. lex makes it feasible - stick the following into a file called thousandify.lex and compile it with the command: flex thousandify.lex && gcc lex.yy.c -o thousandify Then put the resulting 'thousandify' binary in your path and you can run sqlite3 test.db | thousandify /* START thousandify.lex */ %option noyywrap DATE " "*[0-9]{4}-[0-9]{2}-[0-9]{2} INTEGER " "*[0-9]+ FRACTION \.[0-9]*([eE][+-]?[0-9]*)? %% {DATE} printf("%s", yytext); {FRACTION} printf("%s", yytext); {INTEGER} { char *cp; int i, r, n, len; for (cp=yytext; *cp == ' '; ++cp); // skip leading whitespace len = strlen(cp); r = len % 3; n = (len - 1) / 3; // number of commas we'll insert for (i = 0; i < (cp - yytext) - n; i++) { putchar(' '); } putchar(cp[0]); for (i = 1; i < len; i++) { if (--r == 0) { putchar(','); r = 3; } putchar(cp[i]); } } . printf("%s", yytext); %% int main(int argc, char **argv) { yylex(); return 0; } /* END thousandify.lex */ -Rowan