Cruisers Forum
 


Join CruisersForum Today

Reply
 
Thread Tools Rate Thread Display Modes
Old 10-06-2015, 06:21   #1
Registered User

Join Date: Jun 2015
Posts: 7
Excel and openCPN

Excel and OpenCPN
Good morning folks!
I have been playing around with OpenCPN for a while and I have been thinking that it could help me a lot on a manual job I have been doing.
I do track three small boats that send me their position from time to time (e.g. every 30 min).
Those positions goes automatically into an Excel spreadsheet that display Latitude, Longitude, UTC and the name of the boat. The Spreadsheet update itself automatically every time I do received a position from one boat.
Is there any way I could automatize that operation in a way that those positions (Lat, Long, UTC, and the Boat’s name) could be understood and were displayed (plotted) in the OpenCPN?
Should it be possible, for example, to emulate a GPS or AIS data by NMEA sentence?
Alternatively, maybe, just generate three never-ending gpx track?
Thank you in advance. Any help will be highly appreciated
Sergio
__________________

__________________
Sergio Rocha is offline   Reply With Quote
Old 10-06-2015, 07:04   #2
Registered User
 
jstevens's Avatar

Join Date: May 2006
Location: On board Sarah, currently lying in Jacksonville, FL
Boat: Pearson, 424, 42', Sarah
Posts: 582
Images: 4
Re: Excel and openCPN

Sergio,
The easiest method to generate a file for import into another program from an Excel workbook is the save the worksheet as a comma-separated file ("xxx.csv"). Unfortunately OpenCPN only imports GPX files, or at least it appears so. Creating a GPX file from an Excel worksheet is not difficult, if you have some programming experience, using Visual Basic for Applications (VBA), which is built into Excel. MS provides a class of programming tools within VBA for manipulating xml files, which is the generalized form of GPX files.
I've written several VBA programs that do that, but they are tied to the Excel worksheet formats I use, which undoubtedly are different from whatever you are using.
If writing a GPX file generator in VBA code is not something you wish to attempt you might search for an NMEA generator program what accepts csv files as input.
If no one else offers a solution, a final option would be to send me a copy of the Excel workbook you use I'll see if I can easily adapt one of my VBA programs to generate a GPX file with trk or wpt elements that can be imported into GPX.

John
__________________

__________________
jstevens is offline   Reply With Quote
Old 10-06-2015, 07:25   #3
Registered User

Join Date: Oct 2009
Location: Lelystad, The Netherlands
Boat: vd Stadt 44, Ben the Boat
Posts: 195
Re: Excel and openCPN

Hi Sergio,

Don't know the input format into Excel but you might want to see if the data stream isn't able to write GPX directly.

You could also look at GPSBabel: convert, upload, download data from GPS and Map programs, it's capable of converting to GPX format.

Bart
__________________
champ8242 is online now   Reply With Quote
Old 10-06-2015, 08:09   #4
Registered User

Join Date: Jun 2015
Posts: 7
Re: Excel and openCPN

Thank you jstevens and champ8242.

Unfortunally my programming skills are near nule
__________________
Sergio Rocha is offline   Reply With Quote
Old 10-06-2015, 08:10   #5
Registered User

Join Date: Jun 2015
Posts: 7
Re: Excel and openCPN

Thank you jstevens and champ8242.
Sad to say that my programming skills are near zero.
I have tried to use GPS babel, but I do not know how it will deal with a never-ending file.
How a NMEA sentence that fit that purpose will look like? I want to make a try (load a position) before digging deeper
Thank you
__________________
Sergio Rocha is offline   Reply With Quote
Old 10-06-2015, 08:33   #6
Registered User
 
jstevens's Avatar

Join Date: May 2006
Location: On board Sarah, currently lying in Jacksonville, FL
Boat: Pearson, 424, 42', Sarah
Posts: 582
Images: 4
Re: Excel and openCPN

Sergio,
If you want to have OpenCPN track the positions of multiple vessels, then the NMEA sentence you will want to feed OpenCPN is likely one of the AIS sentences, "VDM" and "VDO". They are the most complex sentences in the NMEA 183 world (AIVDM/AIVDO protocol decoding).
As Bart suggested, maybe you can have whatever program/script that feeds position data to Excel modified to generate these NMEA sentences. Does that software have a name and a source?

John
__________________
jstevens is offline   Reply With Quote
Old 10-06-2015, 08:46   #7
Registered User

Join Date: Jun 2015
Posts: 7
Re: Excel and openCPN

Thank you jstevens

Yes it has a source. And probably it can be modified.

My problem right now is to discover in what it has to be modified. I Mean what kind of strings.
__________________
Sergio Rocha is offline   Reply With Quote
Old 10-06-2015, 10:47   #8
Registered User

Join Date: Feb 2010
Location: On the go. Not in Prague.
Posts: 4,016
Re: Excel and openCPN

Sergio...
AIS is perhaps a bit too heavy a cannon for your application. OpenCPN can interpret the much simpler WPL sentence as an APRS position report (if enabled on Toolbox->Ships->AIS Targets), which could serve your purpose.
Code:
WPL Waypoint Location
       1       2 3        4 5    6
       |       | |        | |    |
$--WPL,llll.ll,a,yyyyy.yy,a,c--c*hh
1) Latitude
2) N or S (North or South)
3) Longitude
4) E or W (East or West)
5) Waypoint Name
6) Checksum
Keep the waypoint name unique and consistent for each of your targets and you should be done.

Pavel
__________________
nohal is online now   Reply With Quote
Old 10-06-2015, 11:28   #9
Registered User

Join Date: Jun 2015
Posts: 7
Re: Excel and openCPN

Great Nohal! I have to convert in the GPS Babel to .gpx or is there other way?

Thank you
__________________
Sergio Rocha is offline   Reply With Quote
Old 10-06-2015, 11:34   #10
Registered User

Join Date: Feb 2010
Location: On the go. Not in Prague.
Posts: 4,016
Re: Excel and openCPN

Sergio...
No GPX, that you would use to transfer historical data. As I understood your description, your data is live and coming continuously, so there has to be "something" that takes care of it. That something has to act as a source of an NMEA datastream, in this case containing just WPL sentences, to which you set up a connection in OpenCPN, which will treat the data as if it was coming from anything else, like a gps, for example.

Pavel
__________________
nohal is online now   Reply With Quote
Old 10-06-2015, 12:44   #11
Registered User

Join Date: Jun 2015
Posts: 7
Re: Excel and openCPN

OK. So I have to connect as a GPS! I will dig into these!

So I Don't have to save anything as a new position is coming
__________________
Sergio Rocha is offline   Reply With Quote
Old 10-06-2015, 12:54   #12
Registered User

Join Date: Feb 2010
Location: On the go. Not in Prague.
Posts: 4,016
Re: Excel and openCPN

Your connection can be anything - serial, TCP, UDP - depends on what is that thing that receives and processes your data and what is more convenient for you to implement. You don't need to think about a GPS - it is just an example of another device which also produces a NMEA data stream.
__________________
nohal is online now   Reply With Quote
Old 10-06-2015, 14:39   #13
Registered User

Join Date: Jun 2015
Posts: 7
Re: Excel and openCPN

Thank you Nohal!

Is there anyway I can use the UTC in that position?

Thank you for your prompt response
__________________
Sergio Rocha is offline   Reply With Quote
Old 10-06-2015, 14:59   #14
Registered User

Join Date: Feb 2010
Location: On the go. Not in Prague.
Posts: 4,016
Re: Excel and openCPN

Sergio...
No, there is no way to include a timestamp in the WPL sentence, it gets registered with the time it is received.
If you need to send data which is not current, you would have to use a different approach.

Pavel
__________________

__________________
nohal is online now   Reply With Quote
Reply

Tags
enc, opencpn

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Expenses: Excel Spreadsheet, Please MarkJ Dollars & Cents 19 22-12-2009 04:27
Excel Provisioning Spreadsheet MV Provisioning: Food & Drink 52 27-07-2009 16:14



Copyright 2002- Social Knowledge, LLC All Rights Reserved.

All times are GMT -7. The time now is 04:20.


Google+
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Social Knowledge Networks
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.

ShowCase vBulletin Plugins by Drive Thru Online, Inc.