Actions

Difference between revisions of "Excel Copy/Paste Special to Software"

From The RadioReference Wiki

 
(45 intermediate revisions by 4 users not shown)
Line 1: Line 1:
Using Microsoft Excel '''Paste Special feature''' for building data to paste into '''Windows based Scanner Software'''. Open Office and other spread sheets may also have this type of operation.  
+
You can use Microsoft Excel '''Paste Special feature''' to build data that can be pasted into '''Windows based Scanner Software'''. Open Office and other spread sheets may also be capable of this function.  
  
== Generic for all Windows based scanner programming software ==
+
== Copy Data from RadioReference to Excel ==
 +
=== Instructions using earlier versions of Excel ===
  
 
+
These 8 steps are for copying data from either trunking or conventional database pages and pasting into Excel. Once these steps are complete, the data is ready for pasting into other software.   
These 8 steps are for copying data from either trunking or conventional dabebase pages and pasting into Excel for manipulation to ready for pasting into Software.   
+
# Highlight desired freqs from database page and copy.
# Highlight, copy select freqs from database page.
 
 
# Click in Excel Cell A1 and select Paste Special.
 
# Click in Excel Cell A1 and select Paste Special.
 
# Click Text and Ok.   
 
# Click Text and Ok.   
# Re-structure freqs vertically and format to number with 4 decimals. Note the controls are still marked. Unmark these prior to copy/paste to software.  EDACS or LTR freqs will need to be spaced in cells as listed in the DB LCN order.
+
# Re-structure freqs vertically and format to number with 4 decimals. Note the asterisks (which indicate control channels) are still marked. Remove the asterisks prior to copy/paste to software.  If programming an [[EDACS]] or [[LTR]] system, the frequencies must be kept in [[Logical Channel Number]] order as shown in the database. This is indicated by a small 2 digit number before the frequency. These numbers should be edited out as the software will handle this numbering.
# Highlight and copy section/agency of talkgroups.
+
# Highlight desired section/agency of talkgroups and copy.
# TG's after Paste Special to Excel. Delete unneeded column B.
+
# Use Paste Special to copy talkgroups into Excel. Ignore the data in column B. These are hex values for the talkgroups in column A. They are only used by professional radios, such as Motorolas.
# Highlight, copy selected sections from conventional page as in Screenshot 5 and Paste Special into Excel.
+
# Highlight and copy selected sections from conventional page similar to step 5 and Paste Special into Excel.
# Excel columns deleted and restructured for copy to software.  Note: Input, License, Type and Mode columns deleted. Freq column formatted to number with 4 dec and Descriptions shortened and inserted before tones. PL removed from tones and reformatted to number with 1 dec.   
+
# Delete unneeded columns and restructure as needed for the desired software.  Note: Input, License, Type and Mode columns deleted. The freq column is formatted to number with 4 decimal places. Descriptions must be shortened. The column is cut and inserted before tones. Reformat PL/DPL tones as needed. PL tones require 1 decimal point, DPL tones require format to text and if needed, use leading 0 (ie 023).   
 
<gallery>
 
<gallery>
 
Image:1_generic_trunk_freq.gif|Step 1  
 
Image:1_generic_trunk_freq.gif|Step 1  
Line 23: Line 23:
 
Image:8_Restructured_for_copy_paste_to_software.gif|Step 8  
 
Image:8_Restructured_for_copy_paste_to_software.gif|Step 8  
 
</gallery>
 
</gallery>
 +
=== Instructions using Excel 7.0 ===
 +
# Open Excel
 +
# Click on ''Data/From Web''. See figure 1
 +
# Bring up the desired RR page.
 +
# Click on the lower right corner and drag the screen down to the right for a larger view. Some dial up modems may require you to be online to perform this task. Note that there are yellow boxes with arrows pointing to various areas.
 +
# Click on the areas that contain frequencies you wish to import. The box changes to green and the arrows become check marks. If you change your mind about an area, simply click it again, and the box reverts back to the original yellow. See figure 2
 +
# Click the button marked '''Import''' found at the extreme bottom right of the screen.
 +
# Edit the resulting worksheet as required. See figure 3
 +
# The same method can be used for importing trunked data. Figure 4 shows the web page with the arrows and check marks - again, those areas marked in green with a check mark will be imported.
 +
# Figure 5 is the result of importing the data. Note that the data will need to be edited to remove the asterisks from the control channels, and take out whatever is not needed (for example, the hexadecimal talkgroups). See items 4 and 8 from the section above for hints on what needs to be reformatted.
 +
<gallery>
 +
Image:Excel_07_toolbar.gif|figure 1
 +
Image:Excel_07_mapconv.gif|figure 2
 +
Image:Excel_07_workconv.gif|figure 3
 +
Image:Excel_07_maptrunk.gif|figure 4
 +
Image:Excel_07_worktrnk.gif|figure 5
 +
</gallery>
 +
 +
 +
----== Copy the Data from Excel into Software ==
 +
=== Saving Excel file to CSV format for importing to ScanControl Software ===
 +
Note that this function is only available in the ''full version'', not the ''lite version''. This
 +
function is explained in much more detail in the [http://www.scannersoft.com/ ScanControl] owner's
 +
manual.
 +
# In Excel save the desired worksheet in CSV format ''(file/save as)'' so the import recognizes it (figure 1).
 +
# In ScanControl main page click on Import Data Files button (figure 2).
 +
# Next, select the drive, directory and file. Select 'CSV File (Excel and others)' from the radio buttons under 'Data File Format'. Click the 'Map CSV' button on the right of the files box (figure 3).
 +
# In the next screen, choose 'All' from 'File Contents' (figure 4). 
 +
# The 'column map' area is used to inform Scan Control what data exists in what column. Select each column as shown right below the 'column map', then select the 'field type'. Do this for all columns in the file, including PL/DCS if applicable. Note that as you select the field type, the column header changes to match it. This process is referred to as 'mapping' the file.
 +
# Unique to Scan Control is the ability to save all the column names in a file that can be reloaded time and again. This is handy particularly if you are mapping a large number of records, such as for the DMA scanners. If you wish to use this function, hit 'Save Map' on the right. A dialog box is shown; put the name of the map here. When you return and you wish to use the map you previously saved, use the 'Load Map' button. Select the desired map and all the columns will be named exactly the way you left it.
 +
# On the bottom left, select the range of rows you wish to import. If the CSV file had headers, you can tell SC to ignore them by selecting the correct starting row.
 +
# If importing a trunk system, as in this example, select the starting location and the default tag you wish to use. The Sloc (Scan list location) is written in a notation that indicates the number of the bank of the radio being programmed, the scan list number and talkgroup number. For conventional systems select the bank (group) you wish to use, the starting channel number and default alpha tag. Press 'Import' on the right bottom of the screen
 +
# You will get several warnings about overwriting a bank, and the fact that the import cannot set the type of trunk system or bank names. These must be set manually.
 +
# Once you have imported the data, you will get a prompt asking whether you wish to continue. If you hit 'yes' you will be returned to the import screen
 +
# If you hit 'no', you will be returned to the same screen as in figure 3.
 +
# A prompt will appear stating the import function was complete. Hit 'OK' and you will return to the same screen as in figure 2.
 +
# In the main screen, highlight the bank (group) you just built and click the 'Edit Banks' button. Fill in something for the bank name. If a trunked bank is being programmed, you should also select the correct trunk type from the pulldown, and put a check mark in each of the trunked channels under the 'T' column (figure 5).
 +
# You may edit your scan lists, and give them names by clicking the 'Edit ScanLists' button (figure 6).
 +
# If you are connected to the scanner while building data, you can now program your bank by clicking on the 'Program' button
 +
 +
<gallery>
 +
Image:Excel_saved_as_csv_for_ScanControl.gif|Figure 1
 +
Image:Click_Import_Data_Files.gif|Figure 2
 +
Image:Select_CSV_file.gif|Figure 3
 +
Image:Mapping_CSV_data.gif|Figure 4
 +
Image:SC_Bank_Editor.gif|Figure 5
 +
Image:SC_Bank_Scan_List.gif|Figure 6
 +
</gallery>
 +
 +
=== Structuring Excel file for pasting into ARC250D Software ===
 +
ARC250D and likely other ARC software has a feature called Automatic Data Import (ADI) which enables one to import frequencies from any format and list them in the open software main page in vertical format eliminating the need for Paste Special in the generic section above for frequencies.  All other data will need to be set by the user. Excel can receive the freqs from ADI as a 'Save to Clipboard' function, thus all data on the open software page can be prepared in Excel and copied to the page. 
 +
# Structure a bank for ARC250D software using Excel as shown in step 1 below.
 +
# Copy Cells A1 through K1 and down through last freq line then paste into open ARC bank page.
 +
# Collect and Paste Special desired Talkgroup data as in steps 5 & 6 in Generic section above.  After deleting unneeded columns, copy the ID's & Alpha tags and paste into open ARC250 Trunk Id List.  Hint: If talkgroups are structured in Excel in groups of 10 one can paste up to 100 ID/w/tags in the open dialog. 
 +
# Use steps 7 & 8 in above generic section for collecting and sorting conventional data then set tones as indicated in the screenshot for step 4 below.
 +
<gallery>
 +
Image:Excel_trunk_system_structure_for_ARC.gif|Step 1
 +
Image:ARC250D5.gif|Step 2
 +
Image:ARC250D9.gif|Step 3
 +
Image:Adding_conv_freqs_with_tones.gif|Step 4
 +
</gallery>
 +
 +
=== Structuring Excel file for pasting into Win9x Software ===
 +
* Step 1 (below) shows a screenshot of a finished worksheet after using all steps in the Generic Copy/Paste Special section. This file is ready for copy/paste into an open Win9x dialog.  Note: PL/DPL cells in Excel file will need to be formatted as text with leading 0's.
 +
* It should be noted that Win96, Win97 and Win99 can import CSV files, but require extensive reformatting before they will recognize the file. See the [http://www.starrsoft.com/software/CSVSpec.txt file specification] for more information.
 +
====Win92====
 +
# Open a new template or bank and set bank mode (MOT in this sample). Copy trunk frequencies first and click to 00 Alpha Tag in frequency area and select the Paste Icon. 
 +
# Copy conventional frequencies and click in any location after trunk freqs and select paste Icon.  PL/DPL settings will need to be manually set.
 +
# Copy ID's and tags from the Excel file and paste into talkgroup section.
 +
====Win93/95====
 +
# Open a new template or bank and set bank mode (MOT in this sample). Copy trunk frequencies first and click to 00 Alpha Tag in frequency area and select the Paste Icon.
 +
# Copy conventional frequencies and click in any location after trunk freqs and select Paste Icon.  PL/DPL settings will need to be manually set.
 +
# Copy ID's and tags from the Excel file and paste into talkgroup section.
 +
====Win96====
 +
# Open a new template or bank and set bank mode (MOT in this sample). Copy complete frequency section from Excel file in step 1, click in cell 00 Alpha Tag then right click and select paste.  PL/DPL settings will also paste into Win96.
 +
# Copy ID's and tags from the Excel file and paste into talkgroup section.
 +
====Win97====
 +
# Open a new template or bank and set bank mode (MOT in this sample). Copy complete frequency section from Excel file in step 1, click in cell 00 Alpha Tag then right click and select paste.  PL/DPL settings will also paste into Win97.
 +
# Copy ID's and tags from the Excel file and paste into talkgroup section in Win97
 +
<gallery>
 +
Image:Excel_file_for_copy_to_win9x.gif|Step 1 Excel complete
 +
Image:Win92.gif|Step 2 Win92
 +
Image:Win95.gif|Step 3 Win95/93
 +
Image:Win96.gif|Step 4 Win96
 +
Image:Win97.gif|Step 5 Win97
 +
</gallery>
 +
 +
===Use Excel copy/paste data into various packages===
 +
These basic steps will work with the following software. The images shown are typical examples
 +
of operations and the results.
 +
* Scancat Lite Plus
 +
* Uniden E-Scanner
 +
* Freescan
 +
* AR86ctrl
 +
<br>
 +
# Copy all frequencies and associated alpha tags using Ctrl-C Fig 1 for either software.
 +
# Click in the desired Frequency cell, then press Ctrl V Fig 2 or 3. 
 +
# Copy ID's and associated tags using Ctrl-C Fig 1 for either software.
 +
# Click in desired Scan List location cell under Group, then press Ctrl V Fig 2 or 3.
 +
# All other settings including PL/DPL will need to be manually set as in screen shot.
 +
<gallery>
 +
Image:Excel_file_for_copy_to_ScanCat_lite_Software.gif|Figure 1 Excel complete for ScanCat Lite
 +
Image:ScanCat_Lite_for_many_Scanners.gif|Figure 2 Complete bank
 +
Image:E_Scanner_Plus.gif|Figure 3
 +
</gallery>
 +
  
=== Pasting collected data into Win9x Software ===
+
[[Category:Software FAQ]]
Continued tomorrow
+
[[Category:Scanners FAQ]]
[[Category:Software Applications]]
+
[[Category:Programming FAQs and Tips for Radio Shack Scanners]]
[[Category:FAQ]]
+
[[Category:Programming FAQs and Tips for Uniden Scanners]]

Latest revision as of 05:39, 25 April 2018

You can use Microsoft Excel Paste Special feature to build data that can be pasted into Windows based Scanner Software. Open Office and other spread sheets may also be capable of this function.

Copy Data from RadioReference to Excel

Instructions using earlier versions of Excel

These 8 steps are for copying data from either trunking or conventional database pages and pasting into Excel. Once these steps are complete, the data is ready for pasting into other software.

  1. Highlight desired freqs from database page and copy.
  2. Click in Excel Cell A1 and select Paste Special.
  3. Click Text and Ok.
  4. Re-structure freqs vertically and format to number with 4 decimals. Note the asterisks (which indicate control channels) are still marked. Remove the asterisks prior to copy/paste to software. If programming an EDACS or LTR system, the frequencies must be kept in Logical Channel Number order as shown in the database. This is indicated by a small 2 digit number before the frequency. These numbers should be edited out as the software will handle this numbering.
  5. Highlight desired section/agency of talkgroups and copy.
  6. Use Paste Special to copy talkgroups into Excel. Ignore the data in column B. These are hex values for the talkgroups in column A. They are only used by professional radios, such as Motorolas.
  7. Highlight and copy selected sections from conventional page similar to step 5 and Paste Special into Excel.
  8. Delete unneeded columns and restructure as needed for the desired software. Note: Input, License, Type and Mode columns deleted. The freq column is formatted to number with 4 decimal places. Descriptions must be shortened. The column is cut and inserted before tones. Reformat PL/DPL tones as needed. PL tones require 1 decimal point, DPL tones require format to text and if needed, use leading 0 (ie 023).

Instructions using Excel 7.0

  1. Open Excel
  2. Click on Data/From Web. See figure 1
  3. Bring up the desired RR page.
  4. Click on the lower right corner and drag the screen down to the right for a larger view. Some dial up modems may require you to be online to perform this task. Note that there are yellow boxes with arrows pointing to various areas.
  5. Click on the areas that contain frequencies you wish to import. The box changes to green and the arrows become check marks. If you change your mind about an area, simply click it again, and the box reverts back to the original yellow. See figure 2
  6. Click the button marked Import found at the extreme bottom right of the screen.
  7. Edit the resulting worksheet as required. See figure 3
  8. The same method can be used for importing trunked data. Figure 4 shows the web page with the arrows and check marks - again, those areas marked in green with a check mark will be imported.
  9. Figure 5 is the result of importing the data. Note that the data will need to be edited to remove the asterisks from the control channels, and take out whatever is not needed (for example, the hexadecimal talkgroups). See items 4 and 8 from the section above for hints on what needs to be reformatted.



== Copy the Data from Excel into Software ==

Saving Excel file to CSV format for importing to ScanControl Software

Note that this function is only available in the full version, not the lite version. This function is explained in much more detail in the ScanControl owner's manual.

  1. In Excel save the desired worksheet in CSV format (file/save as) so the import recognizes it (figure 1).
  2. In ScanControl main page click on Import Data Files button (figure 2).
  3. Next, select the drive, directory and file. Select 'CSV File (Excel and others)' from the radio buttons under 'Data File Format'. Click the 'Map CSV' button on the right of the files box (figure 3).
  4. In the next screen, choose 'All' from 'File Contents' (figure 4).
  5. The 'column map' area is used to inform Scan Control what data exists in what column. Select each column as shown right below the 'column map', then select the 'field type'. Do this for all columns in the file, including PL/DCS if applicable. Note that as you select the field type, the column header changes to match it. This process is referred to as 'mapping' the file.
  6. Unique to Scan Control is the ability to save all the column names in a file that can be reloaded time and again. This is handy particularly if you are mapping a large number of records, such as for the DMA scanners. If you wish to use this function, hit 'Save Map' on the right. A dialog box is shown; put the name of the map here. When you return and you wish to use the map you previously saved, use the 'Load Map' button. Select the desired map and all the columns will be named exactly the way you left it.
  7. On the bottom left, select the range of rows you wish to import. If the CSV file had headers, you can tell SC to ignore them by selecting the correct starting row.
  8. If importing a trunk system, as in this example, select the starting location and the default tag you wish to use. The Sloc (Scan list location) is written in a notation that indicates the number of the bank of the radio being programmed, the scan list number and talkgroup number. For conventional systems select the bank (group) you wish to use, the starting channel number and default alpha tag. Press 'Import' on the right bottom of the screen
  9. You will get several warnings about overwriting a bank, and the fact that the import cannot set the type of trunk system or bank names. These must be set manually.
  10. Once you have imported the data, you will get a prompt asking whether you wish to continue. If you hit 'yes' you will be returned to the import screen
  11. If you hit 'no', you will be returned to the same screen as in figure 3.
  12. A prompt will appear stating the import function was complete. Hit 'OK' and you will return to the same screen as in figure 2.
  13. In the main screen, highlight the bank (group) you just built and click the 'Edit Banks' button. Fill in something for the bank name. If a trunked bank is being programmed, you should also select the correct trunk type from the pulldown, and put a check mark in each of the trunked channels under the 'T' column (figure 5).
  14. You may edit your scan lists, and give them names by clicking the 'Edit ScanLists' button (figure 6).
  15. If you are connected to the scanner while building data, you can now program your bank by clicking on the 'Program' button

Structuring Excel file for pasting into ARC250D Software

ARC250D and likely other ARC software has a feature called Automatic Data Import (ADI) which enables one to import frequencies from any format and list them in the open software main page in vertical format eliminating the need for Paste Special in the generic section above for frequencies. All other data will need to be set by the user. Excel can receive the freqs from ADI as a 'Save to Clipboard' function, thus all data on the open software page can be prepared in Excel and copied to the page.

  1. Structure a bank for ARC250D software using Excel as shown in step 1 below.
  2. Copy Cells A1 through K1 and down through last freq line then paste into open ARC bank page.
  3. Collect and Paste Special desired Talkgroup data as in steps 5 & 6 in Generic section above. After deleting unneeded columns, copy the ID's & Alpha tags and paste into open ARC250 Trunk Id List. Hint: If talkgroups are structured in Excel in groups of 10 one can paste up to 100 ID/w/tags in the open dialog.
  4. Use steps 7 & 8 in above generic section for collecting and sorting conventional data then set tones as indicated in the screenshot for step 4 below.

Structuring Excel file for pasting into Win9x Software

  • Step 1 (below) shows a screenshot of a finished worksheet after using all steps in the Generic Copy/Paste Special section. This file is ready for copy/paste into an open Win9x dialog. Note: PL/DPL cells in Excel file will need to be formatted as text with leading 0's.
  • It should be noted that Win96, Win97 and Win99 can import CSV files, but require extensive reformatting before they will recognize the file. See the file specification for more information.

Win92

  1. Open a new template or bank and set bank mode (MOT in this sample). Copy trunk frequencies first and click to 00 Alpha Tag in frequency area and select the Paste Icon.
  2. Copy conventional frequencies and click in any location after trunk freqs and select paste Icon. PL/DPL settings will need to be manually set.
  3. Copy ID's and tags from the Excel file and paste into talkgroup section.

Win93/95

  1. Open a new template or bank and set bank mode (MOT in this sample). Copy trunk frequencies first and click to 00 Alpha Tag in frequency area and select the Paste Icon.
  2. Copy conventional frequencies and click in any location after trunk freqs and select Paste Icon. PL/DPL settings will need to be manually set.
  3. Copy ID's and tags from the Excel file and paste into talkgroup section.

Win96

  1. Open a new template or bank and set bank mode (MOT in this sample). Copy complete frequency section from Excel file in step 1, click in cell 00 Alpha Tag then right click and select paste. PL/DPL settings will also paste into Win96.
  2. Copy ID's and tags from the Excel file and paste into talkgroup section.

Win97

  1. Open a new template or bank and set bank mode (MOT in this sample). Copy complete frequency section from Excel file in step 1, click in cell 00 Alpha Tag then right click and select paste. PL/DPL settings will also paste into Win97.
  2. Copy ID's and tags from the Excel file and paste into talkgroup section in Win97

Use Excel copy/paste data into various packages

These basic steps will work with the following software. The images shown are typical examples of operations and the results.

  • Scancat Lite Plus
  • Uniden E-Scanner
  • Freescan
  • AR86ctrl


  1. Copy all frequencies and associated alpha tags using Ctrl-C Fig 1 for either software.
  2. Click in the desired Frequency cell, then press Ctrl V Fig 2 or 3.
  3. Copy ID's and associated tags using Ctrl-C Fig 1 for either software.
  4. Click in desired Scan List location cell under Group, then press Ctrl V Fig 2 or 3.
  5. All other settings including PL/DPL will need to be manually set as in screen shot.