Tech Forum Archive

Bulk import from .CSV: adult lessons for 2015

5 postsstarted by quandmeme 1,833 viewsoriginal topic

quandmeme
New Member
#1
I understand that there is a lessons feature on lds.org and LDS Tools. I understand that what follows is a bunch of work to simply duplicate functionality. But for wards where members synchronize their calendar to their mobile devices, it may be of assistance to someone to know that there is a way to upload lessons to the calendar. Otherwise, this is just another post on importing .csv into the LDS calendar. Adding the link to the lesson on lds.org is just showing off.

Step 1: In Excel (save as a .csv file), list every week when there will be lessons, culling general conference and stake conference (we never know whether ward conference will have normal lessons or will be supplanted until the week of).

Step 2: Copy the lesson tables from each manual into an excel spreadsheet (delete pictures F5>Special>Objects then delete)

Step 3: Extract links for the description

Code: 

Sub Extract_link_text_to_the_neighboring_cell()
Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
        HL.Range.Offset(0, 1).Value = HL.Address
    HL.Range.Hyperlinks.Delete
    Next
End Sub
Step 4: Clean up: kill curly quotes (word: autocorrect option on both tabs), concatenate (add "Page " if you think pages are worth keeping)

Step 5: Line up lessons with weeks

Step 6: Add columns to work with conversion tool as below

(remember save as .csv)

Step 7: try an online tool or use the google calendar two-step to test and generate an iCal/ICS file

[Subject],[Start Date],[Start Time],[End Date],[End Time],[All Day (boolean)],[Description]

The gurus may add that it is more flexible, and you can play with it, but this works for me:
-watch out for special characters (like curly quotes)
-All day is specified True or False
-MM/DD/YYYY works
-AM/PM times work

I have not succeeded in specifying location in way that is compatible with the calendar

(a) As of November 2014, go to http://manas.tungare.name/software/csv-to-ical/ download his csv and use his converter if you feel that this is compatible with confidentiality requirements and passing information through the site of this helpful but unknown fellow.

(b) depending on how you feel about google, you can also use a google account (not google applications) to import the .csv to a new calendar then export the file (use private url). (I use this so I have a nice test run because it is easy to start over if I've done something horribly wrong.)

Step 8: import into LDS.org Calendar
quandmeme
New Member
#2
This is what my .csv for 2015 looks like (don't ask about the mid-year time change).
📎 2015 AH adult lessons.csv (16.45 KiB)
russellhltn
Community Administrator
#3
quandmeme wrote:Step 8: import into LDS.org Calendar
I believe you have to be a calendar administrator (not just a editor) to do that.

And as a end user, I'd plead with those that implement this to make it a separate calendar and not mix it with ward general events. That way I can choose the appropriate class or even not subscribe at all if that fits my needs. My Sundays tends to be packed as it is, I don't want yet more non-events on there.
Have you searched the Help Center? Try doing a Google search and adding "site:churchofjesuschrist.org/help" to the search criteria.

So we can better help you, please edit your Profile to include your general location.
kisaac
Community Moderators
#4
quandmeme wrote:This is what my .csv for 2015 looks like (don't ask about the mid-year time change).
Here is my Excel file for lessons, seems simpler to me, as my lessons are "all day events" so they sit at the top of the day on LDS.org calendar, and it will work for anything you "bulk upload."
Bulk uploading excel spreadsheet.xlsx
File template for bulk uploading events.
(9.46 KiB) Downloaded 236 times
Make sure you keep the columns as shown, and titled as shown. I paste to Word to "massage" the events (lessons,) adding delimiting characters (using Find, Replace) here that will become column breaks in excel, then copy and paste to Excel, use "text to columns" in excel to delimit on the character I added in word to make the columns correct. Save to CSV, upload to google as described in thread, then to LDS.org calendar. Works for any other lists of items you need to transfer...see a screenshot of the excel file.

And yes, I also add my lessons to a separate "lessons calendar" so those that don't need them, won't be bothered by them.
📎 csv screenshot.jpg (99.77 KiB)
"A soft answer turneth away wrath" works great on LDSTech forum boards!
sbradshaw
Community Moderators
#5
quandmeme wrote:... if you feel that this is compatible with confidentiality requirements and passing information through the site of this helpful but unknown fellow.
I don't think there are any confidentiality restrictions for a lesson calendar – just membership data we have to be careful with.
Samuel Bradshaw • If you desire to serve God, you are called to the work.