5 Replies Latest reply: Mar 18, 2011 4:51 AM by alxtoth RSS

    Import iCalendar (ics) data into QlikView

      Is it possible to import iCalendar data (ics) directly into QV? If not is there a workaround?

       

      http://en.wikipedia.org/wiki/ICalendar

       

       

      thanks.

        • Import iCalendar (ics) data into QlikView

          Can you provide a sample, ex as text file?

          -Alex

          • Import iCalendar (ics) data into QlikView
            s j

            you can not import ics into Qlikview.

              • Import iCalendar (ics) data into QlikView

                Hi,

                It is possible to transform iCal from this text

                 


                BEGIN:VEVENT
                SUMMARY:High Tide 5.72 ft
                DTSTART:20110101T095000Z
                UID:2011.0
                DTSTAMP:20080527T000001Z
                SEQUENCE:0
                END:VEVENT


                into this XML by replacing strings and adding XML closing tags.

                 


                <VEVENT>
                <SUMMARY>High Tide 5.72 ft</SUMMARY>
                <DTSTART>20110101T095000Z</DTSTART>
                <DTSTAMP>20080527T000001Z</DTSTAMP>
                </VEVENT>


                Than load the valid XML into QlikView.

                I like to do that using UNIX style command line tools: sed and grep. Get the Windows equivalent from GnuWin32.

                Maybe you could do the same from QV script by loading the ics file as plain text, replace the strings, than save the file as XML exactly in same order, and load the new file as XML.

                See here.

                -Alex

                  • Import iCalendar (ics) data into QlikView

                     


                    rem EDIT PROXY server to get file

                    curl --proxy 10.1.1.1:8080 http://www.mobilegeographics.com:81/ical/3988.ics > in.txt
                    rem curl --proxy 10.1.1.1:8080 http://www.mozilla.org/projects/calendar/caldata/UKHolidays.ics > in.txt

                    copy in.txt in.ics

                    rem replace iCalendar labels to XML labels
                    CALL :WORK "s/BEGIN:VCALENDAR/<VCALENDAR>/g"
                    CALL :WORK "s/END:VCALENDAR/<\/VCALENDAR>/g"
                    CALL :WORK "s/BEGIN:VEVENT/<VEVENT>/g"
                    CALL :WORK "s/END:VEVENT/<\/VEVENT>/g"
                    CALL :WORK "s/SUMMARY:\(.*\)/<SUMMARY>\1<\/SUMMARY>/g"
                    CALL :WORK "s/DTSTART:\(.*\)/<DTSTART>\1<\/DTSTART>/g"
                    CALL :WORK "s/DTEND:\(.*\)/<DTEND>\1<\/DTEND>/g"
                    CALL :WORK "s/DTSTAMP:\(.*\)/<DTSTAMP>\1<\/DTSTAMP>/g"
                    CALL :WORK "s/ORGANIZER:\(.*\)/<ORGANIZER>\1<\/ORGANIZER>/g"

                    rem keep only XML lines, drop the rest
                    grep ">" in.txt | grep "<" > ical.xml
                    del *.txt
                    GOTO:EOF

                    rem subroutine to do the work
                    :WORK
                    sed %1 in.txt > out.txt
                    del in.txt
                    ren out.txt in.txt
                    GOTO:EOF


                      • Import iCalendar (ics) data into QlikView

                        Here is even better way to transform all iCalendar markers into valid XML.And shorter, since there are more regular expressions

                        For example this calendar contains timestamps in the context of timezones, and the XML is invalid.

                         


                        rem replace iCalendar labels to XML labels
                        CALL :WORK "s/BEGIN:\(.*\)/<\1>/g"
                        CALL :WORK "s/END:\(.*\)/<\/\1>/g"
                        CALL :WORK "s/\(.*\):\(.*\)/<\1>\2<\/\1>/g"


                        rem keep only XML lines, drop the rest
                        grep ">" in.txt | grep "<" > ical.xml
                        del *.txt
                        GOTO:EOF

                        rem subroutine to do the work
                        :WORK
                        sed %1 in.txt > out.txt
                        del in.txt
                        ren out.txt in.txt
                        GOTO:EOF