Format SQLite Output So Columns Are Evenly Spaced and Padded

[cdthomer]

Howdy folks,

I would like to see if there is a way to format the output of an SQLite query so the columns are evenly spaced and padded. The length of each entry in each column can vary, so some columns would need some whitespace added (either at the beginning or end).

I would prefer formatting the results after the fact using bash, RegEx or something similar as opposed to modifying the SQL query because there are actually multiple queries that run consecutively to build this list.

Below is a sample of the default output without any adjustments, and please let me know if you need any more information.

Thanks in advance for any help!

-Chris

SQL query unformatted output (click to expand/collapse)

1|Wednesday, January 25, 2023|23713304|08:04:39|08:19:03|CMS|0|14|4.42|
2|Wednesday, January 25, 2023|23713501|08:34:30|08:58:49|SF DPH|0|24|8.00|
3|Wednesday, January 25, 2023|23713831|09:02:07|09:33:50|ERO|0|31|10.33|
4|Wednesday, January 25, 2023|23714448|09:35:56|09:45:34|USCIS|0|09|3.00|
5|Wednesday, January 25, 2023|23714735|09:46:24|09:56:41|ERO|0|10|3.33|
6|Wednesday, January 25, 2023|23715004|09:58:54|10:13:42|ERO|0|14|4.67|
7|Wednesday, January 25, 2023|23715372|10:14:44|10:33:17|ERO|0|18|6.00|
8|Wednesday, January 25, 2023|23715918|10:34:39|10:38:24|ERO|0|03|1.00|
9|Wednesday, January 25, 2023|23716161|10:43:34|12:44:43|USCIS|2|01|40.33|
10|Wednesday, January 25, 2023|23721232|13:21:03|13:54:47|SF DPH|0|33|11.00|
11|Wednesday, January 25, 2023|23722492|14:00:01|14:02:56|ERO|0|02|0.67|
12|Wednesday, January 25, 2023|23722627|14:04:45|14:06:15|SF DPH|0|01|0.33|
13|Wednesday, January 25, 2023|23722647|14:07:22|16:21:48|USCIS|2|14|44.66|
14|Wednesday, January 25, 2023|23726604|16:24:28|16:31:42|SF DPH|0|07|2.33|
15|Wednesday, January 25, 2023|23726860|16:33:41|16:48:38|ERO|0|14|4.67|
16|Wednesday, January 25, 2023|23727293|16:50:34|16:54:49|SF DPH|0|04|1.33|
17|Wednesday, January 25, 2023|23727489|16:59:06|17:10:57|ERO|0|11|3.67|
18|Thursday, January 26, 2023|23731850|08:27:07|08:30:12|ERO|0|03|1.00|
19|Thursday, January 26, 2023|23731896|08:33:07|08:40:44|ERO|0|07|2.33|
20|Thursday, January 26, 2023|23732077|08:47:07|09:10:45|ERO|0|23|7.67|
21|Thursday, January 26, 2023|23732500|09:12:13|09:25:36|ERO|0|13|4.33|
22|Thursday, January 26, 2023|23732874|09:29:00|10:14:45|ERO|0|45|15.00|
23|Thursday, January 26, 2023|23734019|10:16:53|10:26:14|EV|0|09|3.00|
24|Thursday, January 26, 2023|23734245|10:27:06|10:50:19|ERO|0|23|7.67|
25|Thursday, January 26, 2023|23735014|10:54:27|11:03:46|ERO|0|09|3.00|
26|Thursday, January 26, 2023|23735556|11:13:47|11:21:58|ERO|0|08|2.67|
27|Thursday, January 26, 2023|23735785|11:23:09|11:37:11|ERO|0|14|4.67|
28|Thursday, January 26, 2023|23736420|11:40:18|12:03:52|ERO|0|23|7.67|
29|Thursday, January 26, 2023|23737232|12:06:24|12:10:04|ERO|0|03|1.00|
30|Thursday, January 26, 2023|23737422|12:12:36|12:20:52|ERO|0|08|2.67|
31|Thursday, January 26, 2023|23737748|12:23:33|12:33:05|ERO|0|09|3.00|
32|Thursday, January 26, 2023|23738312|12:38:48|12:51:42|ERO|0|12|4.00|
33|Thursday, January 26, 2023|23739406|13:14:44|13:27:17|SF DPH|0|12|4.00|
34|Thursday, January 26, 2023|23739879|13:29:21|13:41:00|ERO|0|11|3.67|
35|Thursday, January 26, 2023|23740292|13:42:39|14:02:37|ERO|0|19|6.33|
36|Thursday, January 26, 2023|23740936|14:05:59|14:20:55|HS|0|14|4.67|
37|Thursday, January 26, 2023|23741469|14:23:08|14:36:35|ERO|0|13|4.33|
38|Thursday, January 26, 2023|23742046|14:43:42|14:50:49|ERO|0|07|2.33|
39|Thursday, January 26, 2023|23742090|14:52:12|14:56:03|HS|0|03|1.00|
40|Thursday, January 26, 2023|23742448|14:58:31|15:08:56|ERO|0|10|3.33|
41|Thursday, January 26, 2023|23742848|15:10:41|15:38:59|ERO|0|28|9.33|
42|Thursday, January 26, 2023|23743795|15:43:01|15:52:14|SF DPH|0|09|3.00|
43|Thursday, January 26, 2023|23744182|15:56:42|16:28:13|USCIS|0|31|10.33|
44|Thursday, January 26, 2023|23745274|16:31:49|16:55:37|ERO|0|23|7.67|
45|Thursday, January 26, 2023|23745999|16:58:56|17:09:32|HS|0|10|3.33|
1|Wednesday, February 01, 2023|23815615|08:34:19|08:40:34|EV|0|06|2.00|
2|Wednesday, February 01, 2023|23815698|08:41:53|08:46:45|ERO|0|04|1.33|
3|Wednesday, February 01, 2023|23815791|08:50:41|08:58:17|ERO|0|07|2.33|
4|Wednesday, February 01, 2023|23815936|09:00:57|10:14:03|ERO|1|13|24.33|
5|Wednesday, February 01, 2023|23817771|10:23:42|10:28:43|ERO|0|05|1.67|
6|Wednesday, February 01, 2023|23817935|10:30:22|10:34:21|ERO|0|03|1.00|
7|Wednesday, February 01, 2023|23818061|10:35:22|10:42:14|VA DH|0|06|2.00|
8|Wednesday, February 01, 2023|23818326|10:45:24|11:07:44|ERO|0|22|7.33|
9|Wednesday, February 01, 2023|23818992|11:09:17|11:17:48|EV|0|08|2.67|

[tiffle]

Without any brainwork on my part try replacing the pipe character “|” with a tab.

[cdthomer]

I did try that, but unfortunately there is enough variance in the columns data that even tab characters don’t line things up very well. For instance, here is the output when replacing pipe separators with tabs:

Formatted output text (click to expand/collapse)

1	Wednesday, January 25, 2023	23713304	08:04:39	08:19:03	CMS	0	14	4.42	
2	Wednesday, January 25, 2023	23713501	08:34:30	08:58:49	SF DPH	0	24	8.00	
3	Wednesday, January 25, 2023	23713831	09:02:07	09:33:50	ERO	0	31	10.33	
4	Wednesday, January 25, 2023	23714448	09:35:56	09:45:34	USCIS	0	09	3.00	
5	Wednesday, January 25, 2023	23714735	09:46:24	09:56:41	ERO	0	10	3.33	
6	Wednesday, January 25, 2023	23715004	09:58:54	10:13:42	ERO	0	14	4.67	
7	Wednesday, January 25, 2023	23715372	10:14:44	10:33:17	ERO	0	18	6.00	
8	Wednesday, January 25, 2023	23715918	10:34:39	10:38:24	ERO	0	03	1.00	
9	Wednesday, January 25, 2023	23716161	10:43:34	12:44:43	USCIS	2	01	40.33	
10	Wednesday, January 25, 2023	23721232	13:21:03	13:54:47	SF DPH	0	33	11.00	
11	Wednesday, January 25, 2023	23722492	14:00:01	14:02:56	ERO	0	02	0.67	
12	Wednesday, January 25, 2023	23722627	14:04:45	14:06:15	SF DPH	0	01	0.33	
13	Wednesday, January 25, 2023	23722647	14:07:22	16:21:48	USCIS	2	14	44.66	
14	Wednesday, January 25, 2023	23726604	16:24:28	16:31:42	SF DPH	0	07	2.33	
15	Wednesday, January 25, 2023	23726860	16:33:41	16:48:38	ERO	0	14	4.67	
16	Wednesday, January 25, 2023	23727293	16:50:34	16:54:49	SF DPH	0	04	1.33	
17	Wednesday, January 25, 2023	23727489	16:59:06	17:10:57	ERO	0	11	3.67	
18	Thursday, January 26, 2023	23731850	08:27:07	08:30:12	ERO	0	03	1.00	
19	Thursday, January 26, 2023	23731896	08:33:07	08:40:44	ERO	0	07	2.33	
20	Thursday, January 26, 2023	23732077	08:47:07	09:10:45	ERO	0	23	7.67	
21	Thursday, January 26, 2023	23732500	09:12:13	09:25:36	ERO	0	13	4.33	
22	Thursday, January 26, 2023	23732874	09:29:00	10:14:45	ERO	0	45	15.00	
23	Thursday, January 26, 2023	23734019	10:16:53	10:26:14	EV	0	09	3.00	
24	Thursday, January 26, 2023	23734245	10:27:06	10:50:19	ERO	0	23	7.67	
25	Thursday, January 26, 2023	23735014	10:54:27	11:03:46	ERO	0	09	3.00	
26	Thursday, January 26, 2023	23735556	11:13:47	11:21:58	ERO	0	08	2.67	
27	Thursday, January 26, 2023	23735785	11:23:09	11:37:11	ERO	0	14	4.67	
28	Thursday, January 26, 2023	23736420	11:40:18	12:03:52	ERO	0	23	7.67	
29	Thursday, January 26, 2023	23737232	12:06:24	12:10:04	ERO	0	03	1.00	
30	Thursday, January 26, 2023	23737422	12:12:36	12:20:52	ERO	0	08	2.67	
31	Thursday, January 26, 2023	23737748	12:23:33	12:33:05	ERO	0	09	3.00	
32	Thursday, January 26, 2023	23738312	12:38:48	12:51:42	ERO	0	12	4.00	
33	Thursday, January 26, 2023	23739406	13:14:44	13:27:17	SF DPH	0	12	4.00	
34	Thursday, January 26, 2023	23739879	13:29:21	13:41:00	ERO	0	11	3.67	
35	Thursday, January 26, 2023	23740292	13:42:39	14:02:37	ERO	0	19	6.33	
36	Thursday, January 26, 2023	23740936	14:05:59	14:20:55	HS	0	14	4.67	
37	Thursday, January 26, 2023	23741469	14:23:08	14:36:35	ERO	0	13	4.33	
38	Thursday, January 26, 2023	23742046	14:43:42	14:50:49	ERO	0	07	2.33	
39	Thursday, January 26, 2023	23742090	14:52:12	14:56:03	HS	0	03	1.00	
40	Thursday, January 26, 2023	23742448	14:58:31	15:08:56	ERO	0	10	3.33	
41	Thursday, January 26, 2023	23742848	15:10:41	15:38:59	ERO	0	28	9.33	
42	Thursday, January 26, 2023	23743795	15:43:01	15:52:14	SF DPH	0	09	3.00	
43	Thursday, January 26, 2023	23744182	15:56:42	16:28:13	USCIS	0	31	10.33	
44	Thursday, January 26, 2023	23745274	16:31:49	16:55:37	ERO	0	23	7.67	
45	Thursday, January 26, 2023	23745999	16:58:56	17:09:32	HS	0	10	3.33	
1	Wednesday, February 01, 2023	23815615	08:34:19	08:40:34	EV	0	06	2.00	
2	Wednesday, February 01, 2023	23815698	08:41:53	08:46:45	ERO	0	04	1.33	
3	Wednesday, February 01, 2023	23815791	08:50:41	08:58:17	ERO	0	07	2.33	
4	Wednesday, February 01, 2023	23815936	09:00:57	10:14:03	ERO	1	13	24.33	
5	Wednesday, February 01, 2023	23817771	10:23:42	10:28:43	ERO	0	05	1.67	
6	Wednesday, February 01, 2023	23817935	10:30:22	10:34:21	ERO	0	03	1.00	
7	Wednesday, February 01, 2023	23818061	10:35:22	10:42:14	VA DH	0	06	2.00	
8	Wednesday, February 01, 2023	23818326	10:45:24	11:07:44	ERO	0	22	7.33	
9	Wednesday, February 01, 2023	23818992	11:09:17	11:17:48	EV	0	08	2.67	

Oddly enough, it looks even weirder when displaying in a window (see screenshot):

Display formatted output in window screenshot (click to expand/collapse)

[tiffle]

You’re using 4-space tabs - try 8-space tabs instead!

Displaying anything in a window will make it look “different” because of the wrapping that occurs.

I’m not at my Mac so I can’t be more constructive I’m afraid Chris.

[cdthomer]

I didn’t even know 8-space tabs were a thing... so how would I specify an 8-space tab?

And I appreciate your help even though you’re not at your computer!

[drdrang]

No matter what tab length you choose, you will eventually run into a situation where your columns aren't aligned.

I know you want to do this via post-processing, but you should know that if you set SQLite's .mode option to columns, you'll get the kind of output you want. The column widths will adjust to the length of the longest row. Since you apparently don't want headers, there's a setting for that, too. So

.mode columns
.headers off

will do what you want. These settings will last through your entire session, no matter how many queries you make. Here's an example of real output from one of my databases:

Column-formatted SQLite output (click to expand/collapse)

JVI v. Universal                Edward H. Rice          3823
Cevasco v. Amtrak, et al.       Richard E. Snyder       3854
Bailey v. Chicago               Jordan Marsh            3910
New York Crane Collapse         Joseph DeDonato         3915
Elwood Fire                     David L. Laveck         3922
Logue v. ITW                    Benjeman L. Nichols     3923
Moradi v. Chicago               Elizabeth A. Freidheim  3936
Torres v. McDermott             Carol P. Woosley        3938
Petrielle v. Martin Plumbing    Jeffrey T. Lahners      3940
3501 S. Francisco               Elizabeth A. Freidheim  3951
Mwesigwa v. DAP                 Steve Heine             3970
Montrose Water Main Break       John H. Ehrlich         3985
JCI Polycarbonate Pan Cracking  Charles Obosu           3986

If you insist on post-processing, search for tools that reformat Markdown tables. The output you have is essentially Markdown.

[cdthomer]

Oh man, I’m a little embarrassed I wasn’t aware of that, but it looks like that will likely work for my needs. I’ll tinker with my macros and report back. Thank you!

[cdthomer]

@drdrang that did the trick. I added those settings to a .sqliterc file in my user home directory, and the output is almost exactly what I am looking for!

Formatted output using SQLite settings (click to expand/collapse)

1    Wednesday, January 25, 2023  23713304          08:04:39    08:19:03  CMS       0      14                         4.42
2    Wednesday, January 25, 2023  23713501          08:34:30    08:58:49  SF DPH    0      24                         8.00
3    Wednesday, January 25, 2023  23713831          09:02:07    09:33:50  ERO       0      31                         10.33
4    Wednesday, January 25, 2023  23714448          09:35:56    09:45:34  USCIS     0      09                         3.00
5    Wednesday, January 25, 2023  23714735          09:46:24    09:56:41  ERO       0      10                         3.33
6    Wednesday, January 25, 2023  23715004          09:58:54    10:13:42  ERO       0      14                         4.67
7    Wednesday, January 25, 2023  23715372          10:14:44    10:33:17  ERO       0      18                         6.00
8    Wednesday, January 25, 2023  23715918          10:34:39    10:38:24  ERO       0      03                         1.00
9    Wednesday, January 25, 2023  23716161          10:43:34    12:44:43  USCIS     2      01                         40.33
10   Wednesday, January 25, 2023  23721232          13:21:03    13:54:47  SF DPH    0      33                         11.00
11   Wednesday, January 25, 2023  23722492          14:00:01    14:02:56  ERO       0      02                         0.67
12   Wednesday, January 25, 2023  23722627          14:04:45    14:06:15  SF DPH    0      01                         0.33
13   Wednesday, January 25, 2023  23722647          14:07:22    16:21:48  USCIS     2      14                         44.66
14   Wednesday, January 25, 2023  23726604          16:24:28    16:31:42  SF DPH    0      07                         2.33
15   Wednesday, January 25, 2023  23726860          16:33:41    16:48:38  ERO       0      14                         4.67
16   Wednesday, January 25, 2023  23727293          16:50:34    16:54:49  SF DPH    0      04                         1.33
17   Wednesday, January 25, 2023  23727489          16:59:06    17:10:57  ERO       0      11                         3.67
18   Thursday, January 26, 2023   23731850          08:27:07    08:30:12  ERO       0      03                         1.00
19   Thursday, January 26, 2023   23731896          08:33:07    08:40:44  ERO       0      07                         2.33
20   Thursday, January 26, 2023   23732077          08:47:07    09:10:45  ERO       0      23                         7.67
21   Thursday, January 26, 2023   23732500          09:12:13    09:25:36  ERO       0      13                         4.33
22   Thursday, January 26, 2023   23732874          09:29:00    10:14:45  ERO       0      45                         15.00
23   Thursday, January 26, 2023   23734019          10:16:53    10:26:14  EV        0      09                         3.00
24   Thursday, January 26, 2023   23734245          10:27:06    10:50:19  ERO       0      23                         7.67
25   Thursday, January 26, 2023   23735014          10:54:27    11:03:46  ERO       0      09                         3.00
26   Thursday, January 26, 2023   23735556          11:13:47    11:21:58  ERO       0      08                         2.67
27   Thursday, January 26, 2023   23735785          11:23:09    11:37:11  ERO       0      14                         4.67
28   Thursday, January 26, 2023   23736420          11:40:18    12:03:52  ERO       0      23                         7.67
29   Thursday, January 26, 2023   23737232          12:06:24    12:10:04  ERO       0      03                         1.00
30   Thursday, January 26, 2023   23737422          12:12:36    12:20:52  ERO       0      08                         2.67
31   Thursday, January 26, 2023   23737748          12:23:33    12:33:05  ERO       0      09                         3.00
32   Thursday, January 26, 2023   23738312          12:38:48    12:51:42  ERO       0      12                         4.00
33   Thursday, January 26, 2023   23739406          13:14:44    13:27:17  SF DPH    0      12                         4.00
34   Thursday, January 26, 2023   23739879          13:29:21    13:41:00  ERO       0      11                         3.67
35   Thursday, January 26, 2023   23740292          13:42:39    14:02:37  ERO       0      19                         6.33
36   Thursday, January 26, 2023   23740936          14:05:59    14:20:55  HS        0      14                         4.67
37   Thursday, January 26, 2023   23741469          14:23:08    14:36:35  ERO       0      13                         4.33
38   Thursday, January 26, 2023   23742046          14:43:42    14:50:49  ERO       0      07                         2.33
39   Thursday, January 26, 2023   23742090          14:52:12    14:56:03  HS        0      03                         1.00
40   Thursday, January 26, 2023   23742448          14:58:31    15:08:56  ERO       0      10                         3.33
41   Thursday, January 26, 2023   23742848          15:10:41    15:38:59  ERO       0      28                         9.33
42   Thursday, January 26, 2023   23743795          15:43:01    15:52:14  SF DPH    0      09                         3.00
43   Thursday, January 26, 2023   23744182          15:56:42    16:28:13  USCIS     0      31                         10.33
44   Thursday, January 26, 2023   23745274          16:31:49    16:55:37  ERO       0      23                         7.67
45   Thursday, January 26, 2023   23745999          16:58:56    17:09:32  HS        0      10                         3.33
1    Wednesday, February 01, 2023  23815615          08:34:19    08:40:34  EV        0      06                         2.00
2    Wednesday, February 01, 2023  23815698          08:41:53    08:46:45  ERO       0      04                         1.33
3    Wednesday, February 01, 2023  23815791          08:50:41    08:58:17  ERO       0      07                         2.33
4    Wednesday, February 01, 2023  23815936          09:00:57    10:14:03  ERO       1      13                         24.33
5    Wednesday, February 01, 2023  23817771          10:23:42    10:28:43  ERO       0      05                         1.67
6    Wednesday, February 01, 2023  23817935          10:30:22    10:34:21  ERO       0      03                         1.00
7    Wednesday, February 01, 2023  23818061          10:35:22    10:42:14  VA DH     0      06                         2.00
8    Wednesday, February 01, 2023  23818326          10:45:24    11:07:44  ERO       0      22                         7.33
9    Wednesday, February 01, 2023  23818992          11:09:17    11:17:48  EV        0      08                         2.67
10   Wednesday, February 01, 2023  23819266          11:18:49    12:05:52  SF DPH    0      47                         15.67
11   Wednesday, February 01, 2023  23820752          12:08:34    12:16:34  CMS       0      08                         2.67
12   Wednesday, February 01, 2023  23821051          12:18:22    12:23:26  ERO       0      05                         1.67
13   Wednesday, February 01, 2023  23821302          12:25:53    12:35:19  ERO       0      09                         3.00
14   Wednesday, February 01, 2023  23821714          12:39:13    13:24:18  ERO       0      45                         15.00
15   Wednesday, February 01, 2023  23823772          13:45:37    13:48:31  SF DPH    0      02                         0.67
16   Wednesday, February 01, 2023  23823907          13:49:51    14:11:00  ERO       0      21                         7.00
17   Wednesday, February 01, 2023  23824748          14:17:32    14:17:57  SF DPH    0      00                         0.00
18   Wednesday, February 01, 2023  23824939          14:23:10    14:27:02  SF DPH    0      03                         1.00

[drdrang]

Oh, now I see that you're concatenating the output of several queries, which does leave some offsets. I thought you were somehow building up a single piece of output from several queries (which might not even make sense; I know just enough SQL to get by). So maybe I've led you astray and post-processing after the concatenation would be better.

Anyway, if you ever need them, here are all the SQLite dot commands.

[Nige_S]

@cdthomer

I would prefer formatting the results after the fact

If you do end up heading down that route rather than using @drdrang's dot commands, try piping the text through column -s '|' -t. Seems to work OK on the output you provided.

[ccstone]

Let's neaten up that table just a little more...

Cleaning Up a Text Table of Financial Data v1.00.kmmacros (9.4 KB)

Macro Image

[cdthomer]

@drdrang

Oh, now I see that you're concatenating the output of several queries, which does leave some offsets. I thought you were somehow building up a single piece of output from several queries (which might not even make sense; I know just enough SQL to get by). So maybe I've led you astray and post-processing after the concatenation would be better.

Yes I am getting my info from multiple tables. I was initially getting it via multiple queries, however, your tip made wonder if there was a way to extract data across multiple tables using a single query (there is), and I discovered that by using UNION ALL I was able to extract the data from all tables in a single query and the formatting is even better now. So your advice helped me a lot to get to where I wanted to go!

Formatted SQL output using UNION ALL (click to expand/collapse)

1    Wednesday, January 25, 2023   23713304      08:04:39    08:19:03  CMS       0      14      4.42
2    Wednesday, January 25, 2023   23713501      08:34:30    08:58:49  SF DPH    0      24      8.00
3    Wednesday, January 25, 2023   23713831      09:02:07    09:33:50  ERO       0      31      10.33
4    Wednesday, January 25, 2023   23714448      09:35:56    09:45:34  USCIS     0      09      3.00
5    Wednesday, January 25, 2023   23714735      09:46:24    09:56:41  ERO       0      10      3.33
6    Wednesday, January 25, 2023   23715004      09:58:54    10:13:42  ERO       0      14      4.67
7    Wednesday, January 25, 2023   23715372      10:14:44    10:33:17  ERO       0      18      6.00
8    Wednesday, January 25, 2023   23715918      10:34:39    10:38:24  ERO       0      03      1.00
9    Wednesday, January 25, 2023   23716161      10:43:34    12:44:43  USCIS     2      01      40.33
10   Wednesday, January 25, 2023   23721232      13:21:03    13:54:47  SF DPH    0      33      11.00
11   Wednesday, January 25, 2023   23722492      14:00:01    14:02:56  ERO       0      02      0.67
12   Wednesday, January 25, 2023   23722627      14:04:45    14:06:15  SF DPH    0      01      0.33
13   Wednesday, January 25, 2023   23722647      14:07:22    16:21:48  USCIS     2      14      44.66
14   Wednesday, January 25, 2023   23726604      16:24:28    16:31:42  SF DPH    0      07      2.33
15   Wednesday, January 25, 2023   23726860      16:33:41    16:48:38  ERO       0      14      4.67
16   Wednesday, January 25, 2023   23727293      16:50:34    16:54:49  SF DPH    0      04      1.33
17   Wednesday, January 25, 2023   23727489      16:59:06    17:10:57  ERO       0      11      3.67
18   Thursday, January 26, 2023    23731850      08:27:07    08:30:12  ERO       0      03      1.00
19   Thursday, January 26, 2023    23731896      08:33:07    08:40:44  ERO       0      07      2.33
20   Thursday, January 26, 2023    23732077      08:47:07    09:10:45  ERO       0      23      7.67
21   Thursday, January 26, 2023    23732500      09:12:13    09:25:36  ERO       0      13      4.33
22   Thursday, January 26, 2023    23732874      09:29:00    10:14:45  ERO       0      45      15.00
23   Thursday, January 26, 2023    23734019      10:16:53    10:26:14  EV        0      09      3.00
24   Thursday, January 26, 2023    23734245      10:27:06    10:50:19  ERO       0      23      7.67
25   Thursday, January 26, 2023    23735014      10:54:27    11:03:46  ERO       0      09      3.00
26   Thursday, January 26, 2023    23735556      11:13:47    11:21:58  ERO       0      08      2.67
27   Thursday, January 26, 2023    23735785      11:23:09    11:37:11  ERO       0      14      4.67
28   Thursday, January 26, 2023    23736420      11:40:18    12:03:52  ERO       0      23      7.67
29   Thursday, January 26, 2023    23737232      12:06:24    12:10:04  ERO       0      03      1.00
30   Thursday, January 26, 2023    23737422      12:12:36    12:20:52  ERO       0      08      2.67
31   Thursday, January 26, 2023    23737748      12:23:33    12:33:05  ERO       0      09      3.00
32   Thursday, January 26, 2023    23738312      12:38:48    12:51:42  ERO       0      12      4.00
33   Thursday, January 26, 2023    23739406      13:14:44    13:27:17  SF DPH    0      12      4.00
34   Thursday, January 26, 2023    23739879      13:29:21    13:41:00  ERO       0      11      3.67
35   Thursday, January 26, 2023    23740292      13:42:39    14:02:37  ERO       0      19      6.33
36   Thursday, January 26, 2023    23740936      14:05:59    14:20:55  HS        0      14      4.67
37   Thursday, January 26, 2023    23741469      14:23:08    14:36:35  ERO       0      13      4.33
38   Thursday, January 26, 2023    23742046      14:43:42    14:50:49  ERO       0      07      2.33
39   Thursday, January 26, 2023    23742090      14:52:12    14:56:03  HS        0      03      1.00
40   Thursday, January 26, 2023    23742448      14:58:31    15:08:56  ERO       0      10      3.33
41   Thursday, January 26, 2023    23742848      15:10:41    15:38:59  ERO       0      28      9.33
42   Thursday, January 26, 2023    23743795      15:43:01    15:52:14  SF DPH    0      09      3.00
43   Thursday, January 26, 2023    23744182      15:56:42    16:28:13  USCIS     0      31      10.33
44   Thursday, January 26, 2023    23745274      16:31:49    16:55:37  ERO       0      23      7.67
45   Thursday, January 26, 2023    23745999      16:58:56    17:09:32  HS        0      10      3.33
1    Wednesday, February 01, 2023  23815615      08:34:19    08:40:34  EV        0      06      2.00
2    Wednesday, February 01, 2023  23815698      08:41:53    08:46:45  ERO       0      04      1.33
3    Wednesday, February 01, 2023  23815791      08:50:41    08:58:17  ERO       0      07      2.33
4    Wednesday, February 01, 2023  23815936      09:00:57    10:14:03  ERO       1      13      24.33
5    Wednesday, February 01, 2023  23817771      10:23:42    10:28:43  ERO       0      05      1.67
6    Wednesday, February 01, 2023  23817935      10:30:22    10:34:21  ERO       0      03      1.00
7    Wednesday, February 01, 2023  23818061      10:35:22    10:42:14  VA DH     0      06      2.00
8    Wednesday, February 01, 2023  23818326      10:45:24    11:07:44  ERO       0      22      7.33
9    Wednesday, February 01, 2023  23818992      11:09:17    11:17:48  EV        0      08      2.67
10   Wednesday, February 01, 2023  23819266      11:18:49    12:05:52  SF DPH    0      47      15.67
11   Wednesday, February 01, 2023  23820752      12:08:34    12:16:34  CMS       0      08      2.67
12   Wednesday, February 01, 2023  23821051      12:18:22    12:23:26  ERO       0      05      1.67
13   Wednesday, February 01, 2023  23821302      12:25:53    12:35:19  ERO       0      09      3.00
14   Wednesday, February 01, 2023  23821714      12:39:13    13:24:18  ERO       0      45      15.00
15   Wednesday, February 01, 2023  23823772      13:45:37    13:48:31  SF DPH    0      02      0.67
16   Wednesday, February 01, 2023  23823907      13:49:51    14:11:00  ERO       0      21      7.00
17   Wednesday, February 01, 2023  23824748      14:17:32    14:17:57  SF DPH    0      00      0.00
18   Wednesday, February 01, 2023  23824939      14:23:10    14:27:02  SF DPH    0      03      1.00
19   Wednesday, February 01, 2023  23825154      14:30:52    16:18:17  USCIS     1      47      35.66
20   Wednesday, February 01, 2023  23828324      16:20:15    16:29:25  HS        0      09      3.00
21   Wednesday, February 01, 2023  23828783      16:36:39    17:28:43  SF DPH    0      52      17.33

@ccstone

Let's neaten up that table just a little more...

Hey Chris, thanks for chiming in. For some reason when I try your macro I get the following error:
Execute a Shell Script failed with script error: column: line too long.

@Nige_S I’m pretty happy with the sql method but I will stash your tip away for another day.

[ccstone]

@cdthomer

For some reason when I try your macro I get the following error:
Execute a Shell Script failed with script error: column: line too long.

For column to work properly there must be a trailing linefeed at the end of the data – that's a little oddity of column.

The way I wrote the script sed should be adding that last linefeed if necessary...

[Nige_S]

@ccstone

Let's neaten up that table just a little more...

And then right-align the first column (coz numbers!) by adding sed -E 's/^([0-9.]+) ( *)/\2\1 /g' to the end of the pipe.

There's probably a better way to do that, but I'm way past the bounds of my sed knowledge -- and I have no idea how to do the same for the last column