Searching for a Number String in Excel or Office

[Randy Tyndall 1087023]

Okay you computer geniuses. I am trying to make AIRAC 1404 work in the freeware iFly B744 for FSX. See my post here. viewtopic.php?f=24&t=64417

 

The instructions say to use the same AIRAC Dataset that the PMDG B744 uses (which I have for FS9). I downloaded the 1404 AIRAC for my PMDG B744 for FS9 and then copied the AIRAC Data into the NAVDATA folder for the iFly. One problem. All the Dataset text files work in the FMC except wpNavAids.

 

After researching the problem, as I indicated above, the problem is in the fact that the iFly FMC only reconizes NDB frequencies such as "333.00" but not "1333.00" and there are NDBs in the PMDG NavAid dataset that have four leading numerics (always beginning with "1") followed by two decimal places and an "N" signifying it is an NDB. A example of the info in the text file follows:

 

AKUJARVI IVALO AKU NDB 68.678389 27.614628432.00N

AASIAAT AS DME 68.719420 -52.792753108.75L

AASIAAT AA NDB 68.723147 -52.784825336.00N

MURMANSK RD NDB 68.743056 32.822222635.00N

MURMANSK R NDB 68.764445 32.7830561290.00N

IPF IPF ILS 68.764722 32.782778110.30T

HALL BEACH UX NDB 68.767361 -81.256278378.00N

MURMANSK IRD ILSD 68.774167 32.761389108.50T

HALL BEACH YUX VORD 68.778325 -81.239400117.30H

IRD IRD ILS 68.794167 32.728056108.50T

MURMANSK P NDB 68.798889 32.7194451290.00N

MURMANSK PF NDB 68.823889 32.673333635.00N

 

If you look at the data, the frequency for the NDB is the last 7 or 8 characters of the entry, depending on whether the frequency begins with 1XXX.XXN or just XXX.XXN. For instance, the Hall Beach NDB has a frequency of 378.00 (the last character in the string ending in "N". The previous numbers are the NDB's hexadecimal geographic flightsim coordinates. You can see they do not space between the last digit of the coordinate and the first digit of the frequency. Now look at Murmansk NDB "P". See the 1290.00N at the end of the string? Those 1XXX.XX frequencies make the entire PMDG wpNavAid text file unreadable by the iFly FMC. The only solution out there that I can find is to delete all the NDBs in the dataset that mirror the 1XXX.XX frequency pattern.

 

To do that I copied the text file and pasted it into a Microsoft Office text file. However, that file is 396 pages long. I did this because the "Find" engine in Notepad (what I will use to delete the offending files) is nowwhere near as useable in Office. Unfortunately, I cannot find the correct search "string" expression to use to find only the text that ends in 1XXX.XXN

 

If I search for ".00N" it finds all NDBs (and only NDBs because of the "N"). I do not know how to make it search for "1XXX.00N". Every expression I have tried results in no returns. The only search expression I can find that represents a single character is the period. I tried "1....00N" and it found nothing. I tried "[:alnum:] 1....00N" and again nothing. I have gone through the entire list of regular search expressions and nothing seems to work. I know I must be doing something wrong, I just don't know what.

 

I sure could use some help...

 

Randy

[Luca Benelli]

try notepad++ on windows... or if you have access to a linux box try with grep, egrep, awk and similars

[Bradley Grafelman]

+1 on using Notepad++ or Notepad2 (the latter being my weapon of choice). In fact, on any of my Windows computers (work or home), even attempting to run the built-in "notepad.exe" program will launch Notepad2 instead.

 

As for a search string, I'd say the following regular expression should suffice:

 

1[0-9]{3}\.[0-9]{2}N$

 

The previous numbers are the NDB's hexadecimal geographic flightsim coordinates.

Minor correction here... there's nothing "hexadecimal" about them - they're just plain decimal numbers. Otherwise, "-81.256278" would appear as "0xc2a28337" or something similar.

 

EDIT: And if you wanted to try sed on for size...

 

sed -ri 's/1([0-9]{3}\.[0-9]{2}N)$/\1/g' thefile.txt

[Randy Tyndall 1087023]

Luca and Bradley,

 

Good news and bad news. First the bad news, because you always end on a positive note whenever possible.

 

The search string you suggested did not work in Notepad, Notepad++, MS Excel, or MS Office. I made sure the cursor was on the first entry of the file(s), made sure "Search Down" was selected, typed in the search string exactly as you typed it....0 hits.

 

The good news. I had never heard of Notepad++ so I found a free download, downloaded and installed it, copied the PMDG wpNavAid.txt file, and pasted it into Notepad++. If you have ever looked at the PMDG data in Notepad, you know there is no such thing as neat columns and rows. However, when I pasted it into Notepad++ everything lined up perfect. Now I know why one poster said "just look for the "N" out of place by one position to the right. It stuck out in Notepad++ like it never did in either Notepad or MS Office.

 

I've already scrolled through 396 pages twice and identified all the offending NDBs. Now to delete them from the wpNavAid.text file and see if the iFly FMC works

 

Thank you so much for your help gentlemen. I'll let you know what the outcome is here and in the above post.

 

Randy

[Bradley Grafelman]

I'll be honest; I only tested it on the command line using sed. Apparently, Notepad2 (and I'm guessing Notepad++) doesn't like the "{n}" fixed repetition quantifier I used for shorthand. You might have better luck with the expanded pattern:

 

1[0-9][0-9][0-9]\.[0-9][0-9]N$

 

EDIT: For example, if I take this data:

AKUJARVI IVALO AKU NDB 68.678389 27.614628432.00N
AASIAAT AS DME 68.719420 -52.792753108.75L
AASIAAT AA NDB 68.723147 -52.784825336.00N
MURMANSK RD NDB 68.743056 32.822222635.00N
MURMANSK R NDB 68.764445 32.7830561290.00N
IPF IPF ILS 68.764722 32.782778110.30T
HALL BEACH UX NDB 68.767361 -81.256278378.00N
MURMANSK IRD ILSD 68.774167 32.761389108.50T
HALL BEACH YUX VORD 68.778325 -81.239400117.30H
IRD IRD ILS 68.794167 32.728056108.50T
MURMANSK P NDB 68.798889 32.7194451290.00N
MURMANSK PF NDB 68.823889 32.673333635.00N

and do this in Notepad2:

 

 

I end up with this (after clicking "Replace All"):

 

AKUJARVI IVALO AKU NDB 68.678389 27.614628432.00N
AASIAAT AS DME 68.719420 -52.792753108.75L
AASIAAT AA NDB 68.723147 -52.784825336.00N
MURMANSK RD NDB 68.743056 32.822222635.00N
MURMANSK R NDB 68.764445 32.783056290.00N
IPF IPF ILS 68.764722 32.782778110.30T
HALL BEACH UX NDB 68.767361 -81.256278378.00N
MURMANSK IRD ILSD 68.774167 32.761389108.50T
HALL BEACH YUX VORD 68.778325 -81.239400117.30H
IRD IRD ILS 68.794167 32.728056108.50T
MURMANSK P NDB 68.798889 32.719445290.00N
MURMANSK PF NDB 68.823889 32.673333635.00N

[Randy Tyndall 1087023]

Good news Bradley,

 

Using your search expression I found 50 entries in the dataset that matched what I was looking for. Notepad++ has the ability to highlight the "Find" so it was easy to spot and delete them. Your expression to "replace all" looks like all it did was remove the offending "1", but I was afraid somehow the FMC would "see", for instance, P at Murmansk but not find the right frequency and would lock up so deletion was a better choice for me.

 

I climbed into the iFly B744 afterwords and had no trouble inserting VORs and NDBs into my FMC. Even did a short flight offline from KBOI to KTWF using "BOI STI BYI" as my plan. Went off without a hitch. Thanks for all your help.

 

Randy

[Bradley Grafelman]

Your expression to "replace all" looks like all it did was remove the offending "1", but I was afraid somehow the FMC would "see", for instance, P at Murmansk but not find the right frequency and would lock up so deletion was a better choice for me.

I guess I just didn't understand your requirements. How do you know which lines to delete and which ones to keep?

 

If you define your find-and-replace criteria well, it would probably be a trivial matter to turn those into a regular expression pattern or two. That way, it would take literally seconds or less to do this "cleanup" activity; both now and for any future AIRAC updates you may wish to use. Either way, glad I could help.

[Randy Tyndall 1087023]

Bradley,

 

The search expression you provided worked just fine for what I wanted and I appreciate your help. I spent my entire three days off trying to figure out how to do it. Even went to Microsoft and almost...almost...paid $36.00 for their "help desk" to tell me how to do it. Once you gave me the expression I spent about 20 minutes finding each line that had the offending 1XXX.00N frequency and deleting it from the text file.

 

I spotted them two ways in the file. First, I had the offending expressions highlighted, which was an option in the Find and Replace menu for Notepad++ so all I had to do was look for a data entry that had a pink highlight over, for instance, "1290.00N". Unfortunately, it also found those NDBS that had their decimal coordinate ending in a "1" since there was no space generated between the end of the corrdinates and the beginning of the frequency. Second, when I copied wpNavAid.txt into Notepad++, each one of the offending lines was indeed, like the fix I related in my original post under Pilot Software, one character too long for the field so the ending "N" stuck out one more space from all the XXX.XXN NDB datasets. Arjun, the guy who posted the fix I referenced, must have been using Notepad++ or Notepad2 but merely called it Notepad, because when I copied the data into the default simple Notepad in XP none of the datasets lined up like he described. I'm at work right now so I don't have access to my Notepad++ file I created to find the culprits, but when I get home tonight I'll edit this and add a screenshot to this post so you can see how easy they were to spot.

 

The "perfect" FIND AND REPLACE option would be to find those offending NDBs and then automatically delete those 50+ lines from the wpNavAid.txt file. That would indeed save me 20 minutes every time I install the current AIRAC for the iFly, but it would also find the NDBs whose coordinate ends in a "1", so hand deletion based on spotting the out of place "N" seems the best solution and your expression got me there. 20 minutes compared to three days of searching is a definite improvement.

 

I cannot thank you and Luca enough for pointing me toward Notepad++ and Notepad2...but I ain't payin' either of you $36.00 either.

 

Randy

[Bradley Grafelman]

So all of the lines that have this "extra" 1 character (legitimately - not the false positives where the "1" just happened to be the last digit in the longitude value) are actually to be deleted since they are for some reason duplicates?

 

In that case, a better pattern might be:

 

^.+[ -][0-9][0-9]\.[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]\.[0-9][0-9]N$

That would select the entire line that is to be deleted.

 

I'm a software engineer by trade, so laziness is always a virtue (or even a goal) for me (even when not dealing with software )... thus here's the command-line version (again using sed... GNU sed 4.2.2 to be exact):

 

sed -ri '/^.+[ -][0-9]{2}\.[0-9]{10}\.[0-9]{2}N$/d' thefile.txt

[Randy Tyndall 1087023]

So all of the lines that have this "extra" 1 character (legitimately - not the false positives where the "1" just happened to be the last digit in the longitude value) are actually to be deleted since they are for some reason duplicates?

 

No, they are not duplicates. They are valid NDBs with 4-digit+decimal frequencies, mostly in Europe, but scattered throughout the globe.

 

The AIRAC data the iFly B744 uses is not the same as the iFly B737. Unfortunately, Navigraph does not provide a separate AIRAC for the iFly B744, but the format for the iFly B744 is the same as the PMDG dataset. The problem is not with the PMDG AIRAC from Navigraph, it is with the iFly B744 model itself. The iFly is not capable of reading a 4-digit+decimal frequency. What happens is that instead of just ignoring those frequencies the iFly ignores the entire file so you cannot input any data that comes from the wpNavAid.txt file, be they NDBs, VORs, or ILS'.

 

Makes redirects from ATC tough when they say "Direct Thermal" and I say "unable" because the NAV Radio is tuned from the FMC. Its embarr[Mod - Happy Thoughts]ing since even a beginner virtual pilot should be able to track to or from a VOR...should be able I said. Since the entire wpNavAid.txt file is ignored, so are the VOR frequencies. The FMC will not let you move them from the scratchpad to the Active radio slot. Flying airways is tough as well. I got around this by picking a waypoint just prior to or just after the VOR and following the airway to one just before or after the VOR where I wanted to "get off". The LEGS Page was filled with Discontinuities anytime a VOR was part of the airway. Inconvenient as all get out.

 

Deleting those NDB records with a 4-digit+decimal frequency is, to my knowledge, the only way to get the iFly FMC to recognize and use the PMDG data. Losing those 50 or so NDBs out of my NAV data is a small price to pay for the option of having a "close to payware" B744 for FSX. I have the PMDG B744 for FS9 so now I have a choice of platforms for my long-haul trips.

 

Randy