Transportation Administration System
Snoezelen Pillows for Dementia
Begeleiders voor gehandicapten
Laat uw hond het jaarlijkse vuurwerk overwinnen
Betuweroute en Kunst
Hey Vos! Je eigen naam@vos.net emailadres?
Kunst in huis? Nicole Karrèr maakt echt bijzonder mooie dingen
Kunst in huis? Netty Franssen maakt ook bijzonder mooie dingen |
|
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.
 | Download |  | Download |
| for macOS (Universal) (Intel / ARM) | for Windows (64-bit version) |
macOS
When you start ICSviewer the first time, macOS might warn you about the application being from an unidentified developer.
To allow ICSviewer to run on your system, start the application the first time by a right-click on its icon and choosing 'Open':
And then confirm that you want to open it. You only have to do this once.
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.
 'About…'-window when unregistered |  'About…'-window when registered |
Screenshots of the unregistered version
Initial Date Range preference setting
MySQL connection preferences when unregistered (all fields are disabled)
You can find the MySQL settings under the Preferences... menu-item.
Screenshots when registered
MySQL connection preferences when registered
You can find the MySQL settings under the Preferences... menu-item.
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
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.
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:
More info in the Export to MySQL chapter.
How to import an .ics calendar file
Export a calendar, for example from Apple Calendar:
You can load an .ics-file by dragging and dropping it onto the grid or by choosing Open... from the File-menu.
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.
Important note: the tool does currently not honour the RRULE (repeating events) component.
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.
When you click the buttons with the 3 dots, a calendar sheet-window drops down, where you can select a date:
All settings are automatically saved inside a preferences file.
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.
2. Via Copy & Paste
Click inside the grid, select all contents via CMD-A and copy them via CMD-C.
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:
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.
 | Download |  | Download |
| for Mac OS X (Intel) (64-bit version) | for Windows (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 | 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. |
| v2.8 | 02-2017 | Now looks good on HiDPI (Retina) displays. I dropped support for Linux. |
| v2.9 | 06-2017 |
|
| v3.0 | 10-2017 |
|
| v3.1 | 10-2017 |
|
| v3.2 | 11-2017 |
|
| v3.3 | 01-2018 |
|
| v3.5 | 03-2018 |
|
| v3.6 | 08-2018 |
|
| v3.7 | 09-2018 |
|
| v3.8 | 01-2019 |
|
| v3.9 | 05-2019 |
|
| v4.0 | 01-2020 |
|
| v4.1 | 01-2020 |
|
| v4.2 | 12-2020 |
|
| v4.3 | 07-2021 |
|
| v4.4 | 07-2021 |
|
| v4.5 | 08-2021 |
|
