Application Note 1 - Importing Data in Electronically Scrollable Format
Is there an easy way to copy and paste a large number of records from a database that Ergo cannot access? Yes. One of our users discovered that the latest version of SnagIt! can capture almost any database in electronically-scrollable format. (Here is the SnagIt! web page.) This product will enable you to capture a datase into a text file. You can then format the text file into a dBase database in Excel, which Ergo can import. SnagIt! has the ability to copy data to a text file as you scroll the source database on-screen.
Important Notice: If the source database is proprietary, you must not use this method to infringe on copyright. We are mentioning this method only to enable you to get full value out of your personal copy of the data. Under no circumstances should extracted data be in the possession of anyone other than the owner of the data source.
1. Download and purchase the SnagIt application. The other tools you will need are a text editor and a worksheet that can import text and export in dBase format, such as Microsoft Excel.
2. Set your monitor resolution to 1280x1024 if yourvideo setup supports it, or at least 1024x768. This is necessary because, at least in some cases, you may only be able to make the source data window as large as the monitor's resolution, and you need all the space you can get in order not to get truncated data.
3. Browse your source data, andresize the window to as large as possible, and resize all the columns so that no data fields are truncated. It may take a couple of trial SnagIt captures to get the column sizing just right (see below).
4. Start up SnagIt if it isn't already running, andconfigure it as follows:
Input: Active Window and AutoScroll
Input Properties: "Foreground the Scrolling Window" should be checked.
Set the Scroll Delay to 300 milliseconds.
Also, for testing purposes, leave "Scroll from Top of Doc" UNchecked.
Output: File .TXT
Filters->Layout: Space Formatted, Remove Blank Lines
5. Do a test capture by scrolling to the very BOTTOMof the source data, and then scrolling up by a few lines or a single page-up. Run SnagIt (SHIFT-CTRL-P), and examine the resulting text file. If some data are truncated, readjust the source column widths and try again, repeating this until everything is just right.
6. Go back to SnagIt and set the Input Properties to"Scroll from Top of Doc". Then go back to the source window, hit SHIFT-CTRL-P, and let the full capture proceed. Depending on the number of data records, the full capture can take an hour or two, or even more, and so you might want to let it run overnight.
7. Open up the snagged data with a text editor. Makesure that the topmost line has the desired column headings.
Scroll down through the document, and look for any rowsthat might not have been captured correctly. I found about 10 rows where there were extra spaces which had to be deleted in order for the data to line up so that it can be imported properly into Excel. Save your changes.
8. Import the text into Excel, using the space delimitedfield option. You will have to do some fiddling with the field delimiter markers, moving some of them to the beginning of a column. If you find an Exccel delimiter showing up in the middle of a column where it should not be, just delete it (double click the marker).
Proceed to the next screen in the import wizard. Click on each column in turn, and designate each one of them, with only ONE exception, as data type "Text".
Finish the importing, and save the document as an .XLS document.
9. You can now run Visual Basic for Applications macro on this worksheet to clean up or enhance the data. The following VBA script is offered as a sample of what you can do. For example, you can format a column as numbers with a single decimal, you can remove the separator character from the Start times and End times, if any; and amazingly, you can add an additional Mode column, and populate it based on reading information in other columns.
Use the following sample as a guide to create your own code in the VBA code window.
Set rData = ActiveSheet.UsedRange
If rData.Columns.Count <> 9 Then 'make sure you have the number of cols the macro is expecting
MsgBox "Data must have 9 columns", vbExclamation
Set rData = rData.Resize(rData.Rows.Count, rData.Columns.Count + 1)
' the following assumes the frequency data is in column 2
For lRow = 1 To rData.Rows.Count
rData(lRow, 2).NumberFormat = "0.0"
sStart = rData(lRow, 3).Value
If InStr(sStart, ".") = 3 Then
rData(lRow, 3).Value = Left$(sStart, 2) & Right$(sStart, 2)
sEnd = rData(lRow, 4).Value
If InStr(sEnd, ".") = 3 Then
rData(lRow, 4).Value = Left$(sEnd, 2) & Right$(sEnd, 2)
' the following assumes that there is information in a column called Notes
sNotes = rData(lRow, 7).Value
If lRow = 1 Then
rData(lRow, 10).Value = "Mode"
ElseIf InStr(sNotes, "USB") > 0 Then rData(lRow, 10).Value = "USB"
ElseIf InStr(sNotes, "LSB") > 0 Then rData(lRow, 10).Value = "LSB"
ElseIf InStr(sNotes, "CW") > 0 then rData(lRow, 10).Value = "CW"
Else rData(lRow, 10).Value = "AM"
10. Run this macro on your worksheet. When it is finished, it will leave all the data selected. Save the document, and then, WITH THE DATA STILL SELECTED, save it as a new document in DBF 4 (dBASE IV) format, ignoring all the warnings about loss of some features. Quit Excel.
11. Start up ERGO 4, Select File->New, and select "Import Custom Database". Follow the instructions in ERGO 4 Help for Import A Custom Database, but also follow these tips:
12. Press Finish, open up your database, and enjoy!
Your comments and suggestions are welcome to email@example.com
ERGO Radio Software Home Page.