How to convert text file into SQL insert statement?

Question

I would upload a text file, and it will convert every row into a SQL insert statement, so it can insert into the database.

As my tried, there are 3 problems here:

it will insert 5 times the values at once
all the No. should be skip
As the strSQL, I need to insert the arrayOutput one by one to insert them all

Here is my text file

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

Here is my code

 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

my output

Alejandro · Accepted Answer · 2019-11-24 07:36:31Z

2

Notice this line:

file_contents += reader.ReadLine

It is adding to initial file_contents variable so when it is split to create arrayOutput the top 7 array elements are always the same.

Maybe try file_contents = reader.ReadLine so you only split one line per iteration.

answered Nov 24, 2019 at 7:36

Alejandro

4133 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gary Over a year ago

I was just trying to debug but I didn't realize of this. Thanks anyway.

Collectives™ on Stack Overflow

How to convert text file into SQL insert statement?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related