RE: Comparing 2 files

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

RE: Comparing 2 files

Logged in as: Guest

arrSession:exec spGetSession 2,2,30370

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> RE: Comparing 2 files
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: << < prev 1 [2]

Message

<< Older Topic Newer Topic >>

RE: Comparing 2 files - 2/2/2006 2:05:44 AM

ehvbs

Posts: 2061
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi hamboy,

sorry about

(a) posting before reading
(b) the ado error

(b) may be caused by calling (the old version of) getdispSqlNice() for an empty recordset.
Try the new version from my last (monster) posting.

ehvbs

< Message edited by ehvbs -- 2/2/2006 2:07:01 AM >

(in reply to ehvbs)

Post #: 21

RE: Comparing 2 files - 2/2/2006 4:30:46 AM

hamboy

Posts: 94
Score: 6
Joined: 7/11/2005
Status: offline

hi ehvbs,

thanks for the monster post... I enjoy the humor (and no i'm not a warden LOL).
My data are actually locations of communication towers and each element ID (ie. P-*) is the ID assigned to a communication tower.
basically map.txt is the data output of each communication tower from 1 system, and report.csv is output of the communication tower from another system.
the objective was to compare the data from both systems to see if there are any discripancies.

After going through your monster posts, I can sort of make sense some parts, but I can't really get everything together.
Your previous post (1/31/2006 8:39:34 AM) works perfectly for report.csv where it lists all of the data in a nice accessible format.

Were you able to do the same for map.csv? I assume it would be easier to work with since there's no "junk" data that needs to be ignored.
Once the two files are in nicely accessible format, map.txt needs to be compared agaiinst report.csv for any discrepancies and output any differences of Cell/Reporting System using on Element ID as key to an "Unmatching" list. If Element ID from map.txt does not exist in report.csv, then it should be outputed to "Not found" list.

Is it possible to post the entire contents of each file in individual code tags so it's easier to make sense what's what?

< Message edited by hamboy -- 2/2/2006 4:35:49 AM >

(in reply to ehvbs)

Post #: 22

RE: Comparing 2 files - 2/2/2006 4:59:49 AM

ehvbs

Posts: 2061
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi Hamboy,

thanks for the info! While I think about it, I propose that you look at Navarre's "file check" topic.
It is about compareing data from two sources about the same items too, and I will post a demo
using (RegExps and) dictionaries to find matching/missing/contradictory information. Perhaps
you will feel more familiar with this approach. (Or you could post a small set of data from your
domain showing the different cases you are interested in.)

ehvbs

(in reply to hamboy)

Post #: 23

RE: Comparing 2 files - 2/2/2006 5:19:53 AM

hamboy

Posts: 94
Score: 6
Joined: 7/11/2005
Status: offline

To assist in the explaination, Lets say after report.csv & map.txt have been formatted:

report.csv sample data



map.txt sample data



Sample Output

(in reply to ehvbs)

Post #: 24

RE: Comparing 2 files - 2/2/2006 5:59:37 AM

ehvbs

Posts: 2061
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi hamboy,

the easy part first: That's my current set of source files:

(1) schema.ini: used by the ADO/Text driver; he/she/it (?) makes the files
    of one directory specified as DBQ in the connection string accessible
    as tables defined by the sections [<filename>] in schema.ini. You may add
    definitions to this file freely as long as you keep the filenames unique.
    Deleting/hiding sections by commenting out the [<filename>] section
    title, however, may break existing code.

; bare bones schema.ini to access hamboy's files - for further information
 ; preliminary version just to test if ADO/Text can be used; column defs will
 ; be reworked.
 ; further information:
 ; http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
 
 ; First approach disables by hiding the [filename] line
 ;[map.txt]
 ; |"P-MON-OMP-1:314" : "P-PQ0506"|
 ;   ------15-------     ----8---
 ;  X               XXXXX        X
 Format=FixedLength
 ColNameHeader=False
 Col1  = Dummy1 CHAR WIDTH  1
 Col2  = PiPaPo CHAR WIDTH 15
 Col3  = Dummy2 CHAR WIDTH  5
 Col4  = PKey   CHAR WIDTH  8
 
 ; Second try: doing the more detailed parsing here (and not in code)
 ; the names are still bad, but perhaps hamboy will tell us something
 ; about the meaning of his data
 [map.txt]
 ; |"P-MON-OMP-1:314" : "P-PQ0506"|
 ;     ---     - ---     ----8---
 ;  XXX   XXXXX X   XXXXX        X
 Format=FixedLength
 ColNameHeader=False
 Col1  = Dummy1 CHAR WIDTH  3
 Col2  = mon    CHAR WIDTH  3
 Col3  = Dummy2 CHAR WIDTH  5
 Col4  = one    CHAR WIDTH  1
 Col5  = Dummy3 CHAR WIDTH  1
 Col6  = Cell   CHAR WIDTH  3
 Col7  = Dummy5 CHAR WIDTH  5
 Col8  = PKey   CHAR WIDTH  8
 Col9  = Dummy6 CHAR WIDTH  1
 
 [original_map.csv]
 ; |"","P-UNKNOWN"
 ; |"P-CAL-OMP-1:1","P-CAL1TEST"
 ; |"P-CAL-OMP-1:41","P-MB0001"
 Format=CSVDelimited
 ColNameHeader=False
 DecimalSymbol=.
 Col1  = PiPaPo CHAR
 Col2  = Element_ID CHAR
 
 [prisoners_in_cells.csv]
 Format=CSVDelimited
 ColNameHeader=False
 DecimalSymbol=.
 Col1  = PiPaPo CHAR
 Col2  = Element_ID CHAR
 
 [report.csv]
 ; |"P-PQ0502","MAGOG - RTE 112/AUT 10","MON1_ECP","CELL 310"
 ;   ---1----   ----------- 2 --------   --- 3 --   -- 4 ---
 Format=CSVDelimited
 ColNameHeader=False
 DecimalSymbol=.
 Col1  = SKey   CHAR
 Col2  = PaPaPa CHAR
 Col3  = PoPoPo CHAR
 Col4  = PiPiPi CHAR
 
 [report3.csv]
 ; |Element_ID,Reporting_System,Cell_Number
 ; |P-AB0001,CAL1_ECP,CELL 1
 ; |P-AB0010,CAL1_ECP,CELL 10
 ; |P-AB0514,NULL,NULL
 ; |P-AB0852,CAL1_ECP,CELL 211 MICRO #2
 Format=CSVDelimited
 ColNameHeader=True
 DecimalSymbol=.
 Col1  = Element_ID       CHAR
 Col2  = Reporting_System CHAR
 Col3  = Cell_Number      CHAR
 
 [original_Report.csv]
 ' |"Element ID","Element Name","Reporting System","Cell Number"
 ' |"CTLBRASTP","CANTEL BRAMPTON STP","OEMF",""
 ' |"CTLTORSTP","CANTEL TORONTO STP","OEMF",""
 ' |"ILLUMINET","ILLUMINET ISSUES IN USA","N/A",""
 ' |"ALIANT NB","ALIANT ROAMING ISSUES","N/A",""
 ' |"P-++++++","TECUMSEH","UNKNOWN",""
 ' |"P-AB0001","CHARLES GLENN TOYOTA","CAL1_ECP","CELL 1"
 Format=CSVDelimited
 ColNameHeader=True
 DecimalSymbol=.
 Col1  = Element_ID       CHAR
 Col2  = Element_Name     CHAR
 Col3  = Reporting_System CHAR
 Col4  = Cell_Number      CHAR
 
 [exported.csv]
 ColNameHeader=True
 CharacterSet=1252
 Format=Delimited(;)
 Col1=Element_ID Char Width 255
 Col2=mon Char Width 255
 Col3=one Char Width 255
 Col4=Cell Integer
 
 [prisoner_reports.csv]
 Format=CSVDelimited
 ColNameHeader=True
 DecimalSymbol=.
 Col1  = Element_ID       CHAR
 Col2  = Element_Name     CHAR
 Col3  = Reporting_System CHAR
 Col4  = Cell_Number      CHAR

(2) library.vbs: a set of functions/subs/classes that can be loaded into
    the namespace of a .vbs script by something like

       ExecuteGlobal goFS.OpenTextFile( "library.vbs" ).ReadAll

    Adding to this file is no problem; reworking existing code is not
    without danger (as old code using the library may break), but if
    you are carefull, old code may even profit from your changes.

Option Explicit
 
 Function newAdoCN( aParms )
   Const csODBVers = ""
   Dim oRVal : Set oRVal = Nothing
   Dim sType : sType     = aParms( 0 )
   Dim sFDSpec, sCNStr
   Select Case UCase( sType )
     Case "TEXT"
       sFDSpec = makeCurPath( aParms( 1 ) )
       sCNStr  = "{Microsoft Text Driver (*.txt; *.csv)}"
       sCNStr  =   "Provider=MSDASQL;"       _
                 + "Driver=" + sCNStr + ";"  _
                 + "DBQ=" + sFDSpec + ";"
 
     Case Else
       Err.Raise   vbObjectError + 100, "newAdoCon()" _
                 , "Con type '" + sType + "' not implemented."
   End Select
   Set oRVal    = CreateObject( "ADODB.Connection" )
   oRVal.Open sCNStr
   Set newAdoCN = oRVal
 End Function
 
 Sub getdispSql( oCN, sSQL, sFDelim )
   Const adClipString = 2
   Dim oRS : Set oRS = oCN.Execute( sSQL )
   Dim sTable
   If oRS.EOF Or oRS.BOF Then
      sTable  = "<query returns no data>"
   Else
      sTable  = oRS.GetString( adClipString, , sFDelim, vbCrLf, "NULL" )
   End If
   WScript.Echo    "------ " + sSQL + vbCrLf _
                 + sTable                    _
                 + "------"
 End Sub
 
 Sub getdispSqlNice( oCN, sSQL, sMode )
   Dim oRS     : Set oRS = oCN.Execute( sSQL )
   Dim aFields : aFields = collectArr( oRS.Fields, "oItem.Name" )
 
   If oRS.EOF Or oRS.BOF Then
      WScript.Echo "<query returns no data>"
   Else
      Dim aData   : aData   = oRS.GetRows()
      Dim aTable  : aTable  = textTableLines( aData, aFields, Empty, sMode )
      WScript.Echo    "------ " + sSQL       + vbCrLf _
                    + Join( aTable, vbCrLf ) + vbCrLf _
                    + "------"
   End If
 End Sub
 
 Function collectArr( objColl, sEValue )
   ReDim aRVal( objColl.Count - 1 )
   Dim   nIdx  : nIdx = 0
   Dim   oItem
   For Each oItem In objColl
       aRVal( nIdx ) = Eval( sEValue )
       nIdx = nIdx + 1
   Next
   collectArr = aRVal
 End Function
 
 Function textTableArr( aData, aColHds, aRowHds, sMode )
   Dim aRVal
   If True Then ' frstest/default
      Dim nDCols : nDCols = UBound( aData, 1 )   ' frs dim - rows
      Dim nDRows : nDRows = UBound( aData, 2 )   ' sec dim - cols
      ReDim aRVal( nDRows + 1   )                ' Header row
      ReDim aWdth( nDCols + 1, 1 )               ' Widthes per Col, num, space( num )
      Dim nRow, nCol
 
      ' Build header row: top left corner ("") and ColHds
      aRVal( 0 ) = Split( vbTab + Join( aColHds, vbTab ), vbTab )
      ' Width of Counter Col is the Len of the last Index
      aWdth( 0, 0  ) = Len( CStr( nDRows + 1 ) )  '
 
      ' What about the ColHds widthes, ekkehard?
      For nCol = 0 To nDCols
          aWdth( nCol + 1, 0  ) = Len( aColHds( nCol ) )
      Next
 
      ' Fill it
      For nRow = 0 To nDRows
          ReDim aRow( UBound( aWdth ) )
          aRow( 0 ) = nRow + 1 ' Counter
          For nCol = 0 To nDCols
              aRow( nCol + 1 ) = aData( nCol, nRow ) ' copy cell data
              If Not IsNull( aData( nCol, nRow ) ) Then
                 If aWdth( nCol + 1, 0 ) < Len( CStr( aData( nCol, nRow ) ) ) Then
                    aWdth( nCol + 1, 0 ) = Len( CStr( aData( nCol, nRow ) ) )
                 End If
              End If
          Next
          aRVal( nRow + 1 ) = aRow
      Next
 
      ' Format it
      For nCol = 0 To nDCols + 1
          aWdth( nCol, 1 ) = Space( aWdth( nCol, 0 ) )
          For nRow = 0 To nDRows + 1
              Dim sVal : sVal = aRVal( nRow )( nCol )
              Select Case VarType( sVal )
                Case vbNull
                  sVal = aWdth( nCol, 1 )
                Case vbEmpty, vbObject, vbError, vbDataObject, vbArray
                Case vbInteger, vbLong, vbByte
                  sVal = Right( aWdth( nCol, 1 ) + CStr( sVal ), aWdth( nCol, 0 ) )
                Case vbSingle, vbDouble, vbCurrency, vbDecimal
                Case vbDate
                Case vbString, vbVariant
                  sVal = Left( CStr( sVal ) + aWdth( nCol, 1 ), aWdth( nCol, 0 ) )
                Case vbBoolean
                Case Else
                  sVal = Left( CStr( sVal ) + aWdth( nCol, 1 ), aWdth( nCol, 0 ) )
              End Select
              aRVal( nRow )( nCol ) = sVal
          Next
      Next
 
   End If
   textTableArr = aRVal
 End Function
 
 Function textTableLines( aData, aColHds, aRowHds, sMode )
   Dim aTable : aTable = textTableArr( aData, aColHds, aRowHds, sMode )
   Dim nIdx
   For nIdx = 0 To UBound( aTable )
       aTable( nIdx ) = "| " + Join( aTable( nIdx ), " | " ) + " |"
   Next
   textTableLines = aTable
 End Function
 
 Function makeCurPath( ByVal sRelFDSpec )
   If ":" <> Mid( sRelFDSpec, 2, 1 ) Then
      sRelFDSpec = goFS.GetParentFolderName( ".\x" ) + "\" + sRelFDSpec
      sRelFDSpec = goFS.GetAbsolutePathName( sRelFDSpec )
   End If
   makeCurPath = sRelFDSpec
 End Function

(3) frstest.vbs: a main test script having three important parts:

    (a) the library loading (see above)

    (b) the (low tech) main sub call 'dispatcher'

        WScript.Quit doPrisonCheck()
        WScript.Quit doFrsCmp()
        ....
        WScript.Quit doOLdFormat()
        WScript.Quit doMain00()

    (c) the main subs, basically

        Function doXXXX()
          Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
          Dim sSQL, aFields, ....

          sSQL    = "SELECT blah FROM blahblah"
          getdispSql oCN, sSQL, "|"
          getdispSqlNice oCN, sSQL, "|"

          doXXXX = 0
        End Function

    The three parts together should allow you to use a (increasing) amount
    of tested code (library.vbs) to try something out (in the different main
    subs) while switching between them by reordering or [un]commenting the
    WScript.Ouit doThisOrThat() calls.

Option Explicit
 
 Dim goFS : Set goFS = CreateObject( "Scripting.FileSystemObject" )
 
 ExecuteGlobal goFS.OpenTextFile( "library.vbs" ).ReadAll
 
 WScript.Quit doPrisonCheck()
 WScript.Quit doFrsCmp()
 WScript.Quit doOrgMap()
 WScript.Quit doExportTest()
 WScript.Quit doOrgReport()
 WScript.Quit doOLdFormat()
 WScript.Quit doMain00()
 
 Function doMain00()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFields
 
   sSQL    = "SELECT * FROM map.txt"
   getdispSql oCN, sSQL, "|"
 
   sSQL    = "SELECT PKey, PiPaPo FROM map.txt"
   getdispSql oCN, sSQL, "|"
 
   aFields = Array( "SKey", "PiPiPi", "PoPoPo", "PaPaPa" )
   sSQL    = "SELECT " + Join( aFields, ", " ) + " FROM report.csv"
   getdispSql oCN, sSQL, "|"
 
   doMain00 = 0
 End Function
 
 Function doOLdFormat()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFields
 
 ' Very simple SQL to look at the raw data of map.txt
   sSQL    = "SELECT * FROM map.txt"
   getdispSql oCN, sSQL, "|"
 
 ' Simple SQL to look at the important data of map.txt; all
 ' the parsing was done in schema.ini
   aFields = Array( "PKey", "mon", "one", "cell" )
   sSQL    = "SELECT " + Join( aFields, ", " ) + " FROM map.txt"
   getdispSql oCN, sSQL, "|"
 
 ' Simple SQL to look at the important data of report.txt
   aFields = Array( "SKey", "PiPiPi", "PoPoPo", "PaPaPa" )
   sSQL    = "SELECT " + Join( aFields, ", " ) + " FROM report.csv"
   getdispSql oCN, sSQL, "|"
 
 ' Concentrating on the parsing of report.csv with a little help
 ' from SQL
 '  P-PQ0502|CELL 310|MON1_ECP|
 '  - key --      cell
 '                    mon
 '                       one
   aFields = Array(   "SKey" _
                    , "RIGHT( PiPiPi, 3    ) AS CELL" _
                    , "LEFT(  PoPoPo, 3    ) AS mon"  _
                    , "MID(   PoPoPo, 4, 1 ) AS one"  _
                  )
   sSQL    = "SELECT " + Join( aFields, ", " ) + " FROM report.csv"
   getdispSql oCN, sSQL, "|"
 
 ' unrestricted Join to show what "Cartesian product" means
 ' cf. http://www.fluffycat.com/sql/cartesian-joins/
 '     http://www.onlamp.com/pub/a/onlamp/2001/06/15/aboutSQL.html
   aFields = Array( "M.PKey", "R.SKey" )
   sSQL    =   "SELECT " + Join( aFields, ", " ) + " " _
             + "FROM    map.txt    AS M "              _
             + "      , report.csv AS R "              _
             + ""
 
   getdispSql oCN, sSQL, "|"
 
 ' the fields - intermediate names, but now we see exactly
 ' what we are interested in
   aFields = Array(   "M.PKey", "R.SKey"                _
                    , "M.Cell" , "M.one", "M.mon"       _
                    , "RIGHT( R.PiPiPi, 3    ) AS Cell" _
                    , "LEFT(  R.PoPoPo, 3    ) AS one"  _
                    , "MID(   R.PoPoPo, 4, 1 ) AS mon"  _
                  )
 ' restricted (inner) Join from map to report
   sSQL    =   "SELECT " + Join( aFields, ", " ) + " " _
             + "FROM    map.txt    AS M "              _
             + "      , report.csv AS R "              _
             + "WHERE   M.PKey = R.SKey "              _
             + ""
   getdispSql oCN, sSQL, "|"
 
 ' same with "inner join" keyword
   sSQL    =   "SELECT " + Join( aFields, ", " ) + " " _
             + "FROM       map.txt    AS M "           _
             + "INNER JOIN report.csv AS R "           _
             + "        ON M.PKey = R.SKey "           _
             + ""
   getdispSql oCN, sSQL, "|"
 
 ' to get all Items from map, Items missing in report will show up will
 ' null values (add |"P-MON-OMP-1:317" : "P-PQ0400"| to map.txt" )
   sSQL    =   "SELECT " + Join( aFields, ", " ) + " " _
             + "FROM       map.txt    AS M "           _
             + "LEFT  JOIN report.csv AS R "           _
             + "        ON M.PKey = R.SKey "           _
             + ""
   getdispSql oCN, sSQL, "|"
 
 ' to get only the Items from map missing in report
 ' (add |"P-MON-OMP-1:317" : "P-PQ0400"| to map.txt" )
   sSQL    =   "SELECT " + Join( aFields, ", " ) + " " _
             + "FROM       map.txt    AS M "           _
             + "LEFT  JOIN report.csv AS R "           _
             + "        ON M.PKey = R.SKey "           _
             + "WHERE R.SKey IS NULL "                 _
             + ""
   getdispSql oCN, sSQL, "|"
 
   doOLdFormat = 0
 End Function
 
 Function doMain02()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFields
 
   sSQL    = "SELECT TOP 10 * FROM map.txt"
   getdispSql oCN, sSQL, "|"
 
 ' Simple SQL to look at the important data of map.txt; all
 ' the parsing was done in schema.ini
   aFields = Array( "PKey", "mon", "one", "cell" )
   sSQL    = "SELECT " + Join( aFields, ", " ) + " FROM map.txt"
   getdispSql oCN, sSQL, "|"
 
   aFields = Array( "Element_ID", "Reporting_System", "Cell_Number" )
   sSQL    = "SELECT TOP 10 " + Join( aFields, ", " ) + " FROM report3.csv"
   getdispSql oCN, sSQL, "|"
 
   doMain02 = 0
 End Function
 
 ' ============================================================================
 ''= working with hamboy's report.csv (original_Report.csv); cf. schema.ini
 ' |"Element ID","Element Name","Reporting System","Cell Number"
 ' |"CTLBRASTP","CANTEL BRAMPTON STP","OEMF",""
 ' |"CTLTORSTP","CANTEL TORONTO STP","OEMF",""
 ' |"ILLUMINET","ILLUMINET ISSUES IN USA","N/A",""
 ' |"ALIANT NB","ALIANT ROAMING ISSUES","N/A",""
 ' |"P-++++++","TECUMSEH","UNKNOWN",""
 ' |"P-AB0001","CHARLES GLENN TOYOTA","CAL1_ECP","CELL 1"
 ' ============================================================================
 
 Function doOrgReport()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFields
 
 ' Very simple SQL to look at the first 10 records
   sSQL    = "SELECT TOP 10 * FROM original_Report.csv"
   getdispSql oCN, sSQL, "|"
 
 ' Concentrating on the parsing of original_Report.csv with a little help
 ' from SQL
 ' |"Element ID","Element Name","Reporting System","Cell Number"
 ' |CTLTORSTP|CANTEL TORONTO STP|OEMF
 ' |P-AB0003|HIDDEN VALLEY|CAL1_ECP
 ' |"P-AB0001","CHARLES GLENN TOYOTA","CAL1_ECP","CELL 1"
   aFields = Array(   "Element_ID                                       " _
                    , "      LEFT( Reporting_System, 3    )     AS mon  " _
                    , "      MID(  Reporting_System, 4, 1 )     AS one  " _
                    , "CLNG( MID(  Cell_Number     , 6    ) )   AS Cell " _
                  )
   sSQL    =   "SELECT TOP 10 " + Join( aFields, ", " ) + " FROM original_Report.csv"
   sSQL    =   "SELECT        " + Join( aFields, ", " ) + " FROM original_Report.csv"
   sSQL    =   "SELECT        " + Join( aFields, ", " ) + " FROM original_Report.csv " _
             + "WHERE  IIF( Cell_Number IS NULL, FALSE " _
             + "        , IIF( NOT ISNUMERIC( MID( Cell_Number, 6 ) ), FALSE  " _
             + "           , 0 < CLNG( MID( Cell_Number, 6 ) ) ) )"
   getdispSqlNice oCN, sSQL, "|"
 
 ' Very simple SQL to get NumberOfRecords
   sSQL    = "SELECT COUNT( Element_ID ) AS NumberOfRecords FROM original_Report.csv"
   getdispSql oCN, sSQL, "|"
 
 ' Simple SQL to get all Element_IDs and their Occurences
   sSQL    =   "SELECT     Element_ID                         " _
             + "         , COUNT( Element_ID )  AS Occurences " _
             + "FROM       original_Report.csv                " _
             + "GROUP BY   Element_ID                         " _
             + ""
 ' getdispSql oCN, sSQL, "|"  ' disabled because so much output
 
 ' Simple SQL to get non unique Element_IDs and their Occurences
 ' they are unique, test the SQL statement by inserting a duplicate row
 ' in original_Report.csv
   sSQL    =   "SELECT     Element_ID                         " _
             + "         , COUNT( Element_ID )  AS Occurences " _
             + "FROM       original_Report.csv                " _
             + "GROUP BY   Element_ID                         " _
             + "HAVING     1 <> COUNT( Element_ID )           " _
             + ""
   getdispSql oCN, sSQL, "|"
 
   doOrgReport = 0
 End Function
 
 ' ============================================================================
 ''=
 ' ============================================================================
 
 Function doExportTest()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFields
 
 ' Table exported.csv must not exist for "select .. into"; let's
 ' remove it quick & dirty
 On Error Resume Next
   sSQL = "DROP TABLE exported.csv"
   oCN.Execute sSQL
 On Error GoTo 0
 
 ' copied from doOrgReport() - should get 'nice' rows of original_Report.csv
 ' only; to show off the power of ADO/SQL they will be saved to exported.csv
 ' without any programming - look at schema.ini after the *first* run to
 ' see the defs for exported.csv
   aFields = Array(   "Element_ID                                       " _
                    , "      LEFT( Reporting_System, 3    )     AS mon  " _
                    , "      MID(  Reporting_System, 4, 1 )     AS one  " _
                    , "CLNG( MID(  Cell_Number     , 6    ) )   AS Cell " _
                  )
   sSQL    =   "SELECT        " + Join( aFields, ", " ) + " FROM original_Report.csv " _
             + "WHERE  IIF( Cell_Number IS NULL, FALSE " _
             + "        , IIF( NOT ISNUMERIC( MID( Cell_Number, 6 ) ), FALSE  " _
             + "           , 0 < CLNG( MID( Cell_Number, 6 ) ) ) )"
   getdispSqlNice oCN, sSQL, "|"
 
   sSQL    =   "SELECT " + Join( aFields, ", " ) + " INTO exported.csv FROM original_Report.csv " _
             + "WHERE  IIF( Cell_Number IS NULL, FALSE " _
             + "        , IIF( NOT ISNUMERIC( MID( Cell_Number, 6 ) ), FALSE  " _
             + "           , 0 < CLNG( MID( Cell_Number, 6 ) ) ) )"
   oCN.Execute sSQL
 
   sSQL    =   "SELECT * FROM exported.csv"
   getdispSqlNice oCN, sSQL, "|"
 
   doExportTest = 0
 End Function
 
 ' ============================================================================
 ''= trying to work with hamboy's original_Report.txt
 ' if you look at this file/format, you may loose all hope -
 ' |"" : "P-UNKNOWN"
 ' |"P-CAL-OMP-1:1" : "P-CAL1TEST"
 ' |"P-CAL-OMP-1:41" : "P-MB0001"
 ' ...
 ' |"P-MON-OMP-1:10" : "P-PQ0651"
 ' |"P-MON-OMP-1:100" : "P-PQ0054"
 ' you can't treat it as file with fixedlength fields/records; it isn't a
 ' decent .csv file. In cases like this you'll have to use more brutal
 ' methods.
 ' ============================================================================
 Function doOrgMap()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sOrgText, sSQL, aFields
 
 ' get this rubbish into a string (Q&D: relative path; will fail if file
 ' is missing or size(file) = 0
   sOrgText = goFS.OpenTextFile( ".\original_map.txt" ).ReadAll
 ' replace " : " with a decent delimiter
   sOrgText = Replace( sOrgText, " : ", "," )
 ' save it as original_Report.csv (only for the first time and after changes;
 ' disable it by comment)
 ' goFS.CreateTextFile( ".\original_map.csv", True ).Write sOrgText
 
 ' we did add a definition for original_Report.csv in schema.ini, didn't we?
   sSQL    =   "SELECT TOP 20 * FROM original_map.csv"
   getdispSqlNice oCN, sSQL, "|"
 ' of course someone has to check, whether this conversion doesn't change
 ' the data; I propose the person who specified the format of original_map.txt
 ' should do this.
 
 ' now do the SQL parsing to get the important parts (take the method
 ' used for report.csv (doOLdFormat()) as a template)
   aFields = Array(   "Element_ID" _
                    , "MID( PiPaPo,  3, 3 ) AS mon"  _
                    , "MID( PiPaPo, 11, 1 ) AS one"  _
                    , "MID( PiPaPo, 13    ) AS Cell" _
                  )
   sSQL    = "SELECT        " + Join( aFields, ", " ) + " FROM original_map.csv"
   sSQL    = "SELECT TOP 20 " + Join( aFields, ", " ) + " FROM original_map.csv"
   getdispSqlNice oCN, sSQL, "|"
 
 
 
 ' Very simple SQL to get NumberOfRecords, this time in original_map.csv
   sSQL    = "SELECT COUNT( Element_ID ) AS NumberOfRecords FROM original_map.csv"
   getdispSqlNice oCN, sSQL, "|"
 
 ' Simple SQL to get all Element_IDs and their Occurences
   sSQL    =   "SELECT     Element_ID                         " _
             + "         , COUNT( Element_ID )  AS Occurences " _
             + "FROM       original_map.csv                   " _
             + "GROUP BY   Element_ID                         " _
             + ""
   getdispSqlNice oCN, sSQL, "|"
 
 ' Simple SQL to get non unique Element_IDs and their Occurences
 ' they are not unique
   sSQL    =   "SELECT     Element_ID                         " _
             + "         , COUNT( Element_ID )  AS Occurences " _
             + "FROM       original_map.csv                   " _
             + "GROUP BY   Element_ID                         " _
             + "HAVING     1 <> COUNT( Element_ID )           " _
             + ""
   getdispSqlNice oCN, sSQL, "|"
 
   doOrgMap = 0
 End Function
 
 Function doFrsCmp()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFields
 
 ' aFields combined from doOrgMap() and doOrgReport
   aFields = Array(   "M.Element_ID", "R.Element_ID"    _
                    , "     M.PiPaPo                 "  _
                    , "MID( M.PiPaPo,  3, 3 ) AS Mmon"  _
                    , "MID( M.PiPaPo, 11, 1 ) AS Mone"  _
                    , "MID( M.PiPaPo, 13    ) AS MCell" _
                    , "      LEFT( R.Reporting_System, 3    )     AS Rmon  " _
                    , "      MID(  R.Reporting_System, 4, 1 )     AS Rone  " _
                    , "CLNG( MID(  R.Cell_Number     , 6    ) )   AS RCell " _
                  )
 ' restricted (inner) Join from map to report
   sSQL    =   "SELECT " + Join( aFields, ", " ) + " "  _
             + "FROM    original_map.csv    AS M     "  _
             + "      , original_report.csv AS R     "  _
             + "WHERE   M.Element_ID = R.Element_ID  "  _
             + ""
   getdispSqlNice oCN, sSQL, "|"
 
   doFrsCmp = 0
 End Function
 
 ' ============================================================================
 ''= check the state of the whole prison
 ' ============================================================================
 
 Function doPrisonCheck()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFieldsM, aFieldsR, aFieldsJ
 
   WScript.Echo "##### Warden ehvbs doing his check"
 
   aFieldsM = Array(   "Element_ID" _
                     , "MID( PiPaPo,  3, 3 ) AS mon"  _
                     , "MID( PiPaPo, 11, 1 ) AS one"  _
                     , "MID( PiPaPo, 13    ) AS Cell" _
                   )
 
   aFieldsR = Array(   "Element_ID" _
                     , "            Reporting_System             AS Rmon  " _
                     , "CLNG( MID(  Cell_Number     , 6    ) )   AS RCell " _
                   )
 
   aFieldsJ = Array(   "M.Element_ID", "R.Element_ID"         _
                     , "MID( M.PiPaPo      , 13 )  AS MCell"  _
                     , "MID(  R.Cell_Number,  6 )  AS RCell " _
                   )
 
   WScript.Echo "***** Who was/is sitting where?"
   sSQL    = "SELECT " + Join( aFieldsM, ", " ) + " FROM prisoners_in_cells.csv"
   getdispSqlNice oCN, sSQL, "|"
 
   WScript.Echo "***** Who is moving around?"
   sSQL    =   "SELECT     Element_ID                         " _
             + "         , COUNT( Element_ID )  AS NumOfCells " _
             + "FROM       prisoners_in_cells.csv                 " _
             + "GROUP BY   Element_ID                         " _
             + "HAVING     1 <> COUNT( Element_ID )           " _
             + ""
   getdispSqlNice oCN, sSQL, "|"
 
   WScript.Echo "***** What does the reporting system tell us?"
   sSQL    = "SELECT " + Join( aFieldsR, ", " ) + " FROM prisoner_reports.csv"
   getdispSqlNice oCN, sSQL, "|"
 
   WScript.Echo "***** Cartesian product"
   sSQL    = "SELECT " + Join( aFieldsJ, ", " ) + " FROM prisoners_in_cells.csv AS M, prisoner_reports.csv AS R"
   getdispSqlNice oCN, sSQL, "|"
 
   WScript.Echo "***** Good cases: prisoner reported in his cell"
   sSQL    =   "SELECT " + Join( aFieldsJ, ", " )     + " " _
             + "FROM      prisoners_in_cells.csv AS M     " _
             + "        , prisoner_reports.csv   AS R     " _
             + "WHERE (     M.Element_ID = R.Element_ID   " _
             + "      )                                   " _
             + ""
   sSQL    =   "SELECT " + Join( aFieldsJ, ", " )     + " " _
             + "FROM      prisoners_in_cells.csv AS M     " _
             + "        , prisoner_reports.csv   AS R     " _
             + "WHERE (     M.Element_ID = R.Element_ID   " _
             + "        AND MCell        = RCell          " _
             + "      )                                   " _
             + ""
   sSQL    =   "SELECT " + Join( aFieldsJ, ", " ) +                          " " _
             + "FROM      prisoners_in_cells.csv AS M                          " _
             + "        , prisoner_reports.csv   AS R                          " _
             + "WHERE (     M.Element_ID         = R.Element_ID                " _
             + "        AND MID( M.PiPaPo , 13 ) = MID(  R.Cell_Number,  6 )   " _
             + "      )                                                        " _
             + ""
   getdispSqlNice oCN, sSQL, "|"
 
   WScript.Echo "***** prisoners not reported at all"
   sSQL    =   "SELECT " + Join( aFieldsJ, ", " ) +     " " _
             + "FROM      prisoners_in_cells.csv AS M     " _
             + "LEFT JOIN prisoner_reports.csv   AS R     " _
             + "     ON   M.Element_ID = R.Element_ID     " _
             + "WHERE R.Element_ID IS NULL                " _
             + ""
   getdispSqlNice oCN, sSQL, "|"
 
   WScript.Echo "***** prisoners not reported in cell(s)"
   sSQL    =   "SELECT " + Join( aFieldsJ, ", " ) +                           " " _
             + "FROM      prisoners_in_cells.csv AS M                           " _
             + "LEFT JOIN prisoner_reports.csv   AS R                           " _
             + "     ON (     M.Element_ID         = R.Element_ID               " _
             + "          AND MID( M.PiPaPo , 13 ) = MID(  R.Cell_Number,  6 )  " _
             + "        )                                                       " _
             + "WHERE (    R.Element_ID              IS NULL                    " _
             + "        OR MID(  R.Cell_Number,  6 ) IS NULL                    " _
             + "      )                                                         " _
             + ""
   getdispSqlNice oCN, sSQL, "|"
 
   WScript.Echo "***** ghost prisoners"
   sSQL    =   "SELECT " + Join( aFieldsJ, ", " ) +        " " _
             + "FROM      prisoner_reports.csv   AS R        " _
             + "LEFT JOIN prisoners_in_cells.csv AS M        " _
             + "     ON (     R.Element_ID  = M.Element_ID   " _
             + "        )                                    " _
             + "WHERE (       M.Element_ID  IS NULL          " _
             + "      )                                      " _
             + ""
   getdispSqlNice oCN, sSQL, "|"
 
   doPrisonCheck = 0
 End Function

    For some time it's a pro that you may refer to code you played
    around with yesterday when you search for better ways tomorrow.
    But it will become more of a con when the size of the file
    increases. So just start afresh with a minimal sectest.vbs

(in reply to hamboy)

Post #: 25

RE: Comparing 2 files - 2/2/2006 6:15:41 AM

hamboy

Posts: 94
Score: 6
Joined: 7/11/2005
Status: offline

cool cool... it makes a lot more sense now...
btw...what is sectest.vbs (your last posted code) for? it does not output anything

Just for clarification, example data: "P-MON-OMP-1:314" : "P-PQ0506", comparison is done with 4 things "MON", "1", "314", "P-PQ0506", which you have already done so it's cool.

however, when i ran the code it gave me error:

quote:

C:\vscript practice\commtower>cscript frstest.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
##### Warden ehvbs doing his check
***** Who was/is sitting where?
C:\Documents and Settings\mahkar\Desktop\test2\frstest.vbs(0, 1) Microsoft OLE D
B Provider for ODBC Drivers: [Microsoft][ODBC Text Driver] The Microsoft Jet dat
abase engine could not find the object 'prisoners_in_cells.csv'. Make sure the
object exists and that you spell its name and the path name correctly.

To help on the testing... I've done the "process "of what the script should do in excel:
http://s49.yousendit.com/d.aspx?id=2AK6HY2LG0F0K2W3W1H31AKHRB

The outoupt should have the exact data and a similar format like this:

< Message edited by hamboy -- 2/2/2006 6:47:45 AM >

(in reply to ehvbs)

Post #: 26

RE: Comparing 2 files - 2/2/2006 9:11:38 AM

ehvbs

Posts: 2061
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi hamboy,

we keep each other occupied! While you where working with Excel, I filled the empty frame of sectest.vbs
like this:

Option Explicit
 
 Dim goFS : Set goFS = CreateObject( "Scripting.FileSystemObject" )
 
 ExecuteGlobal goFS.OpenTextFile( "library.vbs" ).ReadAll
 
 WScript.Quit doStartAgain()
 WScript.Quit doGoodShow()
 WScript.Quit doCreateTests()
 WScript.Quit doCompare()
 
 Function doStartAgain()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFieldsR, aFieldsM
 
 ' Get [original_Report.csv] - 1956 Records (+ 1 Header)
 ' |"Element ID","Element Name","Reporting System","Cell Number"
 ' |"CTLBRASTP","CANTEL BRAMPTON STP","OEMF",""
 ' |"P-AB0193","BEAR HILLS","CAL1_ECP","CELL 193"
 ' into nice format
   aFieldsR = Array(   "Element_ID                                       " _
                     , "      LEFT( Reporting_System, 3    )     AS mon  " _
                     , "      MID(  Reporting_System, 4, 1 )     AS one  " _
                     , "CLNG( MID(  Cell_Number     , 6    ) )   AS Cell " _
                   )
 
   sSQL    =   "SELECT        " + Join( aFieldsR, ", " ) + " FROM original_Report.csv"
 ' This will show all 1956 Records, many of them with empty fields; uncomment
 ' the following line, if you don't believe in comments
 ' getdispSqlNice oCN, sSQL, "|"
 
   sSQL    =   "SELECT        " + Join( aFieldsR, ", " ) + " FROM original_Report.csv " _
             + "WHERE  IIF( Cell_Number IS NULL, FALSE " _
             + "        , IIF( NOT ISNUMERIC( MID( Cell_Number, 6 ) ), FALSE  " _
             + "           , 0 < CLNG( MID( Cell_Number, 6 ) ) ) )"
 ' This will show 1239 Records - from now on considered as 'good'/'non junk'
 ' getdispSqlNice oCN, sSQL, "|"
 
 ' To get original_map.txt into a format suitable for the ADO/Text driver
 ' it was converted into original_map.csv (cf. frstest.vbs :: doOrgMap()
 ' both files contain 1010 records
 
   aFieldsM = Array(   "Element_ID" _
                     , "MID( PiPaPo,  3, 3 ) AS mon"  _
                     , "CLNG( MID( PiPaPo, 11, 1 ) ) AS one"  _
                     , "CLNG( MID( PiPaPo, 13    ) ) AS Cell" _
                   )
 ' reorder the sSQL lines to see that there is just one junk line in original_map.csv
 ' - the first.
   sSQL    =   "SELECT        " + Join( aFieldsM, ", " ) + " FROM original_map.csv"
   sSQL    =   "SELECT TOP 10 " + Join( aFieldsM, ", " ) + " FROM original_map.csv"
   sSQL    =   "SELECT        " + Join( aFieldsM, ", " ) + " FROM original_map.csv " _
             + "WHERE 0 < LEN( PIPAPO ) "
 ' getdispSqlNice oCN, sSQL, "|"
 
 ' to make our live easier we copy the the good records to good_map.csv and
 ' good_report.csv - that's easy with a little help from ADO/Text
 
 ' first delete the resulting files (you have to use "insert into .." if you
 ' want to add to existing tables/files; "select into ..." creates them, and
 ' we want that, because it creates the table structure (defs in schema.ini
 ' too
   Dim sFSpec
   For Each sFSpec In Array( ".\good_map.csv", ".\good_report.csv" )
       sFSpec = makeCurPath( sFSpec )
       If goFS.FileExists( sFSpec ) Then
          goFS.DeleteFile sFSpec
          WScript.Echo sFSpec, "deleted."
       End If
   Next
 
 ' We know how to select the records we consider good (see above); to copy them
 ' to a new table, all we have to do is: change
 '     SELECT what FROM table WHERE cond
 ' to
 '     SELECT what INTO newtable FROM table WHERE cond
 ' and ask the connection to execute our statement
 
   sSQL    =   "SELECT        " + Join( aFieldsR, ", " ) +                  " " _
             + "INTO good_report.csv                                          " _
             + "FROM original_Report.csv                                      " _
             + "WHERE  IIF( Cell_Number IS NULL, FALSE                        " _
             + "        , IIF( NOT ISNUMERIC( MID( Cell_Number, 6 ) ), FALSE  " _
             + "           , 0 < CLNG( MID( Cell_Number, 6 ) ) ) )"
   oCN.Execute sSQL
 
   sSQL    =   "SELECT        " + Join( aFieldsM, ", " ) +  " " _
             + "INTO good_map.csv                             " _
             + "FROM original_map.csv                         " _
             + "WHERE 0 < LEN( PIPAPO ) "
   oCN.Execute sSQL
 
 ' this main sub gets filled up, let's continue our work in doGoodShow()
 
   doStartAgain = 0
 End Function
 
 Function doGoodShow()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFieldsR, aFieldsM
 
 ' because we dont have the sql-split the data anymore, showing
 ' off the good ones (first part of hamboy's request) is easy:
 
 ' by using "top n" we restrict the output to be nice to the dos box
 ' scroller (and our eyes)
   sSQL = "SELECT TOP 10 * FROM good_map.csv"
   getdispSqlNice oCN, sSQL, "|"
   sSQL = "SELECT TOP 10 * FROM good_report.csv"
   getdispSqlNice oCN, sSQL, "|"
 
 ' run the unrestricted versions at least once to verfy that we work
 ' with 1239 reps. 1009 records
   sSQL = "SELECT * FROM good_map.csv"
 ' getdispSqlNice oCN, sSQL, "|"
   sSQL = "SELECT * FROM good_report.csv"
   getdispSqlNice oCN, sSQL, "|"
 
 ' After the show - see you in doCreateTests() for a little bit of harder
 ' work
 
   doGoodShow = 0
 End Function
 
 Function doCreateTests()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFieldsR, aFieldsM
 
 ' Preparation: Function incI() in library.vbs and schema.ini defs for
 ' test_map.csv and test_report.csv must have been added
 
 ' First we take the sample lines from hamboy's posting,
 ' remove the formatting hamboy and getdispSqlNice() have
 ' added and make them VBScript code compatible
 
   Dim aT, nI, sReport, sMap
 
   ReDim aT( 50 ) : nI = -1
   aT(incI( nI )) = "'Element_ID';'mon';'one';'Cell'"
   aT(incI( nI )) = "'P-AB0192';'CAL';1;192"
   aT(incI( nI )) = "'P-AB0193';'CAL';1;193"
   aT(incI( nI )) = "'P-BC0001';'VAN';1;1"
   aT(incI( nI )) = "'P-BC0002';'VAN';1;2"
   aT(incI( nI )) = "'P-BC0003';'VAN';1;3"
   aT(incI( nI )) = "'P-BC0004';'MAN';1;4"
   aT(incI( nI )) = "'P-BC0005';'VAN';1;5"
   aT(incI( nI )) = "'P-BC0006';'VAN';1;6"
   aT(incI( nI )) = "'P-BC0007';'VAN';1;7"
   aT(incI( nI )) = "'P-BC0008';'VAN';1;8"
   aT(incI( nI )) = "'P-BC0009';'VAN';1;9"
   aT(incI( nI )) = "'P-BC0010';'VAN';1;10"
   aT(incI( nI )) = "'P-BC0011';'VAN';1;11"
   aT(incI( nI )) = "'P-BC0012';'VAN';1;12"
   aT(incI( nI )) = "'P-BC0013';'VAN';1;13"
   aT(incI( nI )) = "'P-BC0015';'VAN';1;15"
   ReDim Preserve aT( nI )
   sReport = Join( aT, vbCrLf )
   sReport = Replace( sReport, "'", """" )
 
   ReDim aT( 50 ) : nI = -1
   aT(incI( nI )) = "'Element_ID';'mon';'one';'Cell'"
   aT(incI( nI )) = "'P-AB0192';'CAL';1;192"
   aT(incI( nI )) = "'P-AB0193';'CAL';1;200"
   aT(incI( nI )) = "'P-BC0001';'VAN';1;1"
   aT(incI( nI )) = "'P-BC0002';'VAN';1;2"
   aT(incI( nI )) = "'P-BC0003';'VAN';1;3"
   aT(incI( nI )) = "'P-BC0004';'VAN';1;4"
   aT(incI( nI )) = "'P-BC0005';'VAN';1;5"
   aT(incI( nI )) = "'P-BC0006';'VAN';1;6"
   aT(incI( nI )) = "'P-BC0007';'VAN';1;7"
   aT(incI( nI )) = "'P-BC0008';'VAN';1;8"
   aT(incI( nI )) = "'P-BC0009';'VAN';1;9"
   aT(incI( nI )) = "'P-BC0010';'VAN';1;10"
   aT(incI( nI )) = "'P-BC0012';'VAN';1;12"
   aT(incI( nI )) = "'P-BC0013';'VAN';1;13"
   aT(incI( nI )) = "'P-BC0015';'VAN';1;14"
   ReDim Preserve aT( nI )
   sMap = Join( aT, vbCrLf )
   sMap = Replace( sMap, "'", """" )
 
   goFS.CreateTextFile( ".\test_map.csv"   , True ).WriteLine sMap
   goFS.CreateTextFile( ".\test_report.csv", True ).WriteLine sReport
 
 ' see what we got
   WScript.Echo "test_map.csv - by hamboy, editor, ehvbs & mator"
   sSQL = "SELECT * FROM test_map.csv"
   getdispSqlNice oCN, sSQL, "|"
   WScript.Echo "test_report.csv - by hamboy, editor, ehvbs & mator"
   sSQL = "SELECT * FROM test_report.csv"
   getdispSqlNice oCN, sSQL, "|"
 
 ' that was hard work (3 trial runs until I had the "'; exactly right
 ' now to doCompare()
 
   doCreateTests = 0
 End Function
 
 Function doCompare()
   Dim oCN : Set oCN = newAdoCN( Array( "text", ".\" ) )
   Dim sSQL, aFieldsJ, sPfx
 
   sPfx = "good"   'switch these lines to work with good_* / test_*
   sPfx = "test"   'switch these lines to work with good_* / test_*
 
   aFieldsJ = Array(    "M.Element_ID AS MID"   _
                      , "R.Element_ID AS RID"   _
                      , "R.mon        AS Rmon"  _
                      , "M.mon        AS Mmon"  _
                      , "R.Cell       AS RCELL" _
                      , "M.Cell       AS MCELL" _
                   )
 
 ' To get our SQL feet wet, let do Cartesian product first
   sSQL =   " Select  " + Join( aFieldsJ, "," ) + " " _
          + " FROM    " + sPfx + "_map.csv    AS M  " _
          + "       , " + sPfx + "_report.csv AS R  " _
          + ""
 ' getdispSqlNice oCN, sSQL, "|"
 
 ' Now look for missing tower P-BC0011 (found in _report, but not in _map)
   sSQL =   " Select    R.Element_ID AS TowerNotInM   " _
          + " FROM      " + sPfx + "_report.csv AS R  " _
          + " LEFT JOIN " + sPfx + "_map.csv    AS M  " _
          + "        ON R.Element_ID = M.Element_ID   " _
          + " WHERE     M.Element_ID IS NULL          " _
          + ""
   getdispSqlNice oCN, sSQL, "|"
 
 ' Vice Versa - delete e.g. "P-BC0007";"VAN";1;7 temporarily from test_report
   sSQL =   " Select    M.Element_ID AS TowerNotInR   " _
          + " FROM      " + sPfx + "_map.csv    AS M  " _
          + " LEFT JOIN " + sPfx + "_report.csv AS R  " _
          + "        ON M.Element_ID = R.Element_ID   " _
          + " WHERE     R.Element_ID IS NULL          " _
          + ""
   getdispSqlNice oCN, sSQL, "|"
 
 ' check for agreement M -> R
   sSQL =   " Select       " + Join( aFieldsJ, "," ) + "    " _
          + " FROM         " + sPfx + "_map.csv&