NavData.db3 Editing Tool

[Bell 407]

Greetings All,

I have found that the in-programme database editor of RadarBox proves to be extremely tedious and time consuming having to work cell by cell with only copy and paste functions to assist. Furthermore, I have never been able to successfully develop the skills and knowledge to edit in SQL.

A colleague and I have therefore developed a programme for users of RadarBox to edit their NavData.db3 file in the user-friendly environment of MS Excel.

So, what has been developed is called the NavaData.db3 Editing Tool and it does three things:

(1) Extract the six SQL database tables from the NavData.db3 file into individual MS Excel files.
(2) Edit any of the files extracted in (1) above through the MS Excel 2007 or later environment.
(3) Compile the six files edited in (2) above into a new NavData.db3 file.

As an avid user of RadarBox since 2009, I have always wanted to perform some sort of database management on my key NavData.db3 file and now this tool allows me to do that. I have been running a newly compiled NavData.db3 file since the beginning of November 2011 with no problems and have edited all 6 tables without any issues over 10 times.

So what does this mean going forward for other users of RadarBox?

It is hoped that with this programme users will develop the confidence to easily and quickly edit their NavData.db3 file should they feel the need to. For me it was enjoyable to freely explore each table as extracted from the NavData.db3 file in MS Excel and to develop a better understanding of the contents therein.

A further benefit that can be foreseen is that now users can email copies of their tables to other users for them to view, edit or use information in their own tables. It is also believed that this programme will make it easier for the Database Update Team to release updates in individual MS Excel format instead of via the complex installation and overwrite process that is available at present.

The NavData.db3 Editing Tool can be run using the Java Runtime Environment (JRE). The JRE exists for most operating systems, including Windows, Mac and Linux. The JRE can be downloaded from the Java website (www.java.com).

The NavData.db3 Editing Tool is provided free for private, non-commercial use.

For information about downloading the programme, please visit the website www.earthimage.co.za/NavDataDB3. A detailed User's Manual is also available for your reference.

Your comments about this programme would be much appreciated so please post them here for the programme developers to take note of.

Regards,

[TEDDYBEAR]

Hi Bell 407

Works a treat. One problem to be aware of is that in excel you can have duplicate data in the same columns
ie hexcodes or flight codes etc

Bryan

[Bell 407]

Hi Bryan,

Would the duplicates also not show up in SQL as well?

[nortonbeak]

Thank you for investing a lot of time and effort into this project. It looks very much as if it will prove a  more straightforward file editing tool than delving into SQL. :-)

[Bell 407]

Thank you for investing a lot of time and effort into this project. It looks very much as if it will prove a  more straightforward file editing tool than delving into SQL. :-)

You are most welcome and look forward to any input, problems or improvements that we can make.

[RodBearden]

Great program!

Excel filters are a great way of finding the stuff that has been being discussed about data consistency in the Update thread - like Avia Nova or Avianova etc.

I agree that the possibility of duplicate entries is a problem - I don't know of a quick way in Excel of finding duplicate Mode-S codes, for example. There is a duplicate remover available here http://www.ablebits.com/excel-remove-duplicates/index.php (found through the Excel help system) -  has anybody experienced it?

Rod

[Bell 407]

Thanks Rod, appreciate the postive response.

Will have a look at the link you posted about filters. Personally I don't have a lot of experience with filters so maybe other more advanced Excel users can post some suggestions.

[TEDDYBEAR]

Hi Bell 407

In SQL in field MS (Aircraft) & FN (Routes) it is not possable to insert the same code more than once.
To overcome this problem in excel the follow is available.

Click on a cell A9 in the MS column then find the data validation tool, click on data validation, settings (allow) custom, formula =ISNA(VLOOKUP(A134398,$A$2:A134397,1,FALSE))
Click on error alert, ser style, insert title and error message click OK.
Highlight cell A9, Copy, then highlight column A, Paste Special, Validation & OK.

If you then type in any data that is already in that column you are warned.

That should then copy the validation into every cell in column A.

The same process can be used in the other tables routes etc.

Bryan

[TEDDYBEAR]

Hi Bell 407

In the last item the formula should read =ISNA(VLOOKUP(A9,$A$2:A8,1,FALSE))

Sorry for the mistake

Bryan

[Bell 407]

Thanks Bryan, will give it a try out.

[RodBearden]

Hi Bryan

Many thanks for that - works nicely. I think it's worth pointing out (if I'm right) that it works when you add a new record at the bottom of the list, not if you insert a row inside the list and the existing duplicate is below you.

Rod.

[Netcop]

Very useful program, many thanks!

But I have problem with compilation of а new database file from excel files (without making any changes in the files).

Process stops on very last phase – compilation of Routes table with following error message:

null
java.util.Arrays$ArrayList.<init>(Unknown Source)
java.util.Arrays.asList(Unknown Source)
com.csparksfly.anrbdb3tool.DBTable.ensureRequiredHeadersExist(DBTable.java:99)
com.csparksfly.anrbdb3tool.ANRBdb3Tool.importDB(ANRBdb3Tool.java:339)
com.csparksfly.anrbdb3tool.gui.CreateDBFromDirectoryAction.doThisTask(CreateDBFromDirectoryAction.java:39)
com.csparksfly.anrbdb3tool.gui.ANRBdb3ToolAbstractAction$2.doInBackground(ANRBdb3ToolAbstractAction.java:122)
com.csparksfly.anrbdb3tool.gui.ANRBdb3ToolAbstractAction$2.doInBackground(ANRBdb3ToolAbstractAction.java:118)
javax.swing.SwingWorker$1.call(Unknown Source)
java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
java.util.concurrent.FutureTask.run(Unknown Source)
javax.swing.SwingWorker.run(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
java.lang.Thread.run(Unknown Source)

New database file contains empty Routes table.

[TEDDYBEAR]

Hi Rod

Your point is correct

Bryan

[TEDDYBEAR]

Hi Rod

Try altering the formula to =ISNA(VLOOKUP(A9,$A$2:$A$200000,1,FALSE)) where 200000 (line number) is whatever you want.

Bryan

[Bell 407]

Hi Netcop,

Your error is noted and I will get the programmer to address it for you.