Observation Planner Tool

Affiliation
American Association of Variable Star Observers (AAVSO)
Thu, 09/18/2014 - 00:57

This is probably not very high on the priority list, but...

I tried to use the "Observation Planner Tool" and downloaded the cvs file for further processing. When I open it with Excel, the RA(2000) column has a different format and comes up with wrong appearing numbers. The Dec(2000) column is fine.

I then opened the cvs file with a text editor. Here are a few lines:

Name,RA(2000),Dec(2000),Type,max,min,band,period(d),N(total),N(data_30),N(obsr_30),DeltaT
GM And,"00:00:03.65",+35:21:46.2,RRAB,12.430,13.300,V,0.706,1273,0,0,30
Z Peg,"00:00:06.55",+25:53:11.2,M,7.300,13.600,V,320,11291,19,12,2.92
EP Peg,"00:00:06.9",+20:14:14.4,SR,10.700,11.400,p,340,0,0,0,30

You can see that RA values are in quotes, whereas Dec values are not. Is there a reason for this choice, or is it a "bug"? I would prefer the format without quotes, then Excel would (probably) display the RA values correctly (I cannot change the cell format either to get the original "accuracy" - oops, not true, I found the "custom" cell format...). It would still be easier without this additional formatting step.

Thanks for looking at this issue  smiley

Cheers,

Helmar (AHM)

 

 

Formatting for Excel users

Hello Helmar,

I've had to address this issue with every .csv file I make public on the website that includes the RA.  That was originally done because by leaving off the quotes, Microsoft Excel would interpret the string as a time, and convert it to an AM/PM string.  However, the most recent update of Excel that I have (Excel for Mac 2011) even ignores the quotes around it, and *still* converts it! 

The only thing I can do to prevent Excel from reinterpreting the string in the RA column is to include a blank space in front of the RA which is not acceptable.  OpenOffice will open the file correctly, and I encourage people to use that (free!) option.

Since the quotes have been rendered useless by some (or all?) recent versions of Excel, I may be able to safely remove them, unless other users of that data have software that expects them to be there.  I would like to hear from other users of this first before I change it.

Also, if someone knows how to force Excel to read .csv files literally without conversion, I'd love to know.

Matthew

Affiliation
Royal Astronomical Society of Canada (RASC)
Formatting for Excel Users

Good Morning all,

I use Ubuntu as my operating system.  Open Office is not available; it has been replaced by Libre Office.  It is just an updated version of Open Office.  I have had some problems learning to use their spreadsheets, though they have all been minor (It helps if one reads the help pages).  I also have a computer operating under Windows 7 with the latest Mircosoft office.  Never use Microsoft office any more.  Highly recommend Libre Office.

Cheers

Bob

 

Affiliation
Astronomical Society of South Australia (ASSAU)
Excel 2008 and Open Office 4

Hi Matthew

The CSV in the initial post opens for me in Excel 2008 and Open Office 4 (on Mac OS X 10.9.4 in both cases).

In Excel 2008, with or without quotes around RA values, Excel considers them to be of time format and by reduces the number of decimal places to 1.

In Open Office 4, the values are considered to be general strings rather than time values, with or without quotes.

Hope this helps.

David

Affiliation
American Association of Variable Star Observers (AAVSO)
Re:Formatting for Excel users

Thanks Matthew and everybody else for all the responses!

I don't like Excel myself - it is sometimes just convenient since it is already installed...

The only way I found useful is to reformat the RA column cells with "custom", then print  "hh:mm:ss.00", see attachment below. By the way - I am still using Excel 2003...

I am not sure if it is worthwhile to double your work, Matthew, with the additional Excel-version of the csv-files.

I think this little trick -see above - might work well enough(?).

Thanks again,

Helmar

 

Affiliation
American Association of Variable Star Observers (AAVSO)
Hi,
Try putting a single

Hi,

Try putting a single apostrophe in front of the number. I got this to work in Excel 2008 for Mac and Open Office ver 4.1.1 for Windows. It will appear correctly in the spreadsheet, as well as in a CSV file (the apostrophe will not be displayed).

John

 

Thanks

Thanks everyone for the replies.  (I forgot about LibreOffice, thanks for that -- I use it at home occasionally.)

The main reason I don't want to put a space or apostrophe or anything else that isn't a part of the RA in that column is that it's not really a part of the data we're serving -- it's changing the data to suit one piece of (popular) software that's not designed correctly.  Everyone else who then writes software to parse the CSV file has to deal with that malformed field.

I don't know how to reconcile the need to send clean data with the need to serve the large user community for Excel other than to add a third download option "CSV for Microsoft Excel".