ogden air logistics center. purpose of excel2fv many agencies produce point lists of different data...

Ogden Air Logistics CenterOgden Air Logistics Center


Purpose of Excel2FV

• Many agencies produce point lists of different data (target lists, force locations, etc.) in either Excel or delimited text format.

• You can manually enter that data into FalconView or let the Excel2FV tool do the work for you.

CAUTION The tool is NOT certified. Verify your results!


Getting Started

• Ensure you Macro Security is set to Medium.– Excel2FV uses Macros to run the

Visual Basic code.– Go to Tools > Macro > Security and

ensure the setting is Medium.

• The Excel2FV.doc file is the program and runs the program by using an embedded macro.

• PFPS Update Tool must be installed!


Enable Macros

• Open the Excel2FV.doc to run the program.• You must Enable Macros for the program to run.• If you don’t get this window then check your security

setting– It should be set to Medium.


Run Macro

Click HERE to go to C2PC slides.

• Click the Convert Excel or Text File box to run the program for Excel and text files.– Local Points– Drawing Files

• For C2PC files, only a Drawing file can be created.


FalconView must be open.

• Select the Import Type – Local Point database or Drawing file

• Click the Source button and browse for the file you want to convert.– Default format is an .xls file, but

you can also choose a tab delimited text file.

• Select the Destination (Save location) of the .lpt file.


– Cardinal direction (N/S or E/W) can be at either the beginning or end of numeric value.

• N45 15.6 W112 04.2

• 4515.6N 11204.2W

• 4515.6N11204.2W

• MGRS

• Specify what data is in each column.– Use the dropdown to match ID, Lat-Long, Description, etc.– Use Comments as the designation for all non-specific fields.

• Select Comment # for your column that represents your Groups.• We will specify the Groups using the Set Filters button.

• Once the .xls file is “read”, the number of columns will automatically adjust in the program to match the Excel file.

• Select the Coordinate Format your file is in.


Setting Filters

• Click the Set Filters button to specify your groups.

• In the Filter Column dropdown, select the column number that contains your groups.

• Enter the Filter Text you want to filter by and the group name (Point Group) you want to assign it.

– See next 2 slides for specifics on this.

• Click Done! to apply and close.


Filtering

• The data you are working with might only contain descriptions of points and not actual group names.

• By filtering for specific text within that Description column you can designate specific groups.

– Ex. All items in Comment 1 column that contain the text “red” (Redding, Stored, etc.) would be assigned to the Red group.

• There is a hierarchy of filtering based on the Filter number (1, 2, 3…).– Ex. If your second filter was “store”, then the comment “stored”

would also be assigned to the Red group because that filter was applied first.


Filtering

• You can search for several different strings by separating them with commas. – i.e. “Church, Mosque, Synagogue” will group any record

containing any of the three strings.

• You can do searches that are limited to the start of a string using the ~ character. – i.e. A search for “74~” will find 74556 but not 56474.

Similarly, searching for “~74” will search for records that end with 74 only.

• The Auto Filter button will automatically fill in your filter fields based on all the different names it finds within the designated column.– Works for the first 1000 points.


Filtering

• Use the Reset Filters, Groups, and Icons buttons to return those field values back to the default ones.

• The Done! button will apply all settings and close the window.– Clicking on the in the top, right corner will

do the same thing.


Save & Open Settings

• Data that’s distributed on a regular basis will almost always stick with the same format.

• Use the Save Import Settings to save your settings for filters, column format, and coordinate format.– Files get saved as *.t2l files.

• *.t2l = Text 2 Local Point – original program.

• Use the Open Saved Settings to open previously saved *.t2l files.


Create!

• Click the Create! button to create the *.lpt file.• You will get this message.

– If you have multiple Excel sheets or documents open, ensure the one that contains the points is the top-most one.

– Click OK.

• Import Summary Window– Will tell you how many points were

imported into the new *.lpt file, how many failed, and how many were skipped.


Verify Your Data!

• Open PFPS Database Admin

• Go to File > Local Database > Set Active Database…

• At the DBA32 window, select Yes to change to new database.

• Select the *.lpt file you just created– Browse if saved to another location.

• Highlight a group and select Edit.– Verify point data, check for incorrect

Lat/Longs, etc.

• Repeat for remaining Groups.


Display Points in FalconView

• Open Overlay Options in FalconView.– Go to Local Points “Tab”.

• Check the Group Names you want to display on the map.– Default is none checked.

• Adjust Display Threshold as desired.

• Check the Local Points On box and click OK to display points on map.


Converting Drawing Files

• This function will create small drawing objects at the points.– The process of selecting a Source, Destination, and designating

columns remains the same as with Local Points. – C2PC files (*.mgc) are converted using the next process.

• Choose from many different shapes, colors, and fill types.

• A small circle designates the center of the object.– With Plot CenterPoint

checked.


• Drawing specific settings get applied.– Fill Pattern, Symbol Size, Symbol Type and Color– Symbol Color and Type only apply if no match is

found under Set Filters.

• Click Set Filters to set specifics for each Group.


• Set Filter Text, Symbol Type, and Color for each Group.

• Click Done! when ready.

• Save settings in main GUI.– Save Import Settings


Drawing File Overrides

• You can designate a column that will be used to override the drawing defaults or import settings.

• Column can contain any or all characteristics of the object to override.– “5NM green pentagon crosshatch” in the

designated column will override all defaults with these parameters.

– If you just “Yellow crosshatch” the default size and shape will be used and only the color and fill characteristics will be modified.

– Override parameters can be entered in any order. If multiple entries are present (blue 5NM green), then the last one (green) will be used.


• Click Create! to generate the drawing file.

• When complete you will be asked if you want to open the new file in FalconView.– Will launch FVW if not

already open.


Draw File Areas

• If you have multiple Lat/Longs or MGRS coordinates in the same cell an area will be generated.– Also works with Lat/Longs in

separate columns.

• Ex. If you have a Lat/Long cell with 3 coordinates a triangle will be created.


Using Highlighter Fill

• Be cautious when using Highlighter to fill the drawing areas, especially if processing a large number of points.

• There is a substantial loss in performance when highlighter is used.


Local Point Manager

• Allows you to move or copy groups between Local Point files.

• Keeps your Local Point icons and links intact.– PFPS Database Admin

does not.

• Changes are applied immediately. – No save button.


• Use the Change button to select a current Local Point file.

• The New button is used to create a new Local Point file.

• Use the arrow buttons to copy individual groups or all groups. The Move button will move the group from one file to the other.– Number of point and links

transferred will be displayed each time an arrow button is selected.

• The Rename button is used to rename groups that are highlighted.


Converting C2PC Files

• You now have the option of converting C2PC Magic (*.mgc) files or C2PC Threat Export files.


• The GUI is very straightforward.• Select your Source

– (*.mgc file)

• The Destination will fill in automatically based on the name and Save Location window selection.

C2PC Magic Files


• If you want to use anything other than the default settings in FalconView drawings, check and set the 4 Font Overrides settings.– Size– Fore Color– Back Color– Box Style

• Click the Convert File! button when ready.

Click HERE to go to go back to Run Macro slide.


C2PC Threat Export Files

• You will need to run C2PC and save the threat file as an *.mdb file.

• Select this file as you Source.

• Enter a Destination for the resulting FalconView *.thr (or Drawing) file.

• Click Convert File!

• Use the Geo Filtering to “crop” the *.mdb file.


Convert OC-135 INS Route

• The OC-135’s Litton INS points are converted into a Local Point file. – Helps you to rapidly create a route or display points in

FalconView.

• Select the OC-135’s INS log file (*.ins) as your Source.• Click Import Points!


JSTARS Mission Report Converter

• Translates the JSTARS Mission Reports into a Drawing file.

• Save the JSTARS mission report Word document as a text file then use this tool to import all the JSTARS mission reports as a FalconView drawing file.


Any Questions???Questions or Comments to [email protected] or [email protected] or [email protected] or [email protected] (please send example files if any problems

are found)

ogden air logistics center. purpose of excel2fv many agencies produce point lists of different data...

Documents

excel file

drawing file

filter text

text file box

tab delimited text file

text files

filter column dropdown

specific text