sql - 如何将文本文件转换为 SQL 插入语句？

Question

我会上传一个文本文件，它会将每一行转换为一个 SQL 插入语句，以便它可以插入到数据库中。

正如我所尝试的，这里有3个问题：

1. 它将一次插入5倍的值
2. 所有的号码都应该跳过
3. 作为strSQL，我需要将arrayOutput一个一个插入，将它们全部插入

这是我的文本文件

    No  Subject Code    Subject Name    Student ID  Student Name    Student Email   Lecturer Name   Lecturer Email
1   BSOA011 COMPUTERIZED PAYROLL    1751234-BSOA    WONG QI YI  [e-mail]  LIM BEE HUI [e-mail]
2   SEMD008 COMPUTER NETWORKING 1851234-SEMD    LIM FENG FENG   [e-mail]  NG WAI KONG [e-mail]
3   ITIN015 DISASTER RECOVERY MANAGEMENT    1851234-ITIN    HO DENG DENG    [e-mail]  SZE CHEN NEE @ SOO CHEN NEE [e-mail]
4   ECOM004 WEB DEVELOPMENT SKILLS: WEB PROGRAMMING (PHP & MYSQL)   1851234-ECOM    KONG JIA WEN    [e-mail]  TAN MING MING   [e-mail]
5   CGDD002 PRACTICAL ICT SKILLS    1951234-CGDD    CHONG SIAO YU   [e-mail]  NG WAI KONG [e-mail]

这是我的代码

 Private Sub ReadAndReplace()

    Dim file_contents As String = ""

    locationPath = Server.MapPath("~") & "File\" & filename

    If File.Exists(locationPath) Then
        Dim reader As StreamReader = My.Computer.FileSystem.OpenTextFileReader(locationPath)
        reader.ReadLine() 'skip first line


        Do While reader.Peek() <> -1
            file_contents += reader.ReadLine

            Dim arrayOutput As String() = Split(file_contents, vbTab)


            Dim errMessage As String = ""
            Dim strConn As String = "server=localhost;port=3306; user id=root;" _
                             & "password=;database=school;SslMode=none"
            Dim oDBCon As New MySqlConnection(strConn)
            Try
                oDBCon.Open()

                'INSERT RECORD
                Dim strSQL As String = "INSERT INTO studentinfo(subject_code, subject_name, student_id,
                                        student_name, student_email, lecturer_name, lecturer_email)VALUES('" _
                                        & arrayOutput(1) & "','" & arrayOutput(2) & "','" & arrayOutput(3) _
                                        & "','" & arrayOutput(4) & "','" & arrayOutput(5) _
                                        & "','" & arrayOutput(6) & "','" & arrayOutput(7) & "');"


                Dim oCommand2 As New MySqlCommand(strSQL, oDBCon)
                oCommand2.ExecuteNonQuery()

            Catch ex As Exception
                errMessage = ex.Message
            Finally
                oDBCon.Close()
            End Try
        Loop
        reader.Close()
    End If

End Sub

我的输出

标签： sqlasp.netvb.netfile-io

Answer 1

注意这一行：

file_contents += reader.ReadLine

它正在添加到初始 file_contents 变量中，因此当它被拆分以创建 arrayOutput 时，前 7 个数组元素始终相同。

也许尝试 file_contents = reader.ReadLine 这样你每次迭代只分割一行。