Home PageFacebookRSS News Feed
PocketGPS
Web
SatNav,GPS,Navigation
Pocket GPS World - SatNavs | GPS | Speed Cameras: Forums

Pocket GPS World :: View topic - Please recommend a POI editor for Mac (Solved)
 Forum FAQForum FAQ   SearchSearch   UsergroupsUsergroups   ProfileProfile   Log in for private messagesLog in for private messages   Log inLog in 

Please recommend a POI editor for Mac (Solved)
Goto page 1, 2  Next
 
Post new topic   Reply to topic    Pocket GPS World Forum Index -> Points Of Interest (POIs)
View previous topic :: View next topic  
Author Message
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Sat Jan 14, 2017 4:37 pm    Post subject: Please recommend a POI editor for Mac (Solved) Reply with quote

I have recently changed from a Windows laptop to a MacBook Pro. I need to be able to create POIs, primarily for TomTom (.ov2 files) but the ability to create POIs for other SatNavs would be an advantage.

How I used to create a POI

I'd capture POIs by either using the POIViewer app in my iPhone or by using Google Earth to capture the coordinates of the POI and then transfer the data to an .xlsx file by using MS Excel, which I used as my Master File for converting to TomTom. I prefer to use the .xlsx file as it keeps the formatting information such as column width etc. which makes reading, editing, spell checking, etc. the file much easier.

I use four columns in my master file, e.g.:

Code:
Longitude Latitude  Name                       Phone
-2.23263  49.23138  Jersey Pearl, St Ouen      441534862137
-2.17936  49.20481  AVIS Hire Garage, St Peter 441534495000


I then used Extra POI Editor by TurboCCC to convert the .xlsx Master file into an .ov2 file with telephone numbers.

What I would like to do

I would ideally like to keep my master file as a four column .xlsx file which I can convert to an .ov2 file with telephone numbers. Unfortunately Extra_POI_Editor is Windows only and not available for the Mac.

I would like to preferably use software on the Mac but would also use online if I can get one to work properly.

What I have found

I have tried a few apps and also a few online convertors/editors with little success. From what I can gather using Mac Excel to convert an .xlsx file to a .csv can cause problems but I need to experiment further.

So, can anybody recommend software or online method to convert my master .xlsx files to TomTom .ov2 files and other SatNav POI file formats?

Many thanks,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D


Last edited by Privateer on Fri Feb 24, 2017 10:35 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail
abre
Occasional Visitor


Joined: Jan 30, 2013
Posts: 45
Location: Saddleworth, Yorkshire :o)

PostPosted: Sun Jan 15, 2017 7:05 pm    Post subject: Reply with quote

I've never had the need to create .ov2 files, but have saved many .csv files from .xlsx without any issue.

However you could dual boot your MBP and run Windows on a partition. Dual boot is a good option and works well.

Alternatively you could use VMWare Fusion 8 or Parallels and run Windows OS on the MacOS. You can then use your existing Windows apps to do what you need.

For dual boot or emulation you just need a Windows licence to install the OS.

I've done both methods over the years on my MBPs, but find emulation more flexible for my own use needs. I find VMWare better than Parallels and the licensing is far better and cheaper.
Back to top
View user's profile Send private message
MaFt
Pocket GPS Staff
Pocket GPS Staff


Joined: Aug 31, 2005
Posts: 15125
Location: Bradford, West Yorkshire

PostPosted: Mon Jan 16, 2017 10:33 am    Post subject: Re: Please recommend a POI editor for Mac Reply with quote

My Mac's hard drive died - replacement coming today - so I can't tell you off-hand the POI (OV2) editor/viewer that I have on there. I *think* it's this one: http://www.samposoft.com/software/poiview.aspx


Privateer wrote:
From what I can gather using Mac Excel to convert an .xlsx file to a .csv can cause problems but I need to experiment further.


Is it putting it all onto a single line? I generally use TextWrangler and when you save it as a csv or txt file then you need to change the save option to be "Windows (CRLF)" so that the line breaks work correctly on both windows and mac.

MaFt
Back to top
View user's profile Send private message Visit poster's website
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Wed Jan 18, 2017 12:04 pm    Post subject: Reply with quote

Hi abre and MaFt, thanks for the replies.

abre wrote:
However you could dual boot your MBP … Alternatively you could use VMWare Fusion 8 or Parallels and run Windows OS on the MacOS. You can then use your existing Windows apps to do what you need.

I have heard of dual booting and emulation. I’d rather stick to 100% Mac, if I can. If I can’t then I will look at emulation. Thanks for the recommendation of VMWare - I’ll look into that further if I need to.

MaFt wrote:
My Mac's hard drive died - replacement coming today - so I can't tell you off-hand the POI (OV2) editor/viewer that I have on there. I *think* it's this one: http://www.samposoft.com/software/poiview.aspx

Sorry to hear that your hard drive has died. I hope that you have it working again.

Yes, I have tried PoiView for Mac OS X but I initially didn’t get on that well with it although I think that is because I may have used the wrong .csv file from MS Excel. Once I have the right .csv file format working (I need to show all types of accents on letters correctly) I'll try PoiView for Mac OS X again.

MaFt wrote:
Is it putting it all onto a single line?

Yes, TextWrangler shows that it’s all on one line.

MaFt wrote:
I generally use TextWrangler and when you save it as a csv or txt file then you need to change the save option to be "Windows (CRLF)" so that the line breaks work correctly on both windows and mac.

I discovered TextWrangler when I was investigating BBEdit. I have already found TextWrangler very useful.

When using TextWrangler to view a .csv file, is it usual to see two commas, i.e. ",," on every row after the data has ended? I seem to get it no matter what type of way I save the .csv file? E.g:

-2.112919,49.186671,"Jersey Opera House, St Helier [TBC]}441534511115"
-2.169539,49.186058,"Harbour Gallery, St Brelade [TBC]}441534743044"
-2.102952,49.188943,"Royal Hotel (Best Western) main entrance, St Helier [TBC]}441534726521"
-2.102876,49.188743,"Royal Hotel (Best Western) car park, St Helier [TBC]}441534726521"
,,
,,
,,
,,
,,
,,
,,
,,
,,
,,
,,
,,
,,
,,

Regads,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D
Back to top
View user's profile Send private message Send e-mail
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Fri Jan 27, 2017 7:57 pm    Post subject: Reply with quote

I've been on holiday for a while but now I'm back.

As far as I can see, there are four types of .csv file that Mac Excel can save as:
  1. CSV UTF-8 (Comma delimited) (.csv)
  2. Comma Separated Values (.csv)
  3. Windows Comma Separated (.csv)
  4. MS-DOS Comma Separated (.csv)

I know that you've recommend "Windows Comma Separated", however out of the four, "CSV UTF-8 (Comma delimited) (.csv" is the only one to correctly show accents (e.g. grave, acute, circumflex, etc) when examined by Text Wrangler and unfortunately I would like to keep the accents in place.

I have found a number of obstacles:

Once of the lines contained the height of a car park (e.g. 6'8") and the double quote caused a problem in the .csv file. Once I found that one I changed the double quote to two single quotes.

Even though the .csv description is in double quotes, the conversion (when it works) won't include anything after the comma.

I'm getting a little frustrated that something that was so simple on a Windows platform is so difficult to achieve on the Mac platform.

Although I'd rather not, I am thinking that I will have to try Windows emulation on the Mac. Crying or Very sad

Regards,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D
Back to top
View user's profile Send private message Send e-mail
abre
Occasional Visitor


Joined: Jan 30, 2013
Posts: 45
Location: Saddleworth, Yorkshire :o)

PostPosted: Sat Jan 28, 2017 3:53 pm    Post subject: Reply with quote

I have read somewhere, and please forgive me for suggesting this, that copying the Excel data into a Google Sheet and then downloading it as a .csv from Google can help remove Mac - PC Excel compatibility issues...

I believe that most Mac - PC .csv issues are down to the end of line markers, Mac only uses CR whereas Windows uses CRLF. Editing an LF into a Mac .csv isn't as simple as it should be, but I believe going via Google might help.
Back to top
View user's profile Send private message
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Sat Jan 28, 2017 5:45 pm    Post subject: Reply with quote

Hi abre,

abre wrote:
I have read somewhere, and please forgive me for suggesting this, that copying the Excel data into a Google Sheet and then downloading it as a .csv from Google can help remove Mac - PC Excel compatibility issues...

Thanks for that suggestion. I tried that and it didn't work properly. Although that may be something to do with the following:

The current problem that I'm working on is 7 out of the 292 rows of individual POIs the description column/field doesn't get encased by double quotes (") on conversion to .csv - when viewed in TextWrangler. I've also tried converting to .txt file and still those 7 rows do not get encased in "".

It's always the same 7 rows. Nothing jumps out as to why these rows are not encased in "". I think that I have two options:
1. View file in a Hex editor - I don't rely want to do that as it may be time consuming.
2. Convert .xlsx to .csv, then edit .csv manually to insert "" then convert back to .xlsx. This will hopefully work.

abre wrote:
I believe that most Mac - PC .csv issues are down to the end of line markers, Mac only uses CR whereas Windows uses CRLF. Editing an LF into a Mac .csv isn't as simple as it should be, but I believe going via Google might help.

Yes, Windows and MS Excel for Windows seems a lot more forgiving for weird things that are not visible without the right tools.

Regards,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D
Back to top
View user's profile Send private message Send e-mail
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Sat Jan 28, 2017 6:15 pm    Post subject: Reply with quote

Have you tried double double quotes or ever triple double quotes. In the latter, the centre double quotes is itself enclosed in double quotes.
I remember having to so that in some .CSV files some time ago.
Back to top
View user's profile Send private message
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Sat Jan 28, 2017 8:08 pm    Post subject: Reply with quote

Hi Trevor,

No I haven't tried double or triple double quotes (as yet) that sounds like something I used to have to do with Commodore PET Basic in the early 1980s Shocked ! But I may have to try it at some point.

I have now tried converting the .xlsx file to .csv, editing .csv the .csv file manually to insert the missing " on either side of the data, and then converting back to .xlsx. When I converted it back to .csv and examined it in Text Wrangler the same 7 of the 292 rows still had missing "!! Shocked Evil or Very Mad Rolling Eyes

At the moment I'm stumped, so I'm going to give it up for the night as I've been working on the problem for hours.

I suspect that I have a corrupt master .xlsx file which still retains the corrupt part even when converting to and from .xlsx and .csv. Of course the Mac Excel CRLF issue doesn't help.

I'm starting to use this thread as a note to myself as to what I've done and what I may need to try next. I think that my next steps are to (in no order):

1. Try examining the master file in a Hex editor
2. Get my old Windows laptop out and importing the .xlsx and .csv files, editing them in Windows and then saving so that I can try them in the Mac

Thanks,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D
Back to top
View user's profile Send private message Send e-mail
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Sun Jan 29, 2017 12:27 am    Post subject: Reply with quote

Just as a matter of interest, does the output file actually have to have the ""?
Does it not see it a text anyway as it's not a number, as are the lat/lon?
When excel handles .csv files it obviously imports the text as text and the numbers as numbers, but when you export it as .csv it line terminates LFCR as you have found. I don't think there is a way of stopping that.
Back to top
View user's profile Send private message
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7037
Location: Reading

PostPosted: Sun Jan 29, 2017 6:22 am    Post subject: Reply with quote

Good point about text v numbers.

I've had grief before with numbers where the answer was to convert all the number fields to text fields or it would format the numbers incorrectly.

For example it would truncate decimal numbers into their simplest/shortest form.
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Sun Jan 29, 2017 8:18 pm    Post subject: Reply with quote

Kremmen wrote:
I've had grief before with numbers where the answer was to convert all the number fields to text fields or it would format the numbers incorrectly..

You might have something there! I have my longitude and latitude columns set to numbers with 6 decimal places - this is to give a nice even look to the data when as an .xlsx or .csv file

MS Excel .xlsx
Code:
-2.084050   49.169190   M&S and most southerly Post Office in British Isles, St Clement}441534732881
-2.136071   49.224944   Hamptonne Country Life Museum car park, St Lawrence [TBC]
-2.120090   49.251890   Bonne Nuit Bay
-2.081410   49.240370   Bouley Bay
-2.035830   49.195730   Jersey Pottery, Grouville [MIA 2011]
-2.169680   49.167360   Batterie Lothringen, Noirmont Point, St Brelade

Resulting .csv as displayed by TextWranger and TextEdit
Code:
-2.084050,49.169190,"M&S and most southerly Post Office in British Isles, St Clement}441534732881"
-2.136071,49.224944,"Hamptonne Country Life Museum car park, St Lawrence [TBC]"
-2.120090,49.251890,Bonne Nuit Bay
-2.081410,49.240370,Bouley Bay
-2.035830,49.195730,"Jersey Pottery, Grouville [MIA 2011]"
-2.169680,49.167360,"Batterie Lothringen, Noirmont Point, St Brelade"

You'll notice that neither Bonne Nuit Bay nor Bouley Bay are encased in double quotes.

I have now formatted every column from "general" or "number" to "text".

MS Excel .xlsx
Code:
-2.08405   49.16919   M&S and most southerly Post Office in British Isles, St Clement}441534732881
-2.136071   49.224944   Hamptonne Country Life Museum car park, St Lawrence [TBC]
-2.12009   49.25189   Bonne Nuit Bay
-2.08141   49.24037   Bouley Bay
-2.03583   49.19573   Jersey Pottery, Grouville [MIA 2011]
-2.16968   49.16736   Batterie Lothringen, Noirmont Point, St Brelade

Resulting .csv as displayed by TextWranger and TextEdit
Code:
-2.08405,49.16919,"M&S and most southerly Post Office in British Isles, St Clement}441534732881"
-2.136071,49.224944,"Hamptonne Country Life Museum car park, St Lawrence [TBC]"
-2.12009,49.25189,Bonne Nuit Bay
-2.08141,49.24037,Bouley Bay
-2.03583,49.19573,"Jersey Pottery, Grouville [MIA 2011]"
-2.16968,49.16736,"Batterie Lothringen, Noirmont Point, St Brelade"


As you can see there is no difference except that the alignment is out. Unfortunately both Bonne Nuit Bay and Bouley Bay are still a problem

My data was orginaially captured by a TomTom 950 Live PND which uses up to 5 decimal places. I now use Google Earth or POI Viewer (iOS APP) to capture longitude and latitude and they use 6 decimal places.

You'll notice that the "Hamptonne Country Life Museum car park" is a 6 decimal location thus showing that it was an updated POI, whilst the surrounding POIs are only 5 decimal locations thus showing that they are the originals. I first thought that this 5/6 decimal point might have caused the problem but that's not true for other POIs that have no 6 decimal locations near them but they still are t encased in double quotes.

I still think that I have corrupt data in the .xlsx file that replicates itself when converted to a .csv files. As the converting .xlsx to .csv and back again didn't cure the problem I think that I may well need to use a hex editor to see if I can solve the problem.

Regards,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D
Back to top
View user's profile Send private message Send e-mail
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7037
Location: Reading

PostPosted: Mon Jan 30, 2017 6:05 am    Post subject: Reply with quote

Sometimes, for formatting issues and checking, it's necessary to use a proportional font. Something like Courier or Lucinda Console.

That way any lines that are wrong may stick out because they are visibly longer or shorter where it matters.
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Mon Jan 30, 2017 12:07 pm    Post subject: Reply with quote

Kremmen wrote:
Sometimes, for formatting issues and checking, it's necessary to use a proportional font. Something like Courier or Lucinda Console.

Yes, that makes sense. However, before I changed the font, I opened up the master .xlsx file with Numbers (Mac spreadsheet) and I got the message: "The font Calibri isn't available on macOS. It was replaced with Trebuchet MS".

The above message gave me one of those aha moments when you think that you're on the right path.

I changed the font from Calibri to Courier in the master .xlsx file and then converted it to .csv only to find that Bonne Nuit Bay and Bouley Bay are still not encased in double quotes! Evil or Very Mad Rolling Eyes

I'm still sure that there is some data corruption in the .xlsx file. I think my next steps are to:

1. See if the built in spreadsheet can be used to convert to .csv
2. Might boot up old Windows laptop to see if it can open .xlsx file and save it as a clean uncorrupted one.
3. Use a Hex editor to spot subtle changes.

I wish to keep the four columns separate (Longitude, Latitude, Name/description, and phone) as I want the option to include the phone number or not.

The phone option may possibly be part of the problem as I use a formula to check column C (name) and column D (phone) - if there's an entry in column D then concatenate column C, a GT sign, and column D into column E. If there is no entry in column D then just copy column E into column E. At this point column E shows the concatenation but is just formula in each cell - you then have to copy & paste special (value) to get the contents into column F. Then delete columns C, D, and E. It sounds complicated but after a few goes it's quite straight forward.

I need to try conversion to .csv without the phone number concatenation process to to eliminate that as fouling things.

Regards,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D
Back to top
View user's profile Send private message Send e-mail
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7037
Location: Reading

PostPosted: Mon Jan 30, 2017 12:23 pm    Post subject: Reply with quote

Sometimes you have to be sneaky.

Double quotes are often processed out, much to annoyance.

The answer, in code, was always to use the Chr$(34) code, which is the ASCII for a double quote.

The result was instead of """" use " & chr$(34).

Can I suggest using Windows Laughing
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message







Posted: Today    Post subject: Pocket GPS Advertising

Back to top
Display posts from previous:   
Post new topic   Reply to topic    Pocket GPS World Forum Index -> Points Of Interest (POIs) All times are GMT + 1 Hour
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Make a Donation



CamerAlert Database

Click here for the PocketGPSWorld.com Speed Camera Database

Download Speed Camera Database
22.034 (27 Mar 24)



WORLDWIDE SPEED CAMERA SPOTTERS WANTED!

Click here to submit camera positions to the PocketGPSWorld.com Speed Camera Database


12mth Subscriber memberships awarded every week for verified new camera reports!

Submit Speed Camera Locations Now


CamerAlert Apps



iOS QR Code






Android QR Code







© Terms & Privacy


GPS Shopping