ICSviewer Pro
Convert your calendar into a work hours table and save it for your records.
ICSviewer Pro is a utility to parse a calendar file (.ics) into a table-formatted data grid, while calculating the hours you worked.
You can then archive the output via copy/paste, export to .csv or a database.
ICSviewer Pro parses single and repeating events, including detached events from a repeating event.
Tweet
You can then archive the output via copy/paste, export to .csv or a database.
ICSviewer Pro parses single and repeating events, including detached events from a repeating event.
System Requirements
ICSviewer Pro v5 runs on macOS 11 or higher, on 64-bit Intel and M (ARM) Macs, 64bit and ARM Windows and 64bit and ARM Linux.
Lower OS versions may work, however we don't support these OS versions.
Lower OS versions may work, however we don't support these OS versions.
Installing ICSviewer Pro
Use the macOS installer or, on Windows and/or Linux, drag a folder to your Applications-folder.
Languages / Talen / Sprachen / Idiomas / Lingue / Langues
ICSviewer Pro is localised for, in alphabetical order, Dutch, English, French, German, Italian and Spanish, with English as the default language.
If you see wrongly translated words, from English, in one of the languages, please let me know.
If you see wrongly translated words, from English, in one of the languages, please let me know.
Registration
After purchasing a serial number, then, when you received it, enter the email address and serial number in the appropriate fields at the bottom of the 'About…'-window. Simply closing this window will save your registration. You can install ICSviewer Pro on 5 (virtual) computers. When registered, a 'deactivate license' button will be available in de 'About...' window.
When you purchased ICSviewer Pro through the Mac App Store, there is no registration necessary.
When you purchased ICSviewer Pro through the Mac App Store, there is no registration necessary.
Some screenshots below show the text 'This is an unregistered version', which only applies to the 'serial number'-version, it is not shown in the Mac App Store version.
Screenshots
User's Manual
Reading an .ics file
You start by exporting a calendar from your calendar application. In this example I use Apple Calendar.
Then you either drag the exported .ics file onto the window of ICSviewer Pro, or open the file by choosing 'Open...' from the file menu.
The file is parsed immediately, using the selected date range.
When an .ics file is read, the following tags get processed: DTSTART, DTEND, SUMMARY, LOCATION, DESCRIPTION, CATEGORIES, ATTENDEE and RRULE. The start and end-times are split into two columns and the hours are calculated. Hours have two decimal places and are not rounded.
When the data is shown you can:
The Begin at and End at date/times are written using the ISO date notation: YYYY-MM-DD HH:MM, which is required for correct sorting of events.
The From date and To date columns present the date in words, so these are not suitable for use in a database, but perhaps interesting in other areas.
When you are done manipulating the lines, you then can:
The file is parsed immediately, using the selected date range.
When an .ics file is read, the following tags get processed: DTSTART, DTEND, SUMMARY, LOCATION, DESCRIPTION, CATEGORIES, ATTENDEE and RRULE. The start and end-times are split into two columns and the hours are calculated. Hours have two decimal places and are not rounded.
When the data is shown you can:
- Delete lines by selecting them and pressing one of the 'Delete'-keys. Lines are only deleted from the visible data, not from the .ics file.
- Sort the data by clicking a column header,
- Renumber lines after sorting by clicking the little circled arrow above the 'Line no.' column,
- Show a different date range by changing dates. Click on a date to change it.
- Show or hide columns by (un)checking the respective checkboxes at the bottom of the window.. These changes are automatically saved inside the preferences file.
The Begin at and End at date/times are written using the ISO date notation: YYYY-MM-DD HH:MM, which is required for correct sorting of events.
The From date and To date columns present the date in words, so these are not suitable for use in a database, but perhaps interesting in other areas.
When you are done manipulating the lines, you then can:
- Copy the visual table by clicking inside the grid, select all contents via CMD-A and copy them via CMD-C. This will include the <br> line breaks. Then you can paste it into a spreadsheet.
- Copy the visual table by clicking the 'Copy' button. Clicking this button replaces <br> line breaks with a space. Then you can paste it into a spreadsheet.
- Export the visual table to a CSV-file. Clicking this button replaces <br> line breaks by a space, or a new line, depending on the Text Qualifier-setting.
- Export the visual table to a MySQL database. Clicking this button replaces <br> line breaks by a new line. Note that ICSviewer Pro does not check if the events are already present in the MySQL table!
The Settings cq. Preferences
General - Default startup date range
General - CSV-export text qualifier
- When using 'None', <br> is converted to a single space.
- When using '"', <br> is converted to a new line. Note that Apple Numbers does a correct import with these new lines, Microsoft Excel might not.
SSH Connection
The SSH section is only used for a connection to a MySQL database. Therefore, an SSH connection is not mandatory.
You can use it, for example, when your MySQL / MariaDB database is on a remote server and MySQL is only accessible via localhost or 127.0.0.1.
You can use it, for example, when your MySQL / MariaDB database is on a remote server and MySQL is only accessible via localhost or 127.0.0.1.
MySQL Connection
If you want to archive the calendar events to a MySQL / MariaDB database, provide the details here.
This can be a database on your Mac or PC, a Mac or PC in your home network, or on a remote server.
If it is not on your local Mac or PC, remember that the default port 3306 might be closed for external access on the other server. You might need a VPN and/or an SSH connection.
This can be a database on your Mac or PC, a Mac or PC in your home network, or on a remote server.
If it is not on your local Mac or PC, remember that the default port 3306 might be closed for external access on the other server. You might need a VPN and/or an SSH connection.
MySQL Column Mapping
If the connection to the database was successful, ICSviewer Pro will try to read the column names of the specified table. If that does not succeed, which would be strange, you might lack certain rights to read the table 'information_schema.columns'.
The columns shown in the screenshot are from my own database table, and are shown here as an example.
Concatenation:
If you want to concatenate calendar columns into one database column, like for example store location and description together in the same database column, map both these columns to the same MySQL table column. Data is then separated by a vertical bar, surrounded by a space: " | ". The concatenation-order is in the ICSviewer Pro table columns order, from left to right.
The Unit-field:
You can enter some default value here, since ICSviewer Pro does not have a unit column. The unit is just for you, no need to actually use it. You can, for example, write 'Hrs.' in it, depending on the length of your database column. For example, I myself use a single 'W', for 'Work hours'.
Implicit decimals:
With implicit decimals (or implied decimals), the decimal places are converted to INT. ICSviewer Pro uses 2 decimals. For example, a value of '1.5' hours is then stored as '150'.
Integer calculations are faster and more accurate, so if you can store the 'Hours'-column in a column of type INT, then check 'Use implicit decimals'. Your software should then show the value not as 150, but as 1,50 or 1.50 - depending on the used decimal separator.
If you want to store the 'Hours'-column in a fixed-point decimal column, uncheck 'Use implicit decimals' and make sure your database column is, at minimum, a DECIMAL(5,2) type to be able to store values from 0.01 up to 999.99 (fixed-point-types.html ).
I myself do never use floats or doubles for financial data, as the types are not precise (problems-with-float.html ).
MySQL Queries
Here you have the possibility to add additional data, to, for example, identify the calendar events by assigning a customer code.
A calendar does not have a customer ID or order number, but you might need those in your software to match your calendar events to a customer and/or order number, for billing purposes.
What you can do here for each one of these three extra fields:
Note: you do not have to use all 3 extra fields. If you need only one, just fill out the first one. And if you don't need any extra data, leave them all blank.
Placeholders:
In the 2nd and 3rd field-sections, you can use the placeholder %F1% in SQL-queries, which will reference the selected content from the 1st field-section.
In the 3rd field-section, you can also use the placeholder %F2%, which in turn will reference the selected content from the 2nd field-section.
By using these placeholders, you can limit the result sets for the 2nd and 3rd pop-up menus, and show only related content.
For example, if, on export, you choose a certain customer ID, this customer ID can be used in the next SQL query for, say, Products, to list only those products related to the chosen customer ID (%F1%).
The same works for the third field, where %F1% is filled with the selected customer ID from the 1st query, and %F2% is filled with the selected product ID from the 2nd query, and will show only orders related to the selected customer ID + product ID.
Of course, for you, these may be other fields than customer, product and order. See the example queries below.
Syntax:
An SQL query must:
A limit 100 is automatically added to the query to prevent overloading the MySQL server and ICSviewer Pro.
With the 'Test'-buttons, which are only visible if the text is recognised as an SQL query, you can test your query.
A calendar does not have a customer ID or order number, but you might need those in your software to match your calendar events to a customer and/or order number, for billing purposes.
What you can do here for each one of these three extra fields:
- Give the data a label: 'Customer number' for example. This label will be shown when exporting to MySQL.
- Choose a MySQL column from the dropdown-menu to write this data to.
- Either use an SQL-query to present the data you want to choose from when exporting to MySQL, or, type the data directly into the area. Written data results in a pop-up menu with just one option to choose from.
Note: you do not have to use all 3 extra fields. If you need only one, just fill out the first one. And if you don't need any extra data, leave them all blank.
Placeholders:
In the 2nd and 3rd field-sections, you can use the placeholder %F1% in SQL-queries, which will reference the selected content from the 1st field-section.
In the 3rd field-section, you can also use the placeholder %F2%, which in turn will reference the selected content from the 2nd field-section.
By using these placeholders, you can limit the result sets for the 2nd and 3rd pop-up menus, and show only related content.
For example, if, on export, you choose a certain customer ID, this customer ID can be used in the next SQL query for, say, Products, to list only those products related to the chosen customer ID (%F1%).
The same works for the third field, where %F1% is filled with the selected customer ID from the 1st query, and %F2% is filled with the selected product ID from the 2nd query, and will show only orders related to the selected customer ID + product ID.
Of course, for you, these may be other fields than customer, product and order. See the example queries below.
Syntax:
An SQL query must:
- have two resulting column names, named id and text, and
- begin with the word select, and
- must contain the word from, and
- have more than 15 characters.
A limit 100 is automatically added to the query to prevent overloading the MySQL server and ICSviewer Pro.
With the 'Test'-buttons, which are only visible if the text is recognised as an SQL query, you can test your query.
Then, when you click 'Export to MySQL', the calendar events + the here selected options will be exported to your MySQL table.
Example query for the first item:
This query returns customer IDs + its location name:
select
co.coid as id
, ifnull(cl.clid, ' - No location - ') as text
from company co
left join company_locations cl on cl.clcoid = co.coid
where co.coid <> "SDS82"
and (co.cotype = 'K' or co.cotype = 'B')
and cl.clid <> 'HOME'
order by id
Example query for the second item:
This query returns product IDs which are billed per hour + its description, using the selected customer ID from the first item.
select distinct
cp.cpprid as id
, pr.prname as text
from company_products cp
join products pr on pr.prid = cp.cpprid
where cp.cpcoid = '%F1%'
and cp.cpbillingper = 'H'
Example query for the third item:
This query returns order numbers + its description, using the selected customer ID from the first item + the product ID from the second item.
select distinct
cporder as id
, cpsubdomain as text
from company_products
where cpcoid = '%F1%'
and cpprid = '%F2%'