Excel Export Feature

[vegsetup]

Morning Brothers,

I really enjoy using your JWLManager to clean up my JW information every few weeks. I have also used it very successfully to help others clean out their bloated JW Library database.

However I also used https://github.com/AntonyCorbett/JWLMerge to export my study bible notes to Excel, both to share and as a backup. The Excel export that Corbett made use of allowed very easy filtering of the exported information and in a simple format that could be shared. Unfortunately JWLMerge is now archived and no longer being developed, neither does it work with the current database schema of JW Library.

Would it be possible to adapt his Excel export code to work with your system to provide the same functionality?

Below is a sample of what the exported file from JWLMerge would look like.

Copy of Export from JW Library.xlsx

Thank you Veg

[erykjj]

Hi @vegsetup

Thank you for your request.

I am currently implementing a CSV export feature for the upcoming v2.5.0 release. However, the CSV files aren't intended to be re-imported into JWLManager.

Please test this pre-release and let me know what you think. I'm very open to suggestions/observations ;-)

https://github.com/erykjj/jwlmanager/releases/tag/v2.5.0-RC2

[erykjj]

Had to pull RC2. I had been testing on Linux and it worked fine. Tried on Windows10 and no go. Will release something later.

[erykjj]

OK, try this one, please: https://github.com/erykjj/jwlmanager/releases/tag/v2.5.0-RC3

[vegsetup]

Thank you very much, will test tonight and keep you posted.

[vegsetup]

I am not finding a CSV export option in the Stand-alone JWLManager_v2.5.0-RC3.exe.

Going to try the ZIP file now and see if I can see it there.

[vegsetup]

Okay, same experience on the ZIP file.

I copy my jwlibrary file in JWLManager

Select my Study Bible, leave it on Notes and Title

Click Export, click Yes to proceed

Try and save as type CSV, only option is TXT

I exported as TXT and it is in the same format as the older version, not a CSV.

I may be doing something wrong or not looking in the right place. Please let me know.

[vegsetup]

Okay, found it.

By clicking on the VIEW button you presented with the option of CSV and TXT and then whatever you select gives the option to save as a CSV and TXt.

Have a look now at CSV output

[vegsetup]

Evening my Brother,

First of all, brilliant job so far!!!!! It is really looking great and I am sure everyone is going to appreciate the extra features. I mostly focused on the study bible. For the most part you could not ask for more, column breakdown gives you a lot of flexibility, filtering and is a superb way of backing up your notes or sharing information. The TAGS and how you have exported that in the CSV format is nicely formatted. Just some suggestions:

The REFERENCE column is not always returning the bible book name and chapter.

This is only affecting BOOK 6, 16, 17, 19, 20, 40, 43, 44.

This is not consistent however, sometimes these books do return the bible book name and chapter in the REFERENCE column. I could not find any trigger or reason why this is only affecting certain chapters in these books. It may be different for others database but that is what is happening with my database..

All the other BOOK's are reflecting the bible book name and chapters.

---

Just a suggestion, the REFERENCE column does not need to include the chapter as this is also listed in the CHAPTER column

Also, suggest mapping the REFERENCE column after column BOOK and rename as NAME (or something like that) then it follows in normal bible book format Bible Book, Chapter, Verse

Also suggest renaming the column BLOCK to VERSE

---

My instinct was to use the EXPORT feature in JWLManager to get the information and that confused me when I could not find CSV. Playing around I found how you implment this feature in the VIEW feature. I am sure if this is explained in the Help file it would help.

---

Content Most of these observations below related to character set. Seems it might be exported in the UTF8 character set. I tried importing it into Excel with a few different character sets and it helped a little but did not remove all incorrect characters. Not sure if you could list or indicate possibly what character set you used for the export:

In JW Library notes, hard returns (pressing enter to create a space) shows up in the CSV notes as "\n\n". This could easily be removed in Excel with find and replace

In JW Library notes, soft returns (holding shift and enter) shows up in the CSV notes as " \n". This could easily be removed in Excel with find and replace

In JW Library, anything with an apostrophe, for instance "Jehovah's" comes up as follows in the CSV notes: Jehovah’s

In JW Library, when you copy past something like this in the notes: ....of great help.—Prov. 16:21, 23. It shows up in the CSV notes as follows: ....of great help.​—Prov. 16:21, 23

In JW Library, open and close quotation marks eg. "......." show up like this in the CSV notes: e: “For everything there is an appointed time, . . . a time to keep quiet and a time to speak.” (Ecclesiastes 3:1, 7)

In JW Library, anything like this To·biʹah’s comes up with this in the CSV To·biʹah’s

[glenmwright]

I have found that if you view the csv in a text editor it displays in the correct character set without the "​—" characters. In Excel if I open the CSV file, I get the different characters. However, if I open a blank Excel spreadsheet and then Get Data and import the CSV, the characters are displayed correctly using UTF-8. However, the downside is that it seems to mess up on fields without double quotes as text qualifiers. I notice sometimes the Title and Content are surrounded by "" and sometimes not.

[erykjj]

Wow, you guys have been busy :-) Thank you for all that feedback (from both). I'll try to address these points and lets see if we can smooth it out a bit ;-)

[erykjj]

I open the CSV file (in Linux) with Open With... LibreOffice Calc and it automatically selects the UTF-8 encoding. All I need to do is to click OK and the sheet opens. Do you not have the same option?

And, yes, all the files are Unicode (UTF-8) formatted.

[erykjj]

The CSV and TXT buttons are in the Data Viewer window, as they are not the official export/import functions. They are more limited (for now) and for special-use cases.

[erykjj]

The REFERENCE column is not always returning the bible book name and chapter.

Good catch. Found a bug. Will be fixed.

[erykjj]

Also suggest renaming the column BLOCK to VERSE

This varies between Bibles (verse) and other publications (paragraph block). It is called BlockIdentifier in the SQLite db, so I will leave it as such.

[erykjj]

Just a suggestion, the REFERENCE column does not need to include the chapter as this is also listed in the CHAPTER column

Also, suggest mapping the REFERENCE column after column BOOK and rename as NAME (or something like that) then it follows in normal bible book format Bible Book, Chapter, Verse

The reference column contains the subheading or title of the article where the BlockIdentifier is located. In case of notes associated with the Bible, it will include the Book name and chapter only. I've added the verse for completeness (though Jude 21 will show up as Jude 1:21. It's too complicated to filter worry about which books only have one chapter, etc. The column is there just for... reference :-) Everything can be sorted/filtered by the other columns.

[erykjj]

My instinct was to use the EXPORT feature in JWLManager to get the information and that confused me when I could not find CSV. Playing around I found how you implment this feature in the VIEW feature. I am sure if this is explained in the Help file it would help.

HELP file updated. Thanks.

[erykjj]

In JW Library notes, hard returns (pressing enter to create a space) shows up in the CSV notes as "\n\n". This could easily be removed in Excel with find and replace

In JW Library notes, soft returns (holding shift and enter) shows up in the CSV notes as " \n". This could easily be removed in Excel with find and replace

\n represents a newline character. Since CSV allows only one line per entry, some character has to be used to indicate line-breaks. That is why the 'official' export/import text file format is delimited by a header (all those ==={} attribute fields): it allows for searching, editing, cutting/pasting, etc.

[erykjj]

Please tell me if the UTF-8 formatting is adding unnecessary complexity to opening the files on Windows. If so, we may have to rethink it. But I would prefer to keep it, as it is... 'universal'.

[erykjj]

Also, since we are at this point, I'd appreciate some ideas about the export/import format. Is it too complicated with all those {ATTRIBUTE=} fields? Any suggestions about a text-file format that easily be read/edited and still contain all the meta-information about thousands of notes??

[erykjj]

The REFERENCE column is not always returning the bible book name and chapter.

Good catch. Found a bug. Will be fixed.

Please test this: https://github.com/erykjj/jwlmanager/releases/tag/v2.5.0-RC4

[vegsetup]

Please tell me if the UTF-8 formatting is adding unnecessary complexity to opening the files on Windows. If so, we may have to rethink it. But I would prefer to keep it, as it is... 'universal'.

Morning, makes perfect sense now that it is a universal standard for line breaks. That can also be rectified by the person using excel/libre fine and replace.

[vegsetup]

I have found that if you view the csv in a text editor it displays in the correct character set without the "​—" characters. In Excel if I open the CSV file, I get the different characters. However, if I open a blank Excel spreadsheet and then Get Data and import the CSV, the characters are displayed correctly using UTF-8. However, the downside is that it seems to mess up on fields without double quotes as text qualifiers. I notice sometimes the Title and Content are surrounded by "" and sometimes not.

Thanks Glen, that helped a lot. Going this route eradicated all the special characters. Also tested Libre and works fine in Libre as well so that resolves those issues. Might be good to mention in the help to use UTF-8 character set if neccessary to clean up the exported data.

[vegsetup]

Please tell me if the UTF-8 formatting is adding unnecessary complexity to opening the files on Windows. If so, we may have to rethink it. But I would prefer to keep it, as it is... 'universal'.

I don't think so. It works brilliantly for almost 100% of the data export. Agree best to keep as universal.

[vegsetup]

The REFERENCE column is not always returning the bible book name and chapter.

Good catch. Found a bug. Will be fixed.

Please test this: https://github.com/erykjj/jwlmanager/releases/tag/v2.5.0-RC4

Brilliant!!!!!!

REFERENCE field is now fully populated withe the chapter and verse. Excellent and then they can use the preceding columns to filter book and chapter if neccessary. Very nice, clear and understandable.

RC4 also correctly shows the link now which works well.

All special characters other than the standard newline character resolved in RC4.

[vegsetup]

The REFERENCE column is not always returning the bible book name and chapter.

Good catch. Found a bug. Will be fixed.

Please test this: https://github.com/erykjj/jwlmanager/releases/tag/v2.5.0-RC4

I checked this on a few publications.

On the SFL all REFERENCE fields correctly populated.

On the LFF however there are some REFERENCE fields that are coming up blank. The LINK column in the LLF is also all blank. It seems that the REFERENCE field is blank from start of Aug 2023. Before then it was working fine.

[vegsetup]

Also, since we are at this point, I'd appreciate some ideas about the export/import format. Is it too complicated with all those {ATTRIBUTE=} fields? Any suggestions about a text-file format that easily be read/edited and still contain all the meta-information about thousands of notes??

Anthony did something similar in https://github.com/AntonyCorbett/JWLMerge/wiki. The way he showed the attributes may be a bit simpler but it is still a txt file with attributes.

Maybe now that we have the CSV the export/import feature as is works is fine. The CSV gives those a little more technically challenge an option to export, view, backup, share and then for the more advanced users the TXT file will work for editing and reimporting.

[erykjj]

Maybe now that we have the CSV the export/import feature as is works is fine. The CSV gives those a little more technically challenge an option to export, view, backup, share and then for the more advanced users the TXT file will work for editing and reimporting.

The thing is, though, that you cannot re-import from the CSV file through JWLManager - it's a one-way export-only operation.

[erykjj]

On the LFF however there are some REFERENCE fields that are coming up blank. The LINK column in the LLF is also all blank. It seems that the REFERENCE field is blank from start of Aug 2023. Before then it was working fine.

I will have to look into this. Thank you for reporting!

[erykjj]

On the LFF however there are some REFERENCE fields that are coming up blank. The LINK column in the LLF is also all blank. It seems that the REFERENCE field is blank from start of Aug 2023. Before then it was working fine.

I will have to look into this. Thank you for reporting!

Hm, I tested with some notes created today in lff-S and they all showed up correctly, both in the reference and link fields...

Any way you can share your db? Just delete all the other notes (leaving only the "problematic" ones, and MASK the remaining ones, then attach the .jwlibrary backup (you may have to add a .zip extension)

[vegsetup]

On the LFF however there are some REFERENCE fields that are coming up blank. The LINK column in the LLF is also all blank. It seems that the REFERENCE field is blank from start of Aug 2023. Before then it was working fine.

I will have to look into this. Thank you for reporting!

Hm, I tested with some notes created today in lff-S and they all showed up correctly, both in the reference and link fields...

Any way you can share your db? Just delete all the other notes (leaving only the "problematic" ones, and MASK the remaining ones, then attach the .jwlibrary backup (you may have to add a .zip extension)

MODIFIED_2023-09-21.zip

Here is LFF masked, all other information deleted. See if it comes up the same on your side.

[erykjj]

OK. Found another bug, which also got squashed. Now the links should be generated correctly. I will release another RC shortly.

As to the blank reference column, the reason is that it is blank in the db:

The reason for this (most likely) is that you exported those notes using JWLManager and later imported them. The current releases did not export the Title field from the Location table, and so that info would be lost. I have also corrected that in this upcoming release. Your help is, indeed, invaluable. Thank you!

[erykjj]

Please test your db with RC5. Links should show. The empty reference field will have to remain empty; it's won't affect operation in any way.

https://github.com/erykjj/jwlmanager/releases/tag/v2.5.0-RC5

[glenmwright]

The addition of the links is very cool. I am getting some errors in the CSV. Sometimes TITLE and CONTENT are wrapped in double quotes and other times they are not. Also, it seems there are cases when the end of line characters are not replaced with \n

[vegsetup]

Evening Brothers,

The links are working perfectly.

I am still struggling with the LFF though. I created two new records and the REFERENCE column is still blank for those two new records. You will see that in the Excel attached, the records were created today the 22nd September. The behavior only started beginning of August??? The REFERENCE column is the chapter title.

The other thing I noticed in the LFF csv and Excel, JWLManager is not exporting the CHAPTER number in it's column, it is only exporting the BLOCK number (verse). So if you don't have the chapter title that appears in the REFERENCE column or the chapter number in the CHAPTER column, you don't actually know which chapter the notes and title are in. That make sense?

I have also included the database and excel, neither are masked, no worries. LFF Notes.xlsx LFF Export.zip LFF Notes.csv

Thank you

[erykjj]

The addition of the links is very cool. I am getting some errors in the CSV. Sometimes TITLE and CONTENT are wrapped in double quotes and other times they are not. Also, it seems there are cases when the end of line characters are not replaced with \n

@glenmwright , could you send me the .jwlibrary archive with those problematic notes for further investigation? You can email to me directly.

[erykjj]

The other thing I noticed in the LFF csv and Excel, JWLManager is not exporting the CHAPTER number in it's column, it is only exporting the BLOCK number (verse). So if you don't have the chapter title that appears in the REFERENCE column or the chapter number in the CHAPTER column, you don't actually know which chapter the notes and title are in. That make sense?

This is correct. Chapter is only in the case of Bible notes. All other publications use a DocumentId (along with IssueTagNumber for periodical pubs) to identify each article/chapter/document. That, along with the block is enough to locate the paragraph.

But you did catch another oversight on my part: I didn't include the DocumentId in the CSV. I will add it now.

[erykjj]

I am still struggling with the LFF though. I created two new records and the REFERENCE column is still blank for those two new records. You will see that in the Excel attached, the records were created today the 22nd September. The behavior only started beginning of August??? The REFERENCE column is the chapter title.

This is strange. I just created a note in lff-S, and the Location.Title field was correctly populated by the app. In your case, the fields are blank.

Is your JW Library app up-to-date? What version and operating system? Is the lff up-to-date?

[vegsetup]

Morning,

Just another small suggestion. If you have have a database open in JWLManager and you then open the Data View and look at either the CSV or TXT. When you click FILE-New to remove the current database, you should also close the Data View window. Otherwise the database is gone but the Data View window still has a view of the csv or txt.

[vegsetup]

I am still struggling with the LFF though. I created two new records and the REFERENCE column is still blank for those two new records. You will see that in the Excel attached, the records were created today the 22nd September. The behavior only started beginning of August??? The REFERENCE column is the chapter title.

This is strange. I just created a note in lff-S, and the Location.Title field was correctly populated by the app. In your case, the fields are blank.

Is your JW Library app up-to-date? What version and operating system? Is the lff up-to-date?

Okay, tested with both my laptops:

Work Machine: Windows ver 11 22H2 22621.2283 JW Library ver 14.0.163 (316609)

Personnel Machine: Windows ver 11 22H2 22621.2283 JW Library ver 14.1.34 (324551)

On my work machine I did the following: Deleted lff and redownloaded Deleted database and started with fresh database and new lff Same result, no information in the REFERENCE column

On my personnel machine tried the same steps: In addition, tried the LFFi (Introductory Brochure) Also no information in the REFERENCE column.

Here are the files for you too test your side:

New Database redownloaded book - UserdataBackup_2023-09-23_ZA5CD1514LY5.zip lffi same database same problem.xlsx lff new database same problem.xlsx lffi_E.zip

You mention you testing with lff-s? What language is that? I have attached the lffi-e (Life Forever Introductory English) which is what I was testing with.

[erykjj]

Just another small suggestion. If you have have a database open in JWLManager and you then open the Data View and look at either the CSV or TXT. When you click FILE-New to remove the current database, you should also close the Data View window. Otherwise the database is gone but the Data View window still has a view of the csv or txt.

The jury is out on this one :-) But I'll definitely take not of your suggestion. Thank you.

[erykjj]

So, I have tested and observe a similar behavior. It appears that when a note is added to a heading (question or title), the Title field in the Location column is blank. Any subsequent notes added at that location (DocumentId) will reference that same db entry and show up blank. Notice that notes created within a paragraph do show. Please test with a blank archive: save a blank archive in JWLManager and restore that in your JW Library, then add some notes.

If confirmed, this could be a bug in JW Library. When did v14 come out?

[vegsetup]

So, I have tested and observe a similar behavior. It appears that when a note is added to a heading (question or title), the Title field in the Location column is blank. Any subsequent notes added at that location (DocumentId) will reference that same db entry and show up blank. Notice that notes created within a paragraph do show. Please test with a blank archive: save a blank archive in JWLManager and restore that in your JW Library, then add some notes.

If confirmed, this could be a bug in JW Library. When did v14 come out?

Okay, Saved a new database from JWLManager Blank database from JWLManager.zip

Opened in JW Library, made changes and saved back to database Changes made to blank database from JWLManager.zip

Updated database copied back to JWLManager and exported to a CSV Changes made to new database from JWL.csv Changes made to blank database from JWLManager.xlsx

Experience the same. REFERENCE which I believe is your TITLE in the picture above remains blank. Uploads included to see if you see the same.

I am in South Africa Bethel and it all the JW Library versions we running at the branch are on ver 14. I am also on a beta test group for JW Library so am on a slightly later version.

I then also tried with an earlier backup of mine from July. All the information was there in the REFERENCE column except for the new entries I put in the old database. All of that points to something changing at the beginning of August. I noted that the latest version on the website is also ver 14 https://www.jw.org/en/online-help/jw-library/windows/manually-install-windows/

What version you running?

[erykjj]

Hm, so even your "regular text" notes don't generate the Location.Title field:

I am testing on a Windows 10 Virtualbox machine, using the Microsoft Store version of JW Library, which did generate those fields for me in the "regular" paragraph notes (as above) - though I tested again with another publication, and it's on and off... In any case, I don't see this as a bug with JWLManager, as I'm looking at the actual db in the .jwlibrary backups, not the CSV files. You may want to report it for further testing. It may be related to whether the note is highlighted or not.

[erykjj]

I released another test version: v3.0.0-beta1, since I'm planning to rethink/redo the {ATTRIBUTE=} note headers:

https://github.com/erykjj/jwlmanager/releases/tag/v3.0.0-beta1

Please test the reported \n and double-quote issues.

[erykjj]

https://github.com/erykjj/jwlmanager/releases/tag/v3.0.0-beta2

Now with Excel export (from within Data Viewer) :-)

[vegsetup]

https://github.com/erykjj/jwlmanager/releases/tag/v3.0.0-beta2

Now with Excel export (from within Data Viewer) :-)

Very nice!!! Also noticed that the data viewer actually shows the color of the notes as well. Might have been there before but just noticed it now, clever detail.

Something is different with beta2 as the XLS is now showing the DOCUMENTID which it did not before but still not the actual chapter name.

I have attached the files here for you review:

Notes.xlsx

Changes made to blank database from JWLManager (2).zip

Also seems the double quotes in the study bible fixed but Glen can confirm. I may be out of comms for a week but will catch up when I am back. Thank you so much for all the work on this, very exciting and helpful.

[erykjj]

the XLS is now showing the DOCUMENTID which it did not before but still not the actual chapter name

Yes, thanks to you I noticed that DocumentId was missing and added it.

As I mentioned before, the chapter only applies to Bible notes. Other publications don't use that field. They locate by the DocumentId and Block fields, and the reference column (Location.Title) is what should show the chapter title as generated by the app (usually including the chapter number, as it does in lff). You can see in the web link that only docid and par are used.

[erykjj]

Moving forward... export/import to/from Excel is implemented now via the buttons on the main interface (no longer in the Data Viewer window) - just change the extension to .xlsx when choosing filename for your export. Should work with Notes and Annotations.

FOR TESTING ONLY https://github.com/erykjj/jwlmanager/releases/tag/v3.0.0-beta4

[erykjj]

@vegsetup please test as thouroughly as you can. Thank you! https://github.com/erykjj/jwlmanager/releases/tag/v3.0.0-RC

[erykjj]

Already found and squashed a couple of bugs since the last release :-)