用asp把文本文件导入到sql server中

来源：互联网发布：arm和单片机的区别编辑：程序博客网时间：2024/05/16 07:35

<!-ASP不再仅仅能够用于HTML了。我们将会看到，ASP可以在多种多样的应用中发挥作用，其中包括将文本文件导入SQL Sever。-->

ASP是被打算用于创建HTML文档的，但是对于许多其它的编程问题，它也是一种唾手可得的解决工具。比如像把含有定界符的文本文件导入到SQL Sever 6.5的表中这样的工作。虽然SQL Sever包括了一个这样的工具(批量拷贝或者bcp实用工具)，但是它至少有一个主要的缺陷：把您要导入的数据想得太完美无缺了。因此，如果在定界符文件中应该写着数字的地方写上了字符串，bcp实用工具就会崩溃。

这种情况下，一个对于健壮性的改进方案是使用Access作为向SQL Sever导入数据的前端。这是个好的做法，但是Access也对包含错误数据的域也非常的挑剔。

最终，一切都表明一个用户定制编写的程序才是最好的解决方案。要解决这类问题，ASP是一个很好的选择，它几乎拥有Visual Basic的全部功能，并且能够更容易的让程序立即运行起来。因为在这种工作中，并不需要一个用户界面，我们需要的仅仅是导入一些数据并且如果有错误的话，可以得到关于错误的报告。

设想
不幸的是，使用ASP将带有定界符的文本文件变成数据库表并不想所期望的那样简单。不管怎么说，ASP并非一个数据库工具，所以我们需要在实际导入数据前进行一些设置工作。

特别要注意的是，我们需要使用某种方法将导入数据的目标表的信息告诉ASP代码。至少要了解表中域的个数、名称、它们的类型(比如字符型、数字型、日期形等等) 以及每个域中最多可以包含的数据(如果是一个字符型的域)。使用一个简单的文本文件来描述目标数据库表是一个好的解决办法。表A列出了这样一个例子。

表A: 一个简单的数据库表描述文件

3
name, c, 50
age, n, 0
birthday, d, 0
文件的首行告诉我们该表有多少个域(在此例中是三个)。剩下的三行分别描述了表中每一个域的特征。每一行都含有域的名称、类型(n=数字型、c=字符型、d=日期型)以及域可以包含的字符个数。(注意最后一项信息只对字符域有意义。) 需要特别注意的是，这些域的排列顺序必须与它们在含有定界符的文本文件中的顺序相同！好，从表A中我们可以看出这个表有三个域：

name:一个可以容纳50个字符的字符型域
age:一个数字型域
birthday:一个日期型域
在表B中，我们看到的是一个用tab作为定界符的简单的文本文件。它含有几个总统的姓名、他们就职时的年龄以及他们的生日。(注意这个文件中域的顺序与表A所示的文件是相同的。你还可以注意到第二条记录的age域含有一个非数字的值。)

表B: 一个非常简单的用tab字符定界的文件

Jimmy Carter    52    10/01/1924
Ronald Reagan    sixty-nine    02/06/1911
George Bush    64    06/12/1924
如表B所示，大多数文件都使用tab作为定界符。不过，定界符也可以是任意字符。因此，最好的解决方案应该能够处理任何定界符。

解决方案
表C出了这个问题的一种可能的解决方案。这是一个Visual Basic函数，它能够读取带有定界符的文本文件并且将数据加入到指定的数据库表中。

为了尽可能灵活，该函数带有几个不同的参数：

dbName--指明目标表所在的数据库名称。(如果用户使用的数据库系统不使用数据库名，只需要传给这个参数一个空字符串。)
tableName--指定目标表的名称。
sourceText--存有待导入的数据的定界符文本文件，如表B所示。
descriptors--含有目标数据库表的描述信息的文件的名称。
errorLog--记录错误信息的文件的名称。
tCon--一个起关联作用的对象，将dbName参数中的数据库名称和tableName参数中设置的表名称联系起来。
原理
本函数代码中包含了详细的关于操作细节的注释，因此我们仅仅粗略的介绍一下各个对象是如何工作的。在进入函数之前，我们必须为web服务器设置server.scripttimeout属性。这个属性告知web服务器经过多久（以秒为单位）后放弃运行的脚本。

这个值需要根据导入数据量的多少进行相应的调整。好了，完成这些琐碎的管理工作之后，我们可以开始研究真正的函数了。

函数做的第一件事是打开包含着目标数据库表的描述信息的描述文件。用该文件中的信息建立一个数组，来存放我们所需了解的数据库表的所有信息。

完成这一步后，所建立的数组中有表中各个域的名称、类型和长度。然后，打开错误日志文件，令其准备就绪接受我们生成的一切错误信息。现在可以打开定界符文件，然后开始将数据从其中"拽"出了。文件一旦打开，就从中读取记录并将记录一次一个域地进行分解。同时，我们检验每个域中的数据，查看它们是否符合描述文件中的标准设置。

如果描述文件将一个域标志为n(数字型)，我们就使用isNumeric()函数来查看读取的值是否真的是一个数字。如果是的话，将该值插入到SQL语言的Insert语句中。否则，输出一条错误信息并且将插入到SQL语言Insert语句中的值改为NULL(这是很重要的一点：即使一个或者多个域中含有错误的数据，我们仍然可以试图添加这条记录！仅仅需要在添加前将坏数据除去就可以了。)

当所有的域都经过处理之后，我们利用刚才创建好的SQL Insert语句将数据加入到目标表中。如果这一过程中出现了错误，将错误记录到错误日志中。

到此为止，处理一条记录的工作就完成了。接着处理下一条。当源文件空了之后，打印一些信息，然后向函数的调用者返回插入的记录数。

尾声
当然，上面的技术不光可以仅仅用来存取数据库表而已。比如，可以很简单的将这个函数中生成SQL语言Insert语句的部分改为生成HTML表格的一行。(这是一个将不完备的带定界符的文本文件加入到web站点上的绝佳的方式。)

其实，最关键的一点是要明白ASP是一个优秀的工具，而不是仅仅可以作为HTML生成器。实际上，一旦将ASP用在其它事物上，你会发现它可以用于任何生僻的小型编程任务。如果你发现了更好的主意，请通知我们。

表C:向数据库中插入数据

<%
on Error resume next

' 设置本脚本可以运行的时间
server.scripttimeout = 1000

' 调入一个带定界符的文本文件，参数如下
' dbName – 数据库名称
' tableName – 表名称
' delimiter – 分离域的定界字符
' sourceText – 实际的带定界符的文本文件
' descriptors – 含有目标表中各个域的说明信息的文件
' errorLog – 存放错误日志的文件
' tCon – 与数据导入到的表的连接
function loadDelimitedTextFile( dbName, _
         tableName, delimiter, sourceText, _
         descriptors, errorLog, tCon)
         recsLoaded = 0
         totalRecs = 0

         ' 保存描述文件中的信息
         dim descInfo()
         ' SQL insert语句的开始和结束部分
         ' 注意我们创建的语句把所有域都插
         ' 入到新的记录中
         iStart = "insert into " & tableName & "values (" _
                 iEnd = ")"

         ' 设置使用的数据库
         ' 并非所有的数据库系统都需要这一参数
         ' 因此，如果没有dbName参数，我们忽略这一步骤。
         if (dbName <> "") then
                 tCon.execute("use " & dbName)
         end if

         ' 打开数据库描述文件
         set descFile = server.createObject _
                 ( "Scripting.FileSystemObject")
         descPath = server.mappath( descriptors)
         set descData = descFile.OpenTextFile ( descPath)

         ' 文件首行表明需要读取的域的个数
         numFields = cInt(descData.readline)
         ' 下面是域的信息
         ' 第一列是名称
         ' 第二列是类型
         ' 第三列是允许的长度
         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

         ' 创建错误日志文件
         set errorFile = server.createObject _
                 ( "Scripting.FileSystemObject")
         errorPath = server.mappath( errorLog)
         set errorData = errorFile.CreateTextFile _
                 ( errorPath, true)

         ' 打开数据文件
         set sourceFile = server.createObject _
                 ( "Scripting.FileSystemObject")
         sourcePath = server.mappath( sourceText)
         set sourceData = sourceFile.OpenTextFile _
                 ( sourcePath)

         ' 开始调入数据
         response.write( "<font color=red size=+1>Now _
                 loading " & sourcePath & "<BR></font>")
         do while sourceData.atendofstream = false
                 ' 读入一行并且再日志中记录
                 ' 可能的错误
                 thisLine = sourceData.readline
                 errLogLine = thisLine

                 ' 清除insert语句并且
                 ' 寻找下一个定界符
                 iStr = ""
                 nextDelimiter = inStr( thisLine, delimiter)
                 if nextDelimiter = 0 then
                          nextDelimiter = len(thisLine)
                 end if

                 ' 将thisLine插入到insert语句中
                 for x = 0 to (numFields - 1)
                          ' 获取数据值。注意我们将所有单引号替换为
                          ' 两个单引号，以便进行SQL命令的解析（使用
                          ' 单引号分割字符串）
                          thisData = left( thisLine, nextDelimiter)
                          thisData = replace(thisData, "''", "")

                          ' 下面是可以添加特殊情况下的转换代码的地方
                          ' 比如，如果一个域是数字型的，
                          ' 而所导入的数据写成了"One"、"Three"或者"Five"
                          ' 可以将这些值转换为"1", "3"或者"5"

                          if (thisData = "") then
                                   ' special case null values
                                   iStr = iStr & "NULL, "
                          else
                                   ' response.write( thisData & "<BR>")
                                   select case descInfo(x, 1)
                                            case "n"
                                                     ' 此处应是一个数字值
                                                    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"
                                                    ' 此处应是一个字符串
                                                    ' 注意检查长度
                                                    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"
                                                    ' 这里应是一个日期域
                                                    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
                                                    ' 有错误
                                                    errorData.writeLine( errLogLine)
                                                    errorData.writeLine _
                                                    ( "     Field '" & descInfo(x, 0) _
                                                    & "' is an unknown type: '" _
                                                    & thisData & "'")
                                   end select
                          end if

                          ' 截断其余数据
                          thisLine = mid( thisLine, nextDelimiter + 1)
                          nextDelimiter = inStr ( thisLine, delimiter)
                          if nextDelimiter = 0 then
                                   nextDelimiter = len(thisLine)
                          end if
                 next
                 ' 将我们创建的字符串的最后两个字符删去
                 iStr = left(iStr, len(iStr) - 2)
                 ' response.write( iStart & iStr & iEnd & "<p>")

                 ' 将数据加入表中
                 tCon.errors.clear
                 tCon.execute(iStart & iStr & iEnd)

                 ' 输出错误行
                 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

' 建立到服务器的连接
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)

%>