Import iCalendar (ics) data into QlikView

[#1]

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

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

thanks.

[#2]

Can you provide a sample, ex as text file?

-Alex

[suniljain]

you can not import ics into Qlikview.

[#4 · Accepted solution]

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

[#5]

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

[#6]

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