Excel and openCPN

[Sergio Rocha]

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

[jstevens]

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

[champ8242]

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

[Sergio Rocha]

Thank you jstevens and champ8242.

Unfortunally my programming skills are near nule

[Sergio Rocha]

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

[jstevens]

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

[Sergio Rocha]

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.

[nohal]

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.

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

[Sergio Rocha]

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

Thank you

[nohal]

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

[Sergio Rocha]

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

[nohal]

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.

[Sergio Rocha]

Thank you Nohal!

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

Thank you for your prompt response

[nohal]

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