You probably want to use a StreamReader to read in the lines of text from
the fixed-width file into a string variable. Then create SQL Insert
statements that use the Substring() of the various lines as input. You can
also use DTS to Import the data from your fixed-width file via Enterprise
Manager (probably easier).
' open a streamreader above this (named sr in this example)
' Then we process each line
Dim sqlcon As New SqlConnection ("connection string")
sqlcon.Open()
Dim sqlcmd As New SqlCommand("INSERT INTO MyTable " + _
"(LastName, FirstName) " + _
"VALUES (@lastname, @firstname)", sqlcon)
sqlcmd.Parameters.Add("@lastname", SqlDbType.VarChar, 255)
sqlcmd.Parameters.Add("@firstname", SqlDbType.VarChar, 255)
Dim line As String
line = sr.ReadLine()
Do While Not (line Is Nothing)
' For example, assume last name is columns 1 - 25 and
' firstname is columns 26 - 50 of flat file
sqlcmd.Parameters("@lastname").Value = line.Substring(1, 25)
sqlcmd.Parameters("@firstname").Value = line.Substring(26, 25)
sqlcmd.ExecuteNonQuery()
line = sr.ReadLine()
Loop
sqlcmd.Dispose()
sqlcon.Dispose()
' Close your streamreader, etc. below
This is a really simplified example, but it gives you the basic idea.
Adding Try..Catch..Finally blocks and the StreamReader code is left as an
exercise for the reader. Also, if you're loading into an Access database,
you'll use the OleDbCommand and OleDbConnection objects instead of
SqlCommand and SqlConnection.
"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:OV****************@TK2MSFTNGP10.phx.gbl...
I need to parse a text file with fixed columns into a table. I have the
file spec (column positions) but don't know how to use it.
Thanks
Bill
