Page 1 of 1
Generate verse counts for each chapter of the Bible
Posted: Tue Jun 25, 2024 8:36 pm
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?
Re: Generate verse counts for each chapter of the Bible
Posted: Wed Jun 26, 2024 7:25 am
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.
Re: Generate verse counts for each chapter of the Bible
Posted: Wed Jun 26, 2024 7:49 am
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
Re: Generate verse counts for each chapter of the Bible
Posted: Wed Jun 26, 2024 12:02 pm
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!
Re: Generate verse counts for each chapter of the Bible
Posted: Wed Jun 26, 2024 2:26 pm
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
Re: Generate verse counts for each chapter of the Bible
Posted: Wed Jun 26, 2024 7:44 pm
by epement
Thank you, Robert Rathbone! It worked exactly as I needed.
Much appreciated.
Re: Generate verse counts for each chapter of the Bible
Posted: Tue Jul 23, 2024 11:12 am
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)