CPS import CSV data structure

[ka0nhh]

Does anyone have any information on the necessary CSV data structure needed to import table data like frequencies or personalities into a codeplug using the CPS?

I've looked everywhere and can't find anything. Motorola of course just makes vague references that the data structure must be correct, but nothing about what the data structure is supposed to look like.

 

[SteveC0625]

Does anyone have any information on the necessary CSV data structure needed to import table data like frequencies or personalities into a codeplug using the CPS?

I've looked everywhere and can't find anything. Motorola of course just makes vague references that the data structure must be correct, but nothing about what the data structure is supposed to look like.

Uh, I have to ask the obvious.... What radio and what CPS are you working with here? It kinda helps to know these little tidbits of information before anyone can offer any help.

 

[ka0nhh]

Uh, I have to ask the obvious.... What radio and what CPS are you working with here? It kinda helps to know these little tidbits of information before anyone can offer any help.

Sorry, I thought the data structure would be standard. Anyway, it's an XTS5000 and I'm using ASTRO 25 Portable CPS R18.00.02.

I used the export function to export the entire codeplug to a CSV file which I opened in Excel.

I'm trying to remove most of the 127 conventional personalities originally programmed into the radio (to begin with) by the vendor I bought it from. Most of these personalities are duplicates in function and for some reason the tech programmed each and every conventional channel with a different personality number, which kind of defeated the purpose and made it very difficult to know at a glance what the personality meant without having to open it up and peruse it.

I only need a few personalities for HAM stuff, but there is no way to bulk delete personalities, it has to be done one by one. I edited the personalities list in Excel to remove the redundant personalities and then tried to overwrite the existing feature, but it failed.

First it said it didn't have the programming version header, so I cut and pasted that into the first record row, then it said it was expecting a "feature" header, which is there, but a couple of rows down. I gave up last night when my eyes wouldn't uncross.

I deduce that perhaps the export program does not export a version that can be directly reloaded into the codeplug on the computer (which I tried experimentally) but somehow changes the formatting enough to fail a reload.

I'm thinking that the blank rows between actual data rows may be the problem, but that involves manually deleting hundreds of blank rows and I don't want to do that till I know if that's the problem.

I would think that somewhere there is a data standard cheat sheet and/or instructions that describe how to format the data in Excel CSV files so they will import, but can't find any reference online.

Any help would be appreciated because I would like to learn how to set up CSV data for HAM and other (authorized) frequency lists as well as just knowing how the import system works.

 

[Citywide173]

there is no way to bulk delete personalities, it has to be done one by one.

Welcome to Motorola CPS....the above quote is your answer.

 

[SteveC0625]

OK, very helpful info.

Before you do anything else, look in the Program Files area of your hard drive and into the Motorola area where your CPS files and executables are stored. Many of the different CPS packages include sample codeplugs for each possible model number and tanapa. If there's a samples folder and you can find the right codeplug, load it into the CPS. It should be a barebones single personality codeplug. Shoot that into your radio, and you've got a nearly blank radio ready for you to add your needed personalities and such. Don't for get to save a copy of the existing codeplug just in case something unexpected happens.

Then you can export a .csv and examine it to see if you can do what you want.

 

[ka0nhh]

OK, very helpful info.

Before you do anything else, look in the Program Files area of your hard drive and into the Motorola area where your CPS files and executables are stored. Many of the different CPS packages include sample codeplugs for each possible model number and tanapa. If there's a samples folder and you can find the right codeplug, load it into the CPS. It should be a barebones single personality codeplug. Shoot that into your radio, and you've got a nearly blank radio ready for you to add your needed personalities and such. Don't for get to save a copy of the existing codeplug just in case something unexpected happens.

Then you can export a .csv and examine it to see if you can do what you want.

No such luck. I don't have any sample files.

I've been diddling around with CSV files all day long today. I couldn't drag and drop anything, it always gave me the o with the slash whenever I tried to drag from one CPS to another. Don't have a clue why.

I had two different versions of the code plug I was trying to work on in two different places using my iCloud to access the codeplug, but somewhere along the way I opened the wrong one and did a bunch of data entry into the frequencies fields in one, and a bunch in the other. I was trying to figure out how to merge the two without the drag and drop and managed to do it with the export/import feature so I got the data I'd entered in the wrong plug into the right one.

Then I tried to use Excel to cut and paste frequency lists for HAM repeaters from here by exporting just the zone lists using the export function, opening them in Excel, massaging the RR data and cutting and pasting it into the codeplug zone frequency list I'd exported. When I got everything looking just like it did when I exported it, with the new RX/TX and PL frequencies in place, I tried to import that .CSV file into the codeplug, but it keeps failing on the first line, saying it's expecting the file format version, which is there, and aborting the import. It also says it's expecting a "feature" header, which is also there, but the importer isn't seeing it for some reason.

The Excel file looks identical to the one I exported with the changes I made, but it simply will not import.

I even tried deleting all of the existing zone frequency lists except the FPP one, which I cannot seem to delete no matter what, and importing again, but no joy.

I really would like to not have to key in everything by hand, I expect to input at least 150 HAM repeater and simplex channels and that's a daunting task.

Any suggestions?

 

[ka0nhh]

So, a bit more data. It seems that when I export the zone frequency file to a CSV, place it in my iCloud drive and then re-import it without doing anything to it, it imports fine.

Likewise, if I use a CSV editor in Windows (Ron's Editor), open the file, then save it, it will also import.

However, if I open the CSV file in Excel *on my MacBook Pro, in the MacOffice version of Excel I have, something happens to the file format when it's saved as a CSV and it will not subsequently import to the CPS.

Not sure what the problem is, but clearly there's some piddling difference in the file formats that chokes the CPS.

I've tried opening the Mac-modified CSV file in Ron's Editor and then saving it again, no workee.

I've tried opening the good exported CSV in Ron's Editor, making one minor change, saving it and then trying to import it and it works.

But if I touch the file on the Mac with Excel, it won't load at all.

Wasn't looking to buy Excel for Windows as the only thing I use it for is CPS programming and I'm not even sure that will work. It seems there's no way to "sterilize" a CSV once it's been on the Mac, which is where all the work was done. So, unless someone's got some ideas how to recover the CSV files touched by the Mac so they will work in the CPS, I guess I'm screwed.

 

[jeepsandradios]

Guess im a bit confused here. Why not just open the CPS and make the changes you want there ? Thats the entire ida behind the CPS. This isn't a Baofeng using Chirp, its a real radio with real software. Just go into personalities and delete all you dont need, then go into the zones and set the channel personalities to the ones you want.

 

[SteveC0625]

Guess im a bit confused here. Why not just open the CPS and make the changes you want there ? Thats the entire ida behind the CPS. This isn't a Baofeng using Chirp, its a real radio with real software. Just go into personalities and delete all you dont need, then go into the zones and set the channel personalities to the ones you want.

Um, the OP did mention that he has 127 personalities to delete and over 150 to add. I am pretty sure he wants to take advantage of the editing and formatting power of Excel to make these changes rather than enter them into the CPS one at a time.

If he was dealing with just 10 or 20 personalities, your thoughts would be right on. The larger number of edits required makes Excel much more attractive.

 

[ka0nhh]

Um, the OP did mention that he has 127 personalities to delete and over 150 to add. I am pretty sure he wants to take advantage of the editing and formatting power of Excel to make these changes rather than enter them into the CPS one at a time.

If he was dealing with just 10 or 20 personalities, your thoughts would be right on. The larger number of edits required makes Excel much more attractive.

This is the case. I finally just manually deleted all but 20 of the personalities. I'm stuck with 17 of them because it's an FPP capable codeplug, along with an automatic, non-deletable FPP zone, which is fine.

I then set up the extra 3 personalities as I needed for the various services and was able to assign them as necessary and can add new ones as needed.

The problem now is bulk frequency entry. I've got the MURS and other simple services set up and working but now I want to program zones for ham frequencies and since repeater data for states is available in CSV format from RR I want to take advantage of Excel to massage the RR data somewhat (cutting it down to output, input and PL fields) and then insert the required data into the exported zone frequency list into the appropriate CSV fields for each of the 10 new zones I'm establishing for ham repeaters by state, and then import the modified CSV file into the codeplug. If I can figure out why the Mac-manipulated CSV files won't import I'll save myself a LOT of time and trouble.

The ultimate goal is to give me up to 16 zones, each a different service or ham state/region that are selected by the top knob, that can each have any number of channels in each zone (up to the max of 1000 total), which are scrolled through (chup/chdn) from the front panel display keys.

 

[ka0nhh]

Okay, I found a workaround.

Rather than using Excel on my Mac, I downloaded a CSV editor called "Ron's Editor" and installed it on the Windows box (actually a Mac Mini Server with a Windows partition using Boot Camp for those interested).

I was able to open an Excel workbook on my Mac (laptop), edit out everything but the actual frequency data fields from the total codeplug CSV export from the CPS, save that block of fields and data as a CSV file to my iCloud drive, then IMPORT the total codeplug CPS CSV export in Ron's Editor, then IMPORT the Excel-produced CSV block of ham data into Ron's Editor and APPEND it to the end of the codeplug file, then select that block, cut it, move to the proper zone record and select the first channel line and do a "paste other - Insert" which placed the data block in the proper place.

Then I saved the CSV file, being sure in Ron's Editor to first go to "File-Properties" and DESELECTING the "Save the Header Row with the Document" and "Trailing New Line" checkboxes.

In addition, for some strange reason Ron's Editor interprets the first and second cells of the complete codeplug CSV file as headers when importing the file, so it's necessary to retype the "__File Format Version" and following "0x01" data into the first two cells of the first row.

Once that was done I saved the file normally out of Ron's Editor onto my iCloud drive and then imported only the *Zone Channel Assignment* record (the last on the list) into the open codeplug after first setting the "View-Options-Import Options-Import Behavior to "Overwrite Records."

Then, when I hit "Import from Apps" It did it's thing, gave me a bunch of error messages about fields not needed, and completed the import of the new ham data while also overwriting the existing data for the other zones.

I was then ablet to successfully upload the codeplug to the radio and everything works!

So, from what I can tell, it's something in the way Excel formats a "Save as CSV" file that's the problem, and if one strips out any unseen information by importing and then moving stuff within Ron's Editor, it loses whatever is causing it to fugup the CPS when it comes directly from Mac Excel. I don't know if the same problem occurs with Windows Excel because I don't have it and won't buy it because I refuse to pay Microsoft a monthly fee for software I ought to own forever for one payment...so no upgrades to Windows 10 for me.

I hope this helps anyone having difficult like I had. Thanks for all the advice.

 

[Citywide173]

So, 2 days to figure out a workaround that would have taken 90 minutes at the most to just individually delete the personalities....I am sure there's a use for it, and I applaud your dedication, but it seems a bit anticlimactic.

 

[ka0nhh]

So, 2 days to figure out a workaround that would have taken 90 minutes at the most to just individually delete the personalities....I am sure there's a use for it, and I applaud your dedication, but it seems a bit anticlimactic.

Well, it was a learning experience and I intend to follow up more to try and figure out why a Mac Excel CSV file is different because it's worth doing, and posting, just in case someone else has a similar problem.

You didn't read the whole of what I wrote because it's not just about deleting personalities, it's about figuring out tools to allow efficient bulk data entry into a CPS code plug, which is something that's difficult now.

Learning how to take data tables from some place like RR and format them to paste into a code plug without having to manually key in each frequency seems like both a worthwhile pursuit and a good way to learn how to use the CPS software, which is true in my case as I had to spend a lot of time educating myself in order to make things work.

One important thing I learned is to export the ENTIRE codeplug to CSV and then edit it as need be and then import the entire CSV file back into the CPS import system and THEN pick and choose which specific features you need to import...and that it may just be better to import the ENTIRE CSV code plug every time, even if you only changed one small feature. The import system seems to like to see the whole codeplug in a particular order to pass muster before it will let you actually replace anything in the codeplug itself. I think that was an important lesson to learn that SOMEBODY ought to have thought to put in the help files.

The main question I asked has not yet been answered, which is where one might find information on formatting CSV data to import into a codeplug. I had to deduce it myself by revewing the CSV data I was working with, but it seems more than a little strange that there is not some tutorial and accompanying data templates in Excel format for technicians to use, given how byzantine the CPS is.

Having looked at the data structure I think it may be possible to essentially replace the CPS with an Excel template that both asks for and validates frequency data for both conventional and trunked systems and allows the technician to simply cut and paste blocks of frequencies from other sources directly into a CSV code plug template which can then be imported directly into the CPS, thereby avoiding a lot of headaches.

If I had time, I'd try to create one, but frankly I'm pretty surprised that nobody's already done so given how many Motorola radios and programmers there are out there. I find it astonishing that they all haven't risen up in fury and marched on Motorola headquarters and demanded that they hire some actual software programmers who can create a usable and user-friendly CPS interface rather than the kludge they've been purveying for years now.

I mean really...the fact that you can't re-order zone frequency modules simply by dragging and dropping them within the CPS is just silly. Now I THOUGHT you were supposed to be able to do this, but not in my version.

This is the next century and Motorola ought to come into it with its programming software.

Of course this might be just because I'm using an older CPS on a now-discontinued radio, but it's not THAT old. I was programming HTML web site interfaces in the 90s that work better than what I'm using now.

But thanks for your comment, I really appreciate your input.

 

[Citywide173]

I agree that Motorola needs to come into the next century with their software, but they are Motorola and I don't see it happening anytime soon. Especially where the databases contained at RR are concerned. With respect to Lindsay's business model, I highly doubt Motorola will ever pay the licensing fees that Uniden and Whistler have. They've pretty much taken a "take it or leave it" attitude when asked about the user interface on the CPS. The best you could hope for there is cut and paste.

The other thing is their licensing agreement. Motorola has been known to go after people that didn't purchase the software from them as well as shops that bought one copy and put it into more than one computer. The agreement prohibits decompiling the software, which it could be argued, you have done. I don't agree with their approach to their software, but once again, they are Motorola and do what they want.

 

[ka0nhh]

I agree that Motorola needs to come into the next century with their software, but they are Motorola and I don't see it happening anytime soon. Especially where the databases contained at RR are concerned. With respect to Lindsay's business model, I highly doubt Motorola will ever pay the licensing fees that Uniden and Whistler have. They've pretty much taken a "take it or leave it" attitude when asked about the user interface on the CPS. The best you could hope for there is cut and paste.

The other thing is their licensing agreement. Motorola has been known to go after people that didn't purchase the software from them as well as shops that bought one copy and put it into more than one computer. The agreement prohibits decompiling the software, which it could be argued, you have done. I don't agree with their approach to their software, but once again, they are Motorola and do what they want.

Why Motorola would have to pay Uniden or Whistler anything is beyond me. I haven't even discussed them or their scanners. I'm discussing Motorola radios.

With respect to RR, all I'm doing is downloading the system data and frequencies from RR into a CSV file, which is exactly what I paid my premium subscription to be able to do. What I do with that data is nobody's business but mine. Motorola can't control where I get channel information or in what format.

This is the commercial and professional Motorola forum. Why would you assume or even imply that the CPS I'm using isn't fully licensed? Are you calling me a criminal? I bought a brand-new radio from an authorized dealer and I'm programming only frequencies that I'm authorized to use. It is possible for someone to use an authorized and licensed CPS belonging to someone else is it not? Large radio shops have multiple employees, all of whom are authorized to use the CPS. Do you suppose it's possible that an Amateur Radio licensee might also be an employee of a radio shop with a licensed CPS? Or is it possible that I might be friends with someone who is a radio shop owner with a licensed CPS who allows me to use his computer during off hours? I find your implications offensive and uncalled for.

And how do you figure I've "decompiled" their software? They provide the CSV export/import feature expressly for the purpose I've used it for; transferring codeplug data. Looking at and modifying the CSV data in an outside program cannot possibly be considered "decompiling" anything.

I don't appreciate your nannyism at all, and I think you should mind your own business unless you have something worthwhile to contribute to the issue at hand, which is figuring out why a Mac Excel file won't load into the CPS.

 

[Citywide173]

I never said Motorola would have to pay Uniden or Whistler. I said they would never pay RR to license database access in a similar fashion to what Uniden and Whistler do, so copy and paste would be the best you could hope for.

You've obviously never had Motorola audit your RSS or CPS installations, which they have the right to do unannounced per your user agreement in the software. I never said it isn't fully licensed, people seem to think that the purchase gives them the right to use the software as they see fit. The sad fact is that you own nothing, you are paying for the privilege to use the software. If you owned it, you could sell it-prohibited per Motorola, if you owned it, you could put it on more than one computer on a single purchase-prohibited per Motorola, if you owned it, Motorola wouldn't be able to audit your use at any point they see fit. I've seen them walk into shops and perform random audits and threaten to pull the shop's MSS status if they didn't pay for what Motorola considered to be violations of "proper use" of the software. It doesn't matter if I think your decompiling or you think you aren't. This entire thread might catch the eye of someone from Motorola, and if they decide your use is inappropriate, there is no defense-you pretty much signed away any "innocent until proven guilty" when you purchased your license.

A MAC is not a PC. I would assume there is something in Excel that indicates the database was manipulated on an "Office for Mac" installation, and that there is some key that binds the original CSV file to the computer that created it and the "Office for Mac" tag invalidates it. I would be willing to bet Motorola considers the CSV export/import option an additional feature of the software and any CSV file created by the software to be covered by the EULA. Even codeplugs you create with the software are not your intellectual property, they are Motorola's. It sees the "Office for Mac" tag and immediately considers it to be from an installation that is not consistent with the EULA.

 

[ka0nhh]

A MAC is not a PC. I would assume there is something in Excel that indicates the database was manipulated on an "Office for Mac" installation, and that there is some key that binds the original CSV file to the computer that created it and the "Office for Mac" tag invalidates it.

That's what I'd like to know. I examined the CSV exported file using a text editor in Windows and didn't see any headers or other visible data that would cause a problem, but I may have to go deeper and examine it with something that reveals all the file header data. An ASCII text file should be an ASCII text file and should display on any system that supports ASCII text files. Therein lies the conundrum.

I would be willing to bet Motorola considers the CSV export/import option an additional feature of the software and any CSV file created by the software to be covered by the EULA. Even codeplugs you create with the software are not your intellectual property, they are Motorola's. It sees the "Office for Mac" tag and immediately considers it to be from an installation that is not consistent with the EULA.

That doesn't explain it. The same file, once opened and resaved in a Windows-based CSV editor, imports just fine, unless you're suggesting that Motorola is in cahoots with Windows to discriminate against Mac-based Excel files, which seems a bit unlikely to me. If things are as you suggest, then I would expect Motorola to encode the CSV file in a manner that would disallow the user from using any program other than Motorola's CPS to touch the data, but then why have the CSV function at all?

Even if the codeplug CSV data itself is considered "proprietary" it doesn't make any sense for Motorola to provide the export/import CSV system at all if customers are not permitted to manipulate the data in the CSV version of the codeplug in a CSV editor. If what you suggest were true then there would be no CSV export/import feature at all and the only way one could edit a codeplug is if it is in the native Motorola CPS file format from within the CPS.

I think it's a fairly simple matter of encoding of the CSV data and that some sort of header information is being added by Excel that causes the CPS to choke. Evidently whatever that information is can be stripped away using a Windows-based editor.

I'd be interested to see if a Mac Excel (xlsx) file, when imported into a Windows version of Excel, and then exported from the Windows version as a .csv file imports successfully. That's my next project.

 

[Citywide173]

That's what I'd like to know. I examined the CSV exported file using a text editor in Windows and didn't see any headers or other visible data that would cause a problem, but I may have to go deeper and examine it with something that reveals all the file header data. An ASCII text file should be an ASCII text file and should display on any system that supports ASCII text files. Therein lies the conundrum.

If Microsoft farmed the Mac project out, it would make the office suite similar but different, which could be where the issue is.

That doesn't explain it. The same file, once opened and resaved in a Windows-based CSV editor, imports just fine, unless you're suggesting that Motorola is in cahoots with Windows to discriminate against Mac-based Excel files, which seems a bit unlikely to me. If things are as you suggest, then I would expect Motorola to encode the CSV file in a manner that would disallow the user from using any program other than Motorola's CPS to touch the data, but then why have the CSV function at all?

Even if the codeplug CSV data itself is considered "proprietary" it doesn't make any sense for Motorola to provide the export/import CSV system at all if customers are not permitted to manipulate the data in the CSV version of the codeplug in a CSV editor. If what you suggest were true then there would be no CSV export/import feature at all and the only way one could edit a codeplug is if it is in the native Motorola CPS file format from within the CPS.

I think it's a fairly simple matter of encoding of the CSV data and that some sort of header information is being added by Excel that causes the CPS to choke. Evidently whatever that information is can be stripped away using a Windows-based editor.

I'd be interested to see if a Mac Excel (xlsx) file, when imported into a Windows version of Excel, and then exported from the Windows version as a .csv file imports successfully. That's my next project.

I would think that the "tag" for lack of a better term, would be applied by the last program to manipulate the file. If you were to edit in Mac Excel, save and open in PC Excel and use the "Save" or "Save As" function it would appear to be from the PC Excel and would load. If you opened it on the PC and closed out by clicking on "X" it may or may not strip the Mac tag.

It may be drawing the experiment out a little too far, but download Open Office on each machine, as that program is unquestionably identical and see if it works.

 

[Firebuff880]

A MAC is not a PC. I would assume there is something in Excel that indicates the database was manipulated on an "Office for Mac" installation, and that there is some key that binds the original CSV file to the computer that created it and the "Office for Mac" tag invalidates it. I would be willing to bet Motorola considers the CSV export/import option an additional feature of the software and any CSV file created by the software to be covered by the EULA. Even codeplugs you create with the software are not your intellectual property, they are Motorola's. It sees the "Office for Mac" tag and immediately considers it to be from an installation that is not consistent with the EULA.

It's really simple, primarily MS DOS style CSV files have a CRLF at as the end of line where as MAC CSV files has a simple CR. and the CPS import code is looking for the CRLF. Additionally, the Code Page may be different so things like accent marks might be encoded differently.

In Excel Save-As you will find three different CSV formats -

Google - MAC CSV verses MS DOS CSV for more details.

 

[Mikek]

I had the same issue with Excel on my Mac not playing nice with CPS. Upgraded to the latest (Office 2016) and have no problems now.

I also do a lot of importing via spreadsheet - it seems like a lot of work at first, but it pays off when you need to make a major change.