How to import meditation-sittings.txt into a spreadsheet

[Neill Alexander]

This is a question that I get asked occasionally. The meditation-sittings.txt format looks like this:

3679,1361991798000,1361993665000,1800,1806

3678,1361945404000,1361947278000,1800,1814

3677,1361906938000,1361908803000,1800,1805

3676,1361858696000,1361860578000,1800,1821

3675,1361818717000,1361820595000,1800,1817

3674,1361772529000,1361774395000,1800,1805

3673,1361739832000,1361741580000,1800,1687

3672,1361689433000,1361691430000,1800,1936

3671,1361636942000,1361638814000,1800,1812

3670,1361600194000,1361602088000,1800,1834

3669,1361560037000,1361561912000,1800,1815

3668,1361511875000,1361513744000,1800,1809

3667,1361471710000,1361473549000,1800,1779

3666,1361424757000,1361426641000,1800,1823

3665,1361386408000,1361388270000,1800,1801

The first column is the id for the sitting. The second and third are the start and end timestamps. This is the number of milliseconds since January 1st 1970. So we simply need to do a bit of arithmetic to calculate the actual date today. The attached spreadsheet contains the necessary formulae to do this calculation:

=B5/1000/60/60/24+"1-Jan-1970"+($A$2/24)

Where:

A2 = a cell containing the timezone offset in hours for where you live

B5 = the start timestamp (second column in the meditation-sittings.txt)

The same calculation is done for the end timestamp.

Simply paste the contents of meditation-sittings.txt with cell A5 highlighted. In OpenOffice this prompts me to import data. I set the separator to be a comma, and the format of columns 2 and 3 (the timestamp columns) to be 'text'. The spreadsheet then does the calculation and displays the start and end times in the right hand side.

Hopefully this is enough to help people get started.