Visual Basic and SQL Server – Importing Data

In order to import data into an SQL Server database, it must be in a suitable format. CSV files are one such format.  A CSV file, or Comma Separated Values file, is a delimited file that uses commas to separate values. They store tabular data in plain text. The first row in a CSV file often contains headers for each column of data. Each row in the file thereafter is a record of related data. In the example contents of a CSV file below, each row contains information relating to a person, including their first name, last name, title and date of birth in the format YYYY-MM-DD (four digit year, two digit month and two digit day).

"firstname","lastname","title","dob"
"Zoe","Davis","Miss","1979-07-11"
"Tom","Ingram","Mr","1971-10-04"
"Karen","Thomas","Mrs","1969-03-08"
"Samantha","Yates","Miss","1995-08-27"

Below is an example of how Visual Basic can be used to import data from a CSV file called ‘personimport.csv’, into a table called ‘person’, which was used in the examples for selecting, inserting, updating and deleting data.

Firstly, the file path is set and a check is made to see if the CSV file actually exists. If it does, it gets assigned to a ‘reader’ object and the rows of data under the headers are processed one by one, using a ‘While’ loop. For each row of data, an ‘Insert‘ statement is constructed and stored in a list. A connection to the database is then established and a ‘For Each’ loop is used to extract the ‘Insert‘ statements from the list and execute them. Feedback is given as to the number of records added.

Note that, as well as an ‘Imports‘ statement for ‘System.Data.SqlClient’, there must also be one for ‘System.IO’ in order for this to work.

' File path.
Dim filePath As String = "C:\demo\personimport.csv"

' Data variables.
Dim currentRow As String()
Dim headers As Boolean = True
Dim sqlPersonInfo As String = ""
Dim sqlPersonInfoList As New List(Of String)()
Dim recordCount As Integer = 0

' Check if the CSV file exists.
If Not File.Exists(filePath) Then

    ' Message stating CSV file could not be located.
    Console.WriteLine("Could not locate the CSV file.")

    ' Stop program execution.
    End

End If

' Assign the CSV file to a reader object.
Using reader As New Microsoft.VisualBasic.
                      FileIO.TextFieldParser(filePath)

    ' Specify the delimiter.
    reader.TextFieldType = FileIO.FieldType.Delimited
    reader.SetDelimiters(",")

    ' Process the contents of the reader object.
    While Not reader.EndOfData

        Try

            ' Retrieve a row of data from the reader object.
            currentRow = reader.ReadFields()

            ' Check for correct column headers if first row.
            If headers = True Then

                If currentRow(0) <> "firstname" Or
                   currentRow(1) <> "lastname" Or
                   currentRow(2) <> "title" Or
                   currentRow(3) <> "dob" Then

                    ' Message stating incorrect CSV file headers.
                    Console.WriteLine("Incorrect CSV file headers.")

                    ' Stop program execution.
                    End

                Else

                    headers = False

                End If

            Else

                ' Construct the insert statement.
                sqlPersonInfo = "INSERT INTO person "
                sqlPersonInfo += "(firstname, lastname, title, dob) "
                sqlPersonInfo += "VALUES ('" & currentRow(0) & "', "
                sqlPersonInfo += "'" & currentRow(1) & "', "
                sqlPersonInfo += "'" & currentRow(2) & "', "
                sqlPersonInfo += "'" & currentRow(3) & "')"

                ' Add the insert statement to the list.
                sqlPersonInfoList.Add(sqlPersonInfo)

            End If

        Catch ex As Microsoft.VisualBasic.
                    FileIO.MalformedLineException

            ' Confirm error reading CSV file and exit.
            Console.WriteLine("Error reading CSV file.")
            End

        End Try

    End While

    ' Close the reader object.
    reader.Close()

End Using

' Database connection variable.
Dim connect = New SqlConnection(
    "Server=localhost\MSSQLSERVERDEMO; Database=Demo;" &
    "User Id=DemoUN; Password=DemoPW")

Try

    ' Connect to database.
    connect.Open()

Catch ex As Exception

    ' Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.")
    End

End Try

Try

    ' Insert the person information from the list.
    For Each personSQL As String In sqlPersonInfoList

        ' Define and execute the SQL command.
        Dim sqlInsert As New SqlCommand(personSQL, connect)
        sqlInsert.ExecuteNonQuery()

        ' Increment the record count.
        recordCount += 1

    Next

    ' Provide feedback on the number of records added.
    If recordCount = 0 Then

        Console.WriteLine("No new person records added.")

    ElseIf recordCount = 1 Then

        Console.WriteLine(recordCount & " person record added.")

    Else

        Console.WriteLine(recordCount & " person records added.")

    End If

Catch ex As Exception

    ' Confirm error adding person information and exit.
    Console.WriteLine("Error adding person information.")
    End

Finally

    ' Close the database connection.
    connect.Close()

End Try

The contents of the ‘person’ table now looks as follows.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Bloggs Mrs 1985-05-19
6 Zoe Davis Miss 1979-07-11
7 Tom Ingram Mr 1971-10-04
8 Karen Thomas Mrs 1969-03-08
9 Samantha Yates Miss 1995-08-27