AN1

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 your video 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, and resize 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, and configure it as follows:

Text Capture 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 BOTTOM of 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. Make sure that the topmost line has the desired column headings.

(Note: Decomposing a single start-stop time field. If the source data has start-stop times in one column, use the text editor to have the word "Start" appear over the first digit of the start time, and the word "End" line up with the first digit of the ending time, because these are going to become separate columns.)

Scroll down through the document, and look for any rows that 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 delimited field 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).

(Note: Decomposing a single start-stop field. You will also have to add markers at the beginning and the end of the Start time and End time, such that the any separator between them is delimited as a separate field, which we will later tell Excel to ignore. In other words, if you need to get rid of a marker between two values so you can split the column, just create a column around the market, and then tell Excel to ignore the "column".)

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".

(Note: Decomposing a single start-stop field. The exception being the narrow one between the Start and End time, containing a hyphen/colon/period or whatever separator might have been used, which you should tell Excel to completely ignore.)

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.

'-----------------------------

Option Explicit

Sub FixData()
Dim rData As Range
Dim lRow As Long
Dim sStart As String
Dim sEnd As String
Dim sRemarks As String

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

Exit Sub

End If

Set rData = rData.Resize(rData.Rows.Count, rData.Columns.Count + 1)

' the following assumes the frequency data is in column 2
' and the start/end data is in columns 3/4. Adjust your script
' according to the exact requirements for your data project

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)

End If

sEnd = rData(lRow, 4).Value

If InStr(sEnd, ".") = 3 Then

rData(lRow, 4).Value = Left$(sEnd, 2) & Right$(sEnd, 2)

End If

' the following assumes that there is information in a column called Notes
' which contains text that can be used to create a new Modes column
' Use the example below to create your own script according to you needs.
' The sample assumes there is a Notes field in column 7 that contains information
' from which a Mode column can be created as column 10.

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"

End If

Next lRow

rData.Select

End Sub

'-----------------------------

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:

In the "Select or Confirm Fields for the Database" screen, make sure that the "Frequency" field is set to date type "Number". All other fields, including the Start and End time field, should be type "Text". Also, designate picklist fields according to your preferences. In the "Associate Data Fields with Control Functions," make sure that the Frequency is specified appropriately in kHz or whatever the source data represents. In the "Associate Database Modes with ERGO Generic Modes" screen, map the Modes that you created in the macro script.

12. Press Finish, open up your database, and enjoy!

Your comments and suggestions are welcome to ergo@swldx.com

Hit Counter