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

http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/5bf3e812-060f-4636-a72c-02b2b168a7c9

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

        Try
            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
                Label1.Refresh()

            End While

            Label1.Text = "Finish: " & counter
            Label1.Refresh()
        Finally
            If Not Nothing Is rdr Then
                rdr.Close()
            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)
        sqlConn.Open()
        cmd.ExecuteNonQuery()
        sqlConn.Close()

    End Sub

End Class
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: