Generate verse counts for each chapter of the Bible

Topics of Interest other than Bible Analyzer
Post Reply
epement
Posts: 103
Joined: Fri Sep 09, 2011 9:00 pm
Location: Florida
Contact:

Generate verse counts for each chapter of the Bible

Post by 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?
Eric Pement
2 Cor. 4:5

Tim
Site Admin
Posts: 1521
Joined: Sun Dec 07, 2008 1:14 pm

Re: Generate verse counts for each chapter of the Bible

Post by 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.
Tim Morton
Developer, Bible Analyzer

But to him that worketh not, but believeth on him that justifieth the ungodly, his faith is counted for righteousness. (Rom 4:5 AV)

RobertRathbone
Posts: 54
Joined: Mon Jul 07, 2014 6:40 pm

Re: Generate verse counts for each chapter of the Bible

Post by 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
Posts: 103
Joined: Fri Sep 09, 2011 9:00 pm
Location: Florida
Contact:

Re: Generate verse counts for each chapter of the Bible

Post by 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!
Eric Pement
2 Cor. 4:5

RobertRathbone
Posts: 54
Joined: Mon Jul 07, 2014 6:40 pm

Re: Generate verse counts for each chapter of the Bible

Post by 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
Posts: 103
Joined: Fri Sep 09, 2011 9:00 pm
Location: Florida
Contact:

Re: Generate verse counts for each chapter of the Bible

Post by epement »

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

Much appreciated.
Eric Pement
2 Cor. 4:5

wmcdannell
Posts: 30
Joined: Thu Oct 12, 2023 5:13 pm

Re: Generate verse counts for each chapter of the Bible

Post by 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)

Post Reply