<%
on Error resume next

' Set the amount of time this script can run
server.scripttimeout = 1000

' Load a delimited text file. The parameters are
' dbName - the database name
' tableName - the table name
' delimiter - the delimiter character that 
separates fields
' sourceText - the actual delimited text file
' descriptors - file containing info about 
each field in the 
` target table
' errorLog - the file to put the error log in
' tCon - the connection to the table that gets 
the data
function loadDelimitedTextFile( dbName, _
	tableName, delimiter, sourceText, _
	descriptors, errorLog, tCon)
	recsLoaded = 0
	totalRecs = 0
	
	' Holds the info from the descriptors file
	dim descInfo()
	' The beginning and ending bits of the 
SQL insert statement
	' Note that the statement we build here 
will insert ALL 
	` fields into the new record
	iStart = "insert into " & tableName 
& "values (" _
		iEnd = ")"
	
	' Set the database to use
	' Not all db systems require this statement.
	' So, if no dbName was passed, we skip this step.
	if (dbName <> ") then
		tCon.execute("use " & dbName)
	end if

	' Open the database descriptor file
	set descFile = server.createObject _
		( "Scripting.FileSystemObject")
	descPath = server.mappath( descriptors)
	set descData = descFile.OpenTextFile ( descPath)

	' The first line tells us how many fields to read.
	numFields = cInt(descData.readline)
	' This holds info on the fields.
	' The first column is the name.
	' The second is the type.
	' The third is the allowed length.
	reDim descInfo( numFields - 1, 2)
	
	' Load the variable names and their types.
	for x = 0 to numFields - 1
		thisLine = descData.readline
		descInfo( x, 0) = trim(left(thisLine, _
			inStr(thisLine, ",") - 1))
		thisLine = trim(mid(thisLine, _
			inStr(thisLine, ",") + 1))
		descInfo( x, 1) = trim(left(thisLine, _
			inStr(thisLine, ",") - 1))
		descInfo( x, 2) = cInt(trim(mid(thisLine, _
			inStr(thisLine, ",") + 1)))
	' response.write("name: " & descInfo(x,0) 
& ", Type: " _
		& descInfo(x,1) & ", Size: " 
& descInfo(x,2) & "<BR>")
	next

	' Create the error log file.
	set errorFile = server.createObject _
		( "Scripting.FileSystemObject")
	errorPath = server.mappath( errorLog)
	set errorData = errorFile.CreateTextFile _
		( errorPath, true)

	' Open the data file.
	set sourceFile = server.createObject _
		( "Scripting.FileSystemObject")
	sourcePath = server.mappath( sourceText)
	set sourceData = sourceFile.OpenTextFile _
		( sourcePath)

	' Begin loading the data.
	response.write( "<font color=red 
size=+1>Now _
		loading " & sourcePath & 
"<BR></font>")
	do while sourceData.atendofstream = false
		' Read a line & preserve it for
		' possible inclusion in the error log.
		thisLine = sourceData.readline
		errLogLine = thisLine

		' Clear the insert statement and 
		' find the next delimiter.
		iStr = "
		nextDelimiter = inStr( thisLine, delimiter)
		if nextDelimiter = 0 then
			nextDelimiter = len(thisLine)
		end if
		
		' Take thisLine and plug it into the 
insert statement.
		for x = 0 to (numFields - 1)
			' Get the data value. Note that 
we replace any single 
			` quotes "'" with two SINGLE 
QUOTES "''" to get them 
			` past the SQL command parser 
(which uses single quotes 
			` to delimte text strings)
			thisData = left( thisLine, 
nextDelimiter)
			thisData = replace(thisData, 
"''", ")

			' This is where you would add 
any special case 
			` conversion code. For example, 
if a field is supposed 
			` to be numeric, and several 
records have "One" or 
			' "Three" or "Five" in the 
place for that field,
			' you could convert these values 
to "1", "3" and "5"
				
			if (thisData = ") then
				' special case null values
				iStr = iStr & "NULL, "
			else
				' response.write( thisData 
& "<BR>")
				select case descInfo(x, 1)
					case "n"
						' This should 
be a numeric value!
						if not 
isNumeric(thisData) then
						errorData.writeLine 
( errLogLine)
						errorData.writeLine _
						( "     Field '" 
& descInfo(x, 0) _
						& "' contains 
an invalid number: '" 
						& thisData 
& "'")
							thisData 
= "NULL"
						end if
						iStr = iStr 
& thisData & ", "
					case "c"
						' This should 
be a character string!
						' Be sure to 
check the length!
						if len(thisData) > 
descInfo(x, 2) then
						errorData.writeLine 
( errLogLine)
						errorData.writeLine _
						( "     Field '" 
& descInfo(x, 0) _
						& "' contains 
a sting that is too long _
						(max allowed is " 
& descInfo(x, 2) _
						& "): '" & 
thisData & "'")
						thisData = left( 
thisData, _
							descInfo(x, 2))
						end if
						iStr = iStr & 
"'" & thisData & "', "
					case "d"
						' This should be 
a date field!
						if not 
isDate(thisData) then
						iStr = iStr 
& "NULL, "
						
errorData.writeLine ( errLogLine)
					
	errorData.writeLine _
	 				
	( "     Field '" & descInfo(x, 0) _
					
	& "' contains an invalid date: '" _
					
	& thisData & "'")
					
	else
					
	iStr = iStr & "'" & thisData & "', "
					
	end if
				
	case else
					
	' Something isn't quite right!
					
	errorData.writeLine( errLogLine)
					
	errorData.writeLine _
					
	( "     Field '" & descInfo(x, 0) _
					
	& "' is an unknown type: '" _
					
	& thisData & "'")
				end select
			end if
				
			' snap off the rest of the data
			thisLine = mid( thisLine, nextDelimiter + 1)
			nextDelimiter = inStr ( thisLine, delimiter)
			if nextDelimiter = 0 then
				nextDelimiter = len(thisLine)
			end if
		next
		' Remove the last two characters from the
		' string we've built
		iStr = left(iStr, len(iStr) - 2)
		' response.write( iStart & iStr 
& iEnd & "<p>")

		' Insert the data into the table!
		tCon.errors.clear
		tCon.execute(iStart & iStr & iEnd)

		' Output the error line
		if tCon.errors.count > 0 then
			errorData.writeLine( errLogLine)
			for j = 0 to tCon.errors.count - 1
				errorData.writeLine 
(tCon.errors(j).description)
			next
		else
			recsLoaded = recsLoaded + 1
		end if
		totalRecs = totalRecs + 1
	loop

	response.write( "<P><font 
color=red size=+1>" & _
		recsLoaded & " of " & 
totalRecs & _
		" records loaded. Check error 
logs for details on _
		bad records.<BR></font>")

	loadDelimitedTextFile = recsLoaded
end function

' Establish the connection to your server.
Set mainCon = Server.CreateObject ( "ADODB.Connection")
mainCon.Open "driver={SQL Server};uid=sa;pwd=;SERVER=NT2"

numRecs = loadDelimitedTextFile ( "Articles", "People", chr(9), _
	"people.txt", "fieldInfo.txt","errors.txt", mainCon)

%>