Hi! We see you’re using an ad-blocker. We’re fine with that and won’t stop you visiting the site.
But as we’re losing ad-revenue from this then why not make a donation towards website running costs?. Or you could disable your ad-blocker for this site. We think you’ll find our adverts are not overbearing!
Joined: 30/12/2002 17:36:20 Posts: 4918 Location: Oxfordshire, England, UK
Posted: Sat Jan 14, 2017 4:37 pm Post subject: Please recommend a POI editor for Mac (Solved)
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
Joined: Jan 30, 2013 Posts: 45 Location: Saddleworth, Yorkshire :o)
Posted: Sun Jan 15, 2017 7:05 pm Post subject:
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.
Joined: Aug 31, 2005 Posts: 15320 Location: Bradford, West Yorkshire
Posted: Mon Jan 16, 2017 10:33 am Post subject: Re: Please recommend a POI editor for Mac
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.
Joined: 30/12/2002 17:36:20 Posts: 4918 Location: Oxfordshire, England, UK
Posted: Wed Jan 18, 2017 12:04 pm Post subject:
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
Joined: 30/12/2002 17:36:20 Posts: 4918 Location: Oxfordshire, England, UK
Posted: Fri Jan 27, 2017 7:57 pm Post subject:
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:
CSV UTF-8 (Comma delimited) (.csv)
Comma Separated Values (.csv)
Windows Comma Separated (.csv)
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.
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
Joined: Jan 30, 2013 Posts: 45 Location: Saddleworth, Yorkshire :o)
Posted: Sat Jan 28, 2017 3:53 pm Post subject:
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.
Joined: 30/12/2002 17:36:20 Posts: 4918 Location: Oxfordshire, England, UK
Posted: Sat Jan 28, 2017 5:45 pm Post subject:
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
Joined: Apr 04, 2006 Posts: 10118 Location: Bexhill, South Sussex, UK
Posted: Sat Jan 28, 2017 6:15 pm Post subject:
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.
Joined: 30/12/2002 17:36:20 Posts: 4918 Location: Oxfordshire, England, UK
Posted: Sat Jan 28, 2017 8:08 pm Post subject:
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 ! 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 "!!
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
Joined: Apr 04, 2006 Posts: 10118 Location: Bexhill, South Sussex, UK
Posted: Sun Jan 29, 2017 12:27 am Post subject:
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.
Joined: 30/12/2002 17:36:20 Posts: 4918 Location: Oxfordshire, England, UK
Posted: Sun Jan 29, 2017 8:18 pm Post subject:
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
Joined: 30/12/2002 17:36:20 Posts: 4918 Location: Oxfordshire, England, UK
Posted: Mon Jan 30, 2017 12:07 pm Post subject:
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!
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
Posted: Today Post subject: Pocket GPS Advertising
We see you’re using an ad-blocker. We’re fine with that and won’t stop you visiting the site.
Have you considered making a donation towards website running costs?. Or you could disable your ad-blocker for this site. We think you’ll find our adverts are not overbearing!
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
Or you could disable your ad-blocker for this site. We think you’ll find our adverts are not overbearing!
Hi! We see you’re using an ad-blocker. We’re fine with that and won’t stop you visiting the site.
But as we’re losing ad-revenue from this then why not make a donation towards website running costs?. Or you could disable your ad-blocker for this site. We think you’ll find our adverts are not overbearing!
Forum FAQ
Search
Usergroups
Profile
Log in for private messages
Log in
! But I may have to try it at some point.
