Bulk import from .CSV: adult lessons for 2015

Discussions about the Calendar Tool at lds.org. Questions about the calendar on the classic site should be posted in the LUWS forum.
quandmeme
New Member
Posts: 18
Joined: Wed Jul 13, 2011 4:55 pm

Bulk import from .CSV: adult lessons for 2015

Postby quandmeme » Mon Nov 17, 2014 11:30 am

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: Select all

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
Posts: 18
Joined: Wed Jul 13, 2011 4:55 pm

Re: Bulk import from .CSV: adult lessons for 2015

Postby quandmeme » Mon Nov 17, 2014 11:39 am

This is what my .csv for 2015 looks like (don't ask about the mid-year time change).
Attachments
2015 AH adult lessons.csv
don't laugh
(16.45 KiB) Downloaded 64 times

russellhltn
Community Administrator
Posts: 20779
Joined: Sat Jan 20, 2007 2:53 pm
Location: U.S.

Re: Bulk import from .CSV: adult lessons for 2015

Postby russellhltn » Mon Nov 17, 2014 1:18 pm

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 Wiki?
Try using a Google search by adding "site:tech.lds.org/wiki" to the search criteria.

kisaac
Community Moderators
Posts: 1170
Joined: Sun Oct 21, 2007 5:04 am
Location: Utah, united states

Re: Bulk import from .CSV: adult lessons for 2015

Postby kisaac » Fri Jan 09, 2015 7:17 pm

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 47 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.
Attachments
csv screenshot.jpg
(99.77 KiB) Not downloaded yet

User avatar
sbradshaw
Senior Member
Posts: 2501
Joined: Mon Sep 26, 2011 8:42 pm
Location: Provo, UT
Contact:

Re: Bulk import from .CSV: adult lessons for 2015

Postby sbradshaw » Fri Jan 09, 2015 10:07 pm

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 • Interested in church apps and sites, creative recordkeeping, clerk support, YSA wards and stakes, LDS music, Vineyard at BYU, and online service.


Return to “Calendar”

Who is online

Users browsing this forum: No registered users and 1 guest