Import Data from Fixed Text File to MS SQL with VB.NET

25 Oct

This blog have a great example of importing Fixed Text File

The Code In Case is deleted:

Imports System.IO
Imports System.Data.SqlClient

Public Class Form1
    Private Const cs As String = "Server=yourservername;Database=yourdatabase;Uid=user;Pwd=password;"

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim rdr As System.IO.StreamReader = Nothing
        Dim counter As Integer = 0

            rdr = New System.IO.StreamReader("C:\FIXED.TXT")

            While Not rdr.EndOfStream
                counter = counter + 1

                Dim str As String = rdr.ReadLine()

                Dim val1 As String = str.Substring(0, 8)  'adjust according to your file
                Dim val2 As String = str.Substring(10, 16) ' adjust according to your file
                Dim val3 As String = str.Substring(27, 10)
                Dim val4 As String = str.Substring(39, 3)

                ''Add Code to save to the database using insert query
                SaveToDatabase(val1, val2, val3, val4)
                Label1.Text = counter

            End While

            Label1.Text = "Finish: " & counter
            If Not Nothing Is rdr Then
            End If
        End Try
    End Sub

    Private Sub SaveToDatabase(ByVal value1 As String, ByVal value2 As String, ByVal value3 As String, ByVal value4 As String)
        Dim sqlConn As New SqlConnection(cs)
        Dim cmd As New SqlCommand("INSERT INTO FIXED VALUES ('" & value1 & "','" & value2 & "','" & value3 & "','" & value4 & "')", sqlConn)

    End Sub

End Class

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: