Import iCal (ICS File) Data to Excel Table

T

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
205
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Hi,

I am looking to import the details from an iCal (ICS File) into a table in Excel, I was wondering if anyone has experience doing this or could point me in the right direction. Ideally I would only want to import the Name and Date of the Public Holiday.

The file is located on the NSW Australia Public Holidays Website and includes details of all Public Holidays for NSW Australia.

Based on the contents of the ICS (below) I need to find a way to scrape the two following lines for each Public Holiday.

DTSTART;VALUE=DATE:20201225
SUMMARY:Christmas Day

For the above, it would copy into a table as:
Date | Summary
25/12/2020 | Christmas Day


Rich (BB code): 
BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//ical.marudot.com//iCal Event Maker

X-WR-CALNAME:NSW public holiday dates
NAME:NSW public holiday dates
CALSCALE:GREGORIAN
BEGIN:VTIMEZONE
TZID:Australia/Sydney
TZURL:http://tzurl.org/zoneinfo-outlook/Australia/Sydney
X-LIC-LOCATION:Australia/Sydney

BEGIN:STANDARD
TZOFFSETFROM:+1100
TZOFFSETTO:+1000
TZNAME:AEST
DTSTART:19700405T030000
RRULE:FREQ=YEARLY;BYMONTH=4;BYDAY=1SU
END:STANDARD

BEGIN:DAYLIGHT
TZOFFSETFROM:+1000
TZOFFSETTO:+1100
TZNAME:AEDT
DTSTART:19701004T020000
RRULE:FREQ=YEARLY;BYMONTH=10;BYDAY=1SU
END:DAYLIGHT
END:VTIMEZONE

BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-781600677@marudot.com
DTSTART;VALUE=DATE:20201225
DTEND;VALUE=DATE:20201226
SUMMARY:Christmas Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-395273742@marudot.com
DTSTART;VALUE=DATE:20201226
DTEND;VALUE=DATE:20201227
SUMMARY:Boxing Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-428954385@marudot.com
DTSTART;VALUE=DATE:20201228
DTEND;VALUE=DATE:20201229
SUMMARY:Additional Day
DESCRIPTION:As Boxing Day falls on Saturday\, this is an additional public holiday.
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-725989500@marudot.com
DTSTART;VALUE=DATE:20210101
DTEND;VALUE=DATE:20210102
SUMMARY:New Year's Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-293439041@marudot.com
DTSTART;VALUE=DATE:20210126
DTEND;VALUE=DATE:20210127
SUMMARY:Australia Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-995897171@marudot.com
DTSTART;VALUE=DATE:20210402
DTEND;VALUE=DATE:20210403
SUMMARY:Good Friday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-456483595@marudot.com
DTSTART;VALUE=DATE:20210403
DTEND;VALUE=DATE:20210404
SUMMARY:Easter Saturday
DESCRIPTION:- the Saturday following Good Friday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-777595054@marudot.com
DTSTART;VALUE=DATE:20210404
DTEND;VALUE=DATE:20210405
SUMMARY:Easter Sunday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1936065893@marudot.com
DTSTART;VALUE=DATE:20210405
DTEND;VALUE=DATE:20210406
SUMMARY:Easter Monday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1659469736@marudot.com
DTSTART;VALUE=DATE:20210425
DTEND;VALUE=DATE:20210426
SUMMARY:ANZAC Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-179294136@marudot.com
DTSTART;VALUE=DATE:20210614
DTEND;VALUE=DATE:20210615
SUMMARY:Queen's Birthday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-2056790112@marudot.com
DTSTART;VALUE=DATE:20210802
DTEND;VALUE=DATE:20210803
SUMMARY:Bank Holiday
DESCRIPTION:Retail bank branches and certain financial institutions are required to remain closed on the first Monday in August (Bank Holiday) unless otherwise exempt - see Part 3A of the Retail Trading Act 2008. ‘Bank Holiday’ is included in this list because it has implications for the staffing of these institutions. See the Banks and Financial Institutions page of the Fair Trading website. Bank Holiday is not a declared public holiday.
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1145302066@marudot.com
DTSTART;VALUE=DATE:20211004
DTEND;VALUE=DATE:20211005
SUMMARY:Labour Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-185041632@marudot.com
DTSTART;VALUE=DATE:20211225
DTEND;VALUE=DATE:20211226
SUMMARY:Christmas Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-468483448@marudot.com
DTSTART;VALUE=DATE:20211226
DTEND;VALUE=DATE:20211227
SUMMARY:Boxing Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-427839876@marudot.com
DTSTART;VALUE=DATE:20211227
DTEND;VALUE=DATE:20211228
SUMMARY:Additional Day
DESCRIPTION:As Christmas Day falls on Saturday\, this is an additional public holiday. 
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-2077004861@marudot.com
DTSTART;VALUE=DATE:20211228
DTEND;VALUE=DATE:20211229
SUMMARY:Additional Day
DESCRIPTION:As Boxing Day falls on Sunday\, this is an additional public holiday.
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1370757825@marudot.com
DTSTART;VALUE=DATE:20220101
DTEND;VALUE=DATE:20220102
SUMMARY:New Year's Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-757364720@marudot.com
DTSTART;VALUE=DATE:20220103
DTEND;VALUE=DATE:20220104
SUMMARY:Additional Day
DESCRIPTION:As New Year's Day falls on Saturday\, this is an additional public holiday.
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-256695186@marudot.com
DTSTART;VALUE=DATE:20220126
DTEND;VALUE=DATE:20220127
SUMMARY:Australia Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1684387592@marudot.com
DTSTART;VALUE=DATE:20220415
DTEND;VALUE=DATE:20220416
SUMMARY:Good Friday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1708860534@marudot.com
DTSTART;VALUE=DATE:20220416
DTEND;VALUE=DATE:20220417
SUMMARY:Easter Saturday
DESCRIPTION:- the Saturday following Good Friday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-257120327@marudot.com
DTSTART;VALUE=DATE:20220417
DTEND;VALUE=DATE:20220418
SUMMARY:Easter Sunday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1691757165@marudot.com
DTSTART;VALUE=DATE:20220418
DTEND;VALUE=DATE:20220419
SUMMARY:Easter Monday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1556148394@marudot.com
DTSTART;VALUE=DATE:20220425
DTEND;VALUE=DATE:20220426
SUMMARY:ANZAC Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-918900773@marudot.com
DTSTART;VALUE=DATE:20220613
DTEND;VALUE=DATE:20220614
SUMMARY:Queen's Birthday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1767196981@marudot.com
DTSTART;VALUE=DATE:20220801
DTEND;VALUE=DATE:20220802
SUMMARY:Bank Holiday
DESCRIPTION:Retail bank branches and certain financial institutions are required to remain closed on the first Monday in August (Bank Holiday) unless otherwise exempt - see Part 3A of the Retail Trading Act 2008. ‘Bank Holiday’ is included in this list because it has implications for the staffing of these institutions. See the Banks and Financial Institutions page of the Fair Trading website. Bank Holiday is not a declared public holiday.
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1718037395@marudot.com
DTSTART;VALUE=DATE:20221003
DTEND;VALUE=DATE:20221004
SUMMARY:Labour Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-7747573@marudot.com
DTSTART;VALUE=DATE:20221225
DTEND;VALUE=DATE:20221226
SUMMARY:Christmas Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1882405611@marudot.com
DTSTART;VALUE=DATE:20221226
DTEND;VALUE=DATE:20221227
SUMMARY:Boxing Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1452519513@marudot.com
DTSTART;VALUE=DATE:20221227
DTEND;VALUE=DATE:20221228
SUMMARY:Additional Day
DESCRIPTION:As Christmas Day falls on Sunday\, this is an additional public holiday.
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1174402696@marudot.com
DTSTART;VALUE=DATE:20230101
DTEND;VALUE=DATE:20230102
SUMMARY:New Year's Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1790900273@marudot.com
DTSTART;VALUE=DATE:20230102
DTEND;VALUE=DATE:20230103
SUMMARY:Additional Day
DESCRIPTION:As New Year's Day falls on Sunday\, this is an additional public holiday.
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-892064899@marudot.com
DTSTART;VALUE=DATE:20230126
DTEND;VALUE=DATE:20230127
SUMMARY:Australia Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1597219656@marudot.com
DTSTART;VALUE=DATE:20230407
DTEND;VALUE=DATE:20230408
SUMMARY:Good Friday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1705271616@marudot.com
DTSTART;VALUE=DATE:20230408
DTEND;VALUE=DATE:20230409
SUMMARY:Easter Saturday
DESCRIPTION:- the Saturday following Good Friday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1623789798@marudot.com
DTSTART;VALUE=DATE:20230409
DTEND;VALUE=DATE:20230410
SUMMARY:Easter Sunday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1609533851@marudot.com
DTSTART;VALUE=DATE:20230410
DTEND;VALUE=DATE:20230411
SUMMARY:Easter Monday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1492196921@marudot.com
DTSTART;VALUE=DATE:20230425
DTEND;VALUE=DATE:20230426
SUMMARY:ANZAC Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-744901272@marudot.com
DTSTART;VALUE=DATE:20230612
DTEND;VALUE=DATE:20230613
SUMMARY:Queen's Birthday
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-922025176@marudot.com
DTSTART;VALUE=DATE:20230807
DTEND;VALUE=DATE:20230808
SUMMARY:Bank Holiday
DESCRIPTION:Retail bank branches and certain financial institutions are required to remain closed on the first Monday in August (Bank Holiday) unless otherwise exempt - see Part 3A of the Retail Trading Act 2008. ‘Bank Holiday’ is included in this list because it has implications for the staffing of these institutions. See the Banks and Financial Institutions page of the Fair Trading website. Bank Holiday is not a declared public holiday.
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1097614707@marudot.com
DTSTART;VALUE=DATE:20231002
DTEND;VALUE=DATE:20231003
SUMMARY:Labour Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1434512944@marudot.com
DTSTART;VALUE=DATE:20231225
DTEND;VALUE=DATE:20231226
SUMMARY:Christmas Day
END:VEVENT
BEGIN:VEVENT
DTSTAMP:20201218T035645Z
UID:20201218T035645Z-1804943850@marudot.com
DTSTART;VALUE=DATE:20231226
DTEND;VALUE=DATE:20231227
SUMMARY:Boxing Day
END:VEVENT
END:VCALENDAR



Thanks in advance.
t0ny84
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Hi. The following needs to be placed into a standard module. It assumes that there is an ICS file located at "D:\Holiday.ics". I have put code in the comments that will allow you / the user to instead select an ICS file instead, and should be pretty self-explanatory.

If it finds any events that meet your specifications, it will save the details to an array of a User Data Type called HolidayEvent. It then outputs the contents of that array to a new worksheet. Hope that makes sense, but let me know if you have any difficulties with it.

VBA Code: 
    Private Type HolidayEvent
        HolidayDate As Date
        HolidayName As String
    End Type
   
    Private Const EVENTDATE = "DTSTART;VALUE=DATE:"
    Private Const EVENTSUMMARY = "SUMMARY:"
   
    Sub ProcessICS()
       
        Dim Filename As String, arrCode As Variant, Counter As Long, Code As String
        Dim TotalLines As Long, CurrentLine As Long, LineOfCode As String
        Dim Holiday() As HolidayEvent
       
        ' If you want to offer the user the option of selecting the ICS file, you could replace this
        ' next line with:
        '
        ' Filename = Application.GetOpenFilename
       
        Filename = "D:\holidays.ics"
        Code = ReadFile(Filename)
       
        ' Split the contents of the file into an array of lines
        arrCode = Split(Code, Chr(13))
        TotalLines = UBound(arrCode)
       
        Do
            LineOfCode = arrCode(CurrentLine)
            If InStr(LineOfCode, EVENTDATE) Then
                ReDim Preserve Holiday(Counter)
                Holiday(Counter).HolidayDate = GetDate(CStr(Application.Clean(Split(LineOfCode, EVENTDATE)(1))))
            End If
            If InStr(LineOfCode, EVENTSUMMARY) Then
                Holiday(Counter).HolidayName = Split(LineOfCode, EVENTSUMMARY)(1)
                Counter = Counter + 1
            End If
            CurrentLine = CurrentLine + 1
          
        Loop Until CurrentLine = TotalLines + 1
       
        If UBound(Holiday) >= 0 Then
           
            Dim NewRow As Long
            Dim WS As Worksheet
                       
            Set WS = Sheets.Add
            For i = LBound(Holiday) To UBound(Holiday)
                NewRow = NewRow + 1
                WS.Cells(NewRow, 1).Value = Holiday(i).HolidayDate
                WS.Cells(NewRow, 2).Value = Holiday(i).HolidayName
            Next
       
        End If
       
        WS.Range("A:B").EntireColumn.AutoFit
       
        Set WS = Nothing
       
    End Sub
   
    Function ReadFile(ByVal Filename As String) As String
        ReadFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(Filename, 1).ReadAll
    End Function
   
    Function GetDate(ByVal RawDate As String) As Date
        GetDate = CDate(Right(RawDate, 2) & "/" & Mid(RawDate, 5, 2) & "/" & Left(RawDate, 4))
    End Function
 
Upvote 0
Solution
Here ya go, try this.

I'm not sure what good it will do you, that website only has the dates for 2022-2023! 🤣

VBA Code: 
Sub ScrapeICS()
'https://www.mrexcel.com/board/threads/import-ical-ics-file-data-to-excel-table.1208788/

Dim fDialog As FileDialog
Dim xFile As String
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 'Optional: FileDialog properties
fDialog.AllowMultiSelect = False
fDialog.Title = "Select an ICS file"
fDialog.InitialFileName = "C:\"
 'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "ICS files", "*.ics"
fDialog.Filters.Add "All files", "*.*"

'Show the dialog. -1 means success, anything else means Cancel, so End
If fDialog.Show = -1 Then
   xFile = fDialog.SelectedItems(1) 'The full path to the file selected by the user
Else
    End
End If

Dim xLine As String
Dim xStartDate As String
Dim i As Integer

'to start on the 2nd row
i = 1
Open xFile For Input As #1
    Do Until EOF(1)
        Line Input #1, xLine
        'see if the line has the magic words
        If InStr(1, xLine, "DTSTART;VALUE=DATE:", vbTextCompare) > 0 Then
            'increment the counter to put it on a new line
            i = i + 1
            'parse out the date
            xStartDate = Mid(xLine, 20, 4) & "-" & Mid(xLine, 24, 2) & "-" & Mid(xLine, 26, 2)
            'paste it in Excel format in the first column
            Sheets("Holidays").Cells(i, 1) = Format(xStartDate, "YYYY-MM-DD")
        End If
        'see if the line has "SUMMARY:
        If InStr(1, xLine, "SUMMARY", vbTextCompare) > 0 Then
            'Strip out the work "SUMMARY:"
            xLine = Replace(xLine, "SUMMARY:", "")
            'past that in the next cell over
            Sheets("Holidays").Cells(i, 2) = xLine
        End If
        
    Loop
'close the ICS file
Close #1
End Sub

I see that Dan W beat me by 3 minutes with some impressive fancy-dancy code. lol. Here's the bull-in-a-china-shop, brute-force-and-ignorance method!
 
Upvote 0
portews said:
I see that Dan W beat me by 3 minutes with some impressive fancy-dancy code
Click to expand...
LOL!
You've gone ahead and provided useful functionality like a file picker with filters and you call my code 'fancy dancy'!! Ha!
I note that you've decided to use FORMAT for date formatting. That was a good idea.
 
Upvote 0
portews said:
Here ya go, try this.

I'm not sure what good it will do you, that website only has the dates for 2022-2023! 🤣

I see that Dan W beat me by 3 minutes with some impressive fancy-dancy code. lol. Here's the bull-in-a-china-shop, brute-force-and-ignorance method!
Click to expand...
Dan_W said:
LOL!
You've gone ahead and provided useful functionality like a file picker with filters and you call my code 'fancy dancy'!! Ha!
I note that you've decided to use FORMAT for date formatting. That was a good idea.
Click to expand...

A massive BIG THANK YOU to both of you, this was making me want to pull out the little amount of hair I still have!
@Dan_W won the SOLUTION by 3 minutes though @portews I think I will end up using your code as it is smaller and already has the file picker built-in.

@portews - I'm not sure what good it will do you, that website only has the dates for 2022-2023! 🤣
I am building \ modifying a staff calendar and wanted to give users the option to just import the holidays instead of having to manually type them (this can lead to breaking the system!) 🤪:rolleyes:

Thanks again!
t0ny84
 
Upvote 0
If you want to go super duper crazy, if the users have Outlook on their systems, you take it to the next level and have the ICS import into their Outlook calendars....
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
379
Eric W
Eric W
J
  • Question Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
2K
felixstraube
F
B
  • Solved
4-10 holiday schedule formula
Replies
2
Views
591
Fluff
Fluff
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
S
  • Solved
Automating Company Holidays on calendar
Replies
4
Views
1K
saracat2012
S

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top