You are currently on IBM Systems Media’s archival website. Click here to view our new website.


Google CSV Calendars and IBM i

In “Google and i,” I began a series of integrating IBM i DB2 data with Google Apps for Business (GA4B), starting with producing a CSV file from our Employee Master table and importing it to a Google document as a spreadsheet. The second article, Google App Your IBM i Reporting used the same Employee Master table to produce a simple report and convert that report to a PDF, which we then imported to Google.

In this third article, we’ll use the same Employee Master table to produce a CSV file containing data that can be imported into a calendar, whether it’s a Google, Microsoft Office, Yahoo, Lotus Notes or Apple calendar.

Creating the Employee Table Calendar Entries

To provide a scenario of how we can generate calendar entries from our Employee table, let’s create a calendar entry for the current and future year employee birthdays, as well as, generate a vacation for each employee starting on their birthday for 10 days.

You can import two data types into a calendar—an ICS (iCalendar) file or a CSV (comma separated value) file. The decision of which to use depends on your targeted calendar destination. Google Calendar can use either the CSV or ICS format to import calendar entries, as can MS Office, Yahoo and Lotus Notes calendars. Apple’s iCal can only import an ICS file.

Another consideration is that the formatted calendar CSV file can’t provide for an automatic repeating calendar entry instruction as an ICS file can. An example is a birthday or anniversary date that occurred once but repeats yearly. Using a calendar instruction in ICS entry you code a birth date as 01-01-1940 and instruct it to occur yearly. The CSV has no such function so you must generate multiple entries programmatically for each recurring event. Using a CSV is the simplest route, so let’s use that for our proof of concept. I’ll handle ICS generation processing in a future article.

To determine the format required for an import CSV, I first created a calendar entry in Google Calendar for a sample meeting, birthday and anniversary. Then I exported that entry to a CSV file. You can perform the same function using MS Office, Lotus Notes, etc. This simple reverse engineering trick worked perfectly as it provided a formatted CSV as a guideline.

To provide the data for the generated calendar entries using our Employee table data, you can use any method of generating data that’s applicable for your business. RPG, COBOL or C works well, as does SQL. I chose to use SQL PL (SQL procedure language) due to it being a quick and flexible scripting language that can be compiled making it callable by high-level languages, thus suitable for our needs and transportable to other platforms.

If you’re unfamiliar with SQL PL, DB2 Universal Database for iSeries was the first member of the DB2 family to support the SQL PL and make this language available for the development of SQL user-defined functions (UDFs) and SQL triggers. The greatest advantage to using SQL PL is portability, making it much easier to use stored procedures, triggers and UDFs on other relational database management systems (RDBMS). More information about the use of SQL PL can be found in the IBM Redbooks publication, “Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries.”

As we in the IBM i world know all too well, integration has been one of the major elements of differentiation of the IBM i server in the information technology marketplace. The advantages and drawbacks of fully integrated systems, such as the DB2 UDB and IBM i have been the subject of endless disputes in the last few years. The success of IBM i and its predecessors indicates that integration is still considered one of the premier advantages of this platform which makes the use of SQL PL all the more desirable across our integrated platforms that we support in our Smarter Planet, Smarter Computing world.

Explaining the Code

The code for our SQL PL (Code Sample 1) is for the most part self-explanatory but some clarification helps to follow the logic of producing calendar entries for our CSV EMP_CALCSV table.

Variables are declared to hold the dates and subject, and the remaining values are hard coded. You may require more flexibility in your implementation here as well.

I decided to include the Clear/Create of the EMP_CALCSV table in my procedure whereas you may want to keep separate if your coding rules so dictate. Simply check for the existence of the table and if found, clear it, and if not, create it. Make sure to include the Label on coding after you’ve created the table as these entries are used for the column headings in our generated CSV. These labels also provide the compile listing text for columns and column headings for displays, such as interactive SQL.

The use of the For loop is perfect for our needs as SQL will fetch each Employee table row for us until end of data. Using the defined First Name, Middle Initial, Last Name and Birth Date columns from the Employee table we can then generate a calendar entry using the birth date column value as the starting point.

As stated previously, the CSV calendar entry approach doesn’t allow for a range of dates for recurring events so you must code a calendar entry for each year of a birthday programmatically as we show: one generated row for the current year and one for the following year and then any future years as well.

After we have populated all our required variables, we use an SQL Insert commands to write our row entry to the database for each of the two birthday events and the vacation event.

Once all our data has been processed and our For loop ended, all that’s left is to convert the EMP_CALCSV table to a CSV. We used the CRTCSVF utility mentioned in the first article as it allows for the use of Column Headings versus Column Names to be populated as the CSV Column Headings. The calendar import function in Google must have these Column Headings to specifically match to the import data API to know where to place the data in your Google Calendar.

David Andruchuk is the senior architect for Computer Systems Design Associates Inc.



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Are You Multilingual?

Rational enables development in multiplatform environments

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters