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
spec (column positions) but don't know how to use it.
Thanks
Bill
-
-
Re: read fixed column flat file into an SQLserver table
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("INS ERT INTO MyTable " + _
"(LastName, FirstName) " + _
"VALUES (@lastname, @firstname)", sqlcon)
sqlcmd.Paramete rs.Add("@lastna me", SqlDbType.VarCh ar, 255)
sqlcmd.Paramete rs.Add("@firstn ame", SqlDbType.VarCh ar, 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.Paramete rs("@lastname") .Value = line.Substring( 1, 25)
sqlcmd.Paramete rs("@firstname" ).Value = line.Substring( 26, 25)
sqlcmd.ExecuteN onQuery()
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..Fin ally 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" <billn_nospam_p lease@jaco.com> wrote in message
news:OVJG%238yg FHA.3220@TK2MSF TNGP10.phx.gbl. ..[color=blue]
>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
>
>[/color]
-
Re: read fixed column flat file into an SQLserver table
Lookup BULK INSERT in the MSDN Library's Transaction-SQL Reference, it lets
you import data from user-defined files into a database table. This is
really a action you should do in Transaction-SQL and not in VB.NET.
"Bill Nguyen" <billn_nospam_p lease@jaco.com> skrev i melding
news:OVJG%238yg FHA.3220@TK2MSF TNGP10.phx.gbl. ..[color=blue]
>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
>
>[/color]
Comment
Comment