Generate verse counts for each chapter of the Bible

[epement]

This question is addressed to Tim or anyone who might know how to do this. I suspect there is a way that's easy. Here's the request:

For each chapter of the Bible, print ( book-abbrev, chapter-number, verse count ), followed by a newline.

This should produce 1189 lines of output, something like this:

Gen 1 31
Gen 2 25
Gen 3 24
Gen 4 26
. . .
Rev 21 27
Rev 22 21

Is there a way to write a script to do this?

[Tim]

Yes, but you need the whole Bible in a file to parse. Preferably one verse per line.

If you have lines like,

Gen 1:1
...
Gen 1:31
Gen 2:1

You just parse each line keeping track of the book name and chapter and when either changes, take note of the last verse the script saw. For instance, when the script gets to Gen 2:1 it will notice the chapter change and then output Gen 1:31.

[RobertRathbone]

If you have a program to open SQLite database files and execute queries, such as "DB Browser for SQLite", you can open a bible file such as AV1769.bib and execute a SQL query such as the one below. It only took me a few minutes to play around and create the following query:

Code: Select all

WITH chapters AS (
	SELECT ID
	      ,SUBSTR(ref, 1, INSTR(ref, ':')) AS chapter
	FROM bible
	ORDER BY ID
)
SELECT chapter
       ,COUNT(ID) AS verses	   
FROM chapters
GROUP BY chapter
ORDER BY MIN(ID)

I sorted the list by the verse ID in order to keep the chapter list in Bible order.

Hope this is helpful,
Robert

[epement]

Thanks, Robert! I have tested that command in DB Browser for SQLite, and it works correctly.

What commands do I need to add to the SQL query to export the output to c:/temp/test.csv to save the results? The C:/temp directory is writable. BTW, I want the line or row numbers (1 to 1189) to be included in the output.

Thanks in advance!

[RobertRathbone]

Try this query:

Code: Select all

WITH chapters AS (
	SELECT ID
	      ,SUBSTR(ref, 1, INSTR(ref, ':')) AS chapter
	FROM bible
	ORDER BY ID
)
SELECT ROW_NUMBER() OVER (ORDER BY MIN(ID)) AS "row"
       ,chapter
       ,COUNT(ID) AS verses	   
FROM chapters
GROUP BY chapter
ORDER BY MIN(ID)

When you are in DB Browser for SQLite and click on the "Execute Query" tab, there is a small toolbar inside the tab at the top. The 4th button from the right end shows "Save the results view" when you hover over it (at least on my version, which is c. 3.12.2). Clicking it gives you the option for saving the results of the query as a CSV, which will hopefully get you what you need.

Robert

[epement]

Thank you, Robert Rathbone! It worked exactly as I needed.

Much appreciated.

[wmcdannell]

Thank you for this RobertRathbone! If someone else happens to want to remove the colon in the second field/column of the output (like I did) the following modification to the query will do the job (subtract one from the length value of the substring).

Code: Select all

WITH chapters AS (
	SELECT ID
	      ,SUBSTR(ref, 1, INSTR(ref, ':')-1) AS chapter
	FROM bible
	ORDER BY ID
)
SELECT ROW_NUMBER() OVER (ORDER BY MIN(ID)) AS "row"
       ,chapter
       ,COUNT(ID) AS verses	   
FROM chapters
GROUP BY chapter
ORDER BY MIN(ID)