Software development, photography, jokes, ....

Sites by me

 
tas-logoTransportation Administration System
snoezelkussen-logo-kleinstSnoezelen Pillows for Dementia
ikzoekeenbegeleider-logoBegeleiders voor gehandicapten
Laat uw hond het jaarlijkse vuurwerk overwinnen
Betuweroute en Kunst
logo 50x50Hey Vos! Je eigen naam@vos.net emailadres?
Kunst in huis? Nicole Karrèr maakt echt bijzonder mooie dingen
nettylogo2Kunst in huis? Netty Franssen maakt ook bijzonder mooie dingen
Professionele opvang bij Gastouderbureau
Kind-Zijn
Salarisadministratie en belastingadvies bij
De Zaak Loont
Zutphense Bomenstichting

Hosting Favorites

 
ANU Internet Services
XelMedia .internet services
register.com
GoDaddy.com

Blogroll

 
Bomenstichting
MacFreak
Google Translate
PHP
MySQL
jQuery
jQuery UI
codecademy
YourHead Stacks API
Favicon Generator. For real.
Check HTTPS problems



Marc's Place


 
icon


 ICSviewer
 
 Convert .ics-files to table-format

Read the Version History

This is a tool to convert an (exported) calender (.ics-file) into a table-formatted data grid, while calculating the hours you worked. I made it because I use iCal / Calendar / BusyCal for keeping track of worked hours, but these apps do not calculate those hours for me, or you.

ICSviewer can export the calendar data via copy/paste, into a CSV or directly into a MySQL table.



Registration


As of v3.0, SetEXIFData is shareware. You can keep on using it unregistered forever, but then you cannot use the MySQL export feature.
If you make a donation, in return you'll get a serial number mailed to your Paypal email address which removes the limitation and any 'unregistered' messages.

If you have donated for ICSviewer in the past, please e-mail me your Paypal transaction-reference and I will e-mail you your serial number. I am asking you to take action yourself, because email addresses on file are not always actual anymore.

If you have received your serial number, enter your Paypal email address and serial number in the appropriate fields at the bottom of the 'About…'-window. Simply closing this window will save your registration.


ics-viewer-00-registration-1'About…'-window when unregisteredics-viewer-00-registration-2'About…'-window when registered



Screenshots of the unregistered version


ics-viewer-01-main-unregistered

MySQL connection unregistered (all fields are disabled)


You can find the MySQL settings under the Preferences... menu-item.
ics-viewer-01a-prefs-mysql-unregistered
ics-viewer-01b-prefs-mysql-unregistered
ics-viewer-01c-prefs-mysql-unregistered


Screenshots when registered


ics-viewer-02-main-registered

MySQL connection when registered


You can find the MySQL settings under the Preferences... menu-item.
ics-viewer-02a-prefs-mysql-registered
ics-viewer-02b-prefs-mysql-registered


A note on implicit decimals: simply said, with implicit decimals (or implied decimals), the decimal places are converted to INT. If you have a number with 9 decimals, 1.000000000 for example, it is stored as 1000000000, for example in a BIGINT type. Integer calculations are faster and more accurate.

If you use explicit decimals, then data is stored as if there is a decimal comma. To store 9 decimals explicit, you need at minimum a DECIMAL(10,9) type to be able to store 1.000000000 (fixed-point-types.html ).

Do not try not to use float or double for financial data, as the types are not precise (problems-with-float.html ).

If you can store the Hours in a fixed-point decimal, uncheck 'Use implicit decimals'.
With 0 explicit decimals: store 1,50 and it will be used as 1
With 1 explicit decimals: store 1,50 and it will be used as 1.5
With 2 explicit decimals: store 1,50 and it will be used as 1.50
With 3 explicit decimals: store 1,50 and it will be used as 1.500

But if you must store the Hours in an INT, then check 'Use implicit decimals' and make sure the INT is used everywhere with exactly 2 implicit decimals, because:
With 0 implicit decimals: store 150 and it will be used as 150
With 1 implicit decimals: store 150 and it will be used as 15.0
With 2 implicit decimals: store 150 and it will be used as 1.50 (correct)
With 3 implicit decimals: store 150 and it will be used as 0.150

ics-viewer-02c-prefs-mysql-registered
You can select a column name from the pop-up, or, when you do not have the rights to read the table-schema, you can type the names of the table's columns in each appropriate field.
Note: the info between ( and ) is pulled from the MySQL schema and may help you to choose the correct field and data type. When you enter field names manually, you are not supposed to enter this kind of info.

ics-viewer-02d-prefs-mysql-registered

In the above screenshot you see that you have the possibility to add additional data, up to a maximum of three other columns of the selected table.
When you transfer your calendar data to MySQL, you might miss data, like a customer or order number, because these are not present as ICS-calendar attributes.
Therefore, I created the ability to add this kind of missing data, along with the calendar data, via a different way.

What you can do here for each one of these three extra fields:
- Give the data a description: 'Customer number' for example. This label will be shown when exporting to MySQL.
- Pick a MySQL column from the list to write the data to.
- Either use a SQL-query to find the data you need to choose from when exporting to MySQL, or, use static data. In this example, if you have only 1 customer, you would write the customer number into the text field, instead of an SQL query. Static data results in a pop-up menu with just 1 possible option.

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.

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. See below how that looks.
In the 3rd field-section, you can use the placeholder %F2%, which in turn will reference the selected content from the 2nd field-section.
These placeholders are handy, because by using these, you can automatically limit the result sets for the 2nd and 3rd pop-up menus.
For example, if, on export, you choose a certain customer number, this customer number can be used in the SQL query for, say, Products, to list only those products related to the chosen customer number (%F1%). The same goes for the third field, where %F2% is filled here with the selected product code and lists only order numbers related to the customer with that product.

I am talking about customer, product and order numbers, because that's relevant for me, but you can gather any kind of data here, or the same data in a different order. It doesn't matter.

A SQL query must:
- have two resulting column names: ID and TEXT
- begin with 'select '
- must contain ' from '
- have more than 15 characters
otherwise it is used as static data.

A ' limit 100' is automatically added to the query to prevent overloading the MySQL server.
With the 'Test'-button, which is only visible if the text is recognised as an SQL query, you can test your query.

All this results in a dialog window being shown when exporting to MySQL, where the SQL queries or static data fill a pop-up menu for each of the three enabled fields:

ics-viewer-09a-export-mysql

More info in the Export to MySQL chapter.


How to import an .ics calendar file


Export a calendar, for example from Apple Calendar:

ics-viewer-03-export-calendar

You can load an .ics-file by dragging and dropping it onto the grid or by choosing Open... from the File-menu.

ics-viewer-04-import-calendar
When you load a file, the following tags get processed: DTSTART, DTEND, SUMMARY, LOCATION, DESCRIPTION, CATEGORIES and ATTENDEEs. The start and end-times are split into two columns and the hours are calculated. Hours get two decimal places and are not rounded.
The From date and To date columns present the date in words, so these are not suitable for export to MySQL, but perhaps interesting in other areas.
Note: the tool does currently not honor the RRULE component.

ics-viewer-05-main-filled-some-colums

You can hide or show columns by (de-)selecting the respective checkboxes at the bottom of the screen and you can set the decimal point via the small pop-up menu.
The file's contents are immediately reprocessed after each change in settings.

You can broaden or narrow the display of the contents of the .ics-file by selecting from-to dates at the top of the screen.
Screen shot 2011-07-11 at 22.04.30
All settings are automatically saved inside a preferences file. When you start ICSviewer again, the last saved settings are reloaded.


Export to Numbers or Excel


1. Via the Export to CSV... button

This exports the data from the grid into a CSV file, which can be imported into Apple Numbers or Microsoft Excel.

ics-viewer-03a-export
ics-viewer-03b-export


2. Via Copy & Paste


Click inside the grid, select all contents via CMD-A and copy them via CMD-C.

Screen shot 2011-07-11 at 22.05.56
Then paste into an invoice or a spreadsheet. That's it.


Export to MySQL


Via the Export to MySQL... button

This writes the data from the grid directly into the connected MySQL table, according to the field-mapping in the preferences.

Note: when you want to concatenate calendar data into one field, like for example add location to description, simply map both these columns to the same MySQL table field.

Note: read above, if you haven't already, about using the implicit decimals checkbox.

Note: you can write the same data to MySQL over and over again, because there is no reference stored in the calendar that this data already has been written to MySQL.

If you have defined one or all three of the MySQL Extra Data fields, you'll be presented with a pop-up window first, to select data for the appropriate columns:

ics-viewer-09a-export-mysql
ics-viewer-09b-export-mysql
ics-viewer-09c-export-mysql

In the screenshots above, I used three SQL queries to populate the three pop-up menus. In the 2nd and 3rd pop-up menu, I used placeholders %F1% and %F2% to limit the results to my chosen Customer number.

Example SQL query for the 2nd popup menu:
select distinct a.pr_id as id, b.name as text from company_products a left join products b on b.pr_id=a.pr_id where a.co_id='%F1%'

If you click 'Export to MySQL', the calendar-data + the selected options from these 3 fields will be exported to MySQL.




macos_pnthr_vert_v1Download Download
for Mac OS X (Intel)
(32-bit and 64-bit version)
for Windows Vista and newer
(32-bit and 64-bit version)



   

Version history

 
v1 2011 Initial release
 
v2 2013 Fixes reading of Apple Calendar files.
 
v2.1 2013 Correctly reads event-data and multi-line descriptions.
 
v2.2 2014 Repaired crash when no DTEND: present in calendar event.
 
v2.3 2015 Corrected DTEND for All-Day Events.
 
v2.4 2015 Corrected multi-line SUMMARY.
 
v2.5 04-2016 - Sheet windows instead of modals,
- Link to new webpage,
- Better display of bigger text on Windows,
- 'Line No.' column now has a line number,
- 'Start at' and 'End at' columns now also include the date in ISO format for better sorting.
 
v2.6 05-2016 Added CATEGORIES.
 
v2.7 12-2016
From / To dates selection fixed.
From / To dates would take the chosen date, but with the current time as as start or endpoint, for example: 01-01-2016 09:10:34 to 13-12-2016 09:10:34
This would cause events on 01 jan 2016 earlier than 09:10 and events on 13 dec 2016 later then 09:10 not to show up.
From / To dates now use 00:00:00 as From time and 23:59:59 as To-time.
 
About-window at start
The About-window will not show itself automatically anymore when opening the app. The about window would pop-up each time you start the application until you donated; only then it would not pop-up anymore. Since this is a bit annoying, I moved the Paypal-button to the main window. When you donate, this button wil disappear.
 
New version-detection
As of v2.7, the app will check for a new new version and pop-up a notification dialog with a link
 
v2.8 02-2017 Now looks good on HiDPI (Retina) displays.
I dropped support for Linux.
 
v2.9 06-2017
What is new:
- New: Added an 'Export' feature, which exports to a .csv file.
- New: Added automatic save/restore of column checkboxes and decimal separator.
- New: Added column ATTENDEES.
 
What has changed:
- Moved LOCATION into its own column.
- No more rounding of hours. Hours are displayed with 2 decimals.
- Open and Save dialogs are now sheet windows on Mac OS X.
- When checking/unchecking columns or loading a new file, the chosen sort-column is now remembered.
 
v3.0 10-2017
What is new:
- New: Added shareware serial number.
- New: Now also as a 64bit application on MacOS X and Windows.
- New: Added MySQL export for users who donated:
- A Preferences window:
-- for MySQL connection settings
-- for mapping columns from the display with columns from the MySQL table
- An export button 'Export to MySQL' to export the calendar data directly into the MySQL table
 
What has changed:
- Handling of saving the states of the checkboxes has been fixed.
- Renamed the 'Export...' button to 'Export to CSV...'
- Default values for From date / To date are now the first / current day of the current month
- The selected sort-column is now saved.
- Long lines (description, dtstart/dtend) are now handled correctly.
- ICS file is now read once, not on every date-range change.
- Various code improvements.
 
v3.1 10-2017
What has changed:
- Bug fix: loading another .ics file did not reset the table.
 
v3.2 11-2017
What has changed:
- Bug fix MySQL export: concatenation-character ' | ' was added regardless.
 
v3.3 01-2018
What has changed:
- Added Extra Database Fields to the MySQL export Preferences and export-process.
- Fixed the bug that skipped the Unit when exporting to MySQL.
 
v3.5 03-2018
What has changed:
- Resizing the modal sheet windows is now disabled.
- Some other UI and code improvemnts.


mwx_logo-long-small
© 1997- Marc Vos (and others) Contact Me