dankemper.net 2.0 :: VBScript

VBScript Next Up - Database Extractions with VBScript: Solving the Floating Point Problem

Converting a table from a legacy database to a table with a new relational schema may create a problem for the DBA. The main issue is the number of keys in the old table, vs. the number of keys in the new table. The number of keys may not have changed, but a compound key may have been created in the new table, that didn't exist in the old table.

If the old table can be exported to a file delimited by a semicolon, then an empty column that should contain a key according to the new format, can be easily spotted by the presence of ';;'. The script below substitutes text in place of an empty data element. The result is a new delimited file that can be easily imported to the database of your choice. This script was written for a database that resides in SQL Server 2000.

' Begin
	Call ReadFromFile
	
	' Create a procedure to read data from the exported table file
	' and write it back to a file reflecting the relational integrity
	' of the new table
	
	Sub ReadFromFile
	   Dim fso, s, f1Stream, f2Stream
	   Const ForReading = 1
	   Const ForWriting = 2
	   Const ForAppending = 8
	
	   Set fso = CreateObject("Scripting.FileSystemObject")
	   
	  ' First, create the file that will hold our replaced results
	
	   If (Not (fso.FileExists("c:\temp\newFile_KEYS.txt"))) Then
	      Set myFile = fso.CreateTextFile("c:\temp\newFile_KEYS.txt", True)
	      myFile.Close
	   End If
	   
	   Set file = fso.GetFile("c:\temp\newFile_KEYS.txt")
	   Set f2Stream = file.OpenAsTextStream(ForAppending)
	
	   arryCtr = 0
	   elCount = 0
	   
	   Set file2 = fso.GetFile("c:\temp\badKeys.txt")
	   Set f1Stream = file2.OpenAsTextStream(ForReading)
	   
	   Do While Not f1Stream.AtEndOfStream
	    	s = f1Stream.ReadLine & NewLine
	    	elementsArry = Split(s, ";")
	    
	      	for each x in elementsArry
	     		elCount = elCount + 1 ' number of array elements
	     	next
	     
	    	if elementsArry(3) = "" then
	    		elementsArry(3) = "KEY3"
	    		' WScript.Echo elementsArry(3)
	    	end if  
	    	if elementsArry(1) = "" then
	    		elementsArry(1) = "KEY1"
	    		' WScript.Echo elementsArry(1)
	    	end if  
	 
	      	str = ""
	    
	      	for each el in elementsArry
	      		arryCtr = arryCtr + 1
	      		if (arryCtr = elCount) then
	      		   str = str & el
	      		else 
	      		   str = str & el & ";"
	      		end if 
	      	next
	
	      	f2Stream.Write(str & vbCrLf)
	      
	   Loop
	   
	   f2Stream.Close
	   f1Stream.Close
	
	End Sub