We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Text File Parsing Into SQL

sc230 asked
Medium Priority
Last Modified: 2012-05-05
This is fairly basic.  Simply, I have a few text files, databases from an old legacy system, and I need to parse through the information in these files with hundreds to thousands of lines.  Each line is delimited by a certain ammount of characters until it hits the next category.

For example:
MISCF              TEXT                                                 103      0.00      0.00   0    0
ES20678XXX         TEXT                                                 125      0.00     42.95   1    0

Each field is a set ammount of characters (19, 53, 3, 10, 10, 4, 7).

My question, is how would I go about reading through the entire file, line by line and parsing each "field", as set by a certain ammount of characters per line, and then storing each field into a sql database, where each field is its own in its own record in a new table.

All help is greatly appreciated.
Watch Question

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Private Sub ReadFromFile(byval filePath as string)

     Dim fso As New IO.StreamReader(filePath)
     dim i as int
     dim linelength as int
     dim firstpart as string
     dim secondpart as string
While fso.Peek > -1  
   Dim line As String = fso.ReadLine()
   linelength = len(line)
  while i <> linelength
       firstpart = mid(line ,i,19)
       secondpart = mid(line ,i,53)
End While


End Sub

Something like this might help you
According to what I saw you already have an example in VB here in how to do this (But I do not know if you have any VB compiler).

What are exactly your needs? Do you need an SQL Syntax to do the job?

It is SQL Server, Oracle, MySQL, or Other?

Can you be more specific to help you.
Here's a basic function I use to read fixed width files.  In this form, I use it as a template, modifying it for the purposes of specific apps - e.g. by altering the signature to pass in column names and or specific datatypes for columns.  But it illustrates an approach you might like to think about.  I've added some comments.

    Private Function readFile(ByVal fileName As String, ByVal width() As Integer, ByVal lineEnd As String) As DataTable

        'make data table for results
        Dim result As New DataTable
        'give it right number of columns
        For i As Integer = 0 To width.Length - 1
            Dim dc As New DataColumn("Col" & i.ToString, GetType(String))

        'how many chars does new line take?
        Dim endLine As Integer = lineEnd.Length

        'open reader
        Dim br As New BinaryReader(File.Open(fileName, FileMode.Open))

        'assume file has full lines
        Do While br.PeekChar <> -1
            'make data row
            Dim dr As DataRow = result.NewRow
            'fill fields at appropriate length
            For i As Integer = 0 To width.Length - 1
                dr(i) = New String(br.ReadChars(width(i)))
            'add row to table
            'skip new line chars


        Return result

    End Function

Incidentally, I tested it on the data from your post and - although this might be a function of you copying and pasting the data into the post, rather than of the data file itself - the last field/column looks to have a width of 5 rather than 7, with the other two characters serving as the newline marker.



One of those links did not work and the other was no help, thanks.

I will give that a try and mess around with it and see what works best.

I don't as much need the sql syntax, I can do that .. mainly I just need something to parse through the text file, and store the data it parses from each field as a string variable, and while its still in that instance of a loop reading that single line, it will perform sql queries and input the data it parsed into the string variables, then rinse and repeat.  It is MSSQL, and I will be using VB.NET.

I will give yours a try, I don't want it to go into a data table, as much as I just want it to read the text file then execute sql queries.

try the one link again. It is working for me..

also if you were to open that text file and create a .csv file that would be nicer to import to sql as well.

Is that a possible idea for you.

Most of the links i am providing gives you some lead way of how to do what ur asking.

if this is a one time thing I would better yet go into sql and then the dts wizard and do an import. then you have the choice to pick the text file as a source then the destination to sql. This works great then you can manipulate the field names as desired.

I do this all the time to save the agrevation of writing code.

Just a thought. I am trying to help you as much as possible. Hope something helps to earn some points.


This is a one time thing.

how would I go about going into the dts wizzard and just importing?
If the user has SQL Server installed it has DTS if not does not have it. If has SQL Server perhaps BCP could be another solution.


So this is what I ended up doing, except it's not reading all the lines in the text file, it's only reating 5 or 6 rows in the sql.  Any idea why?


Option Explicit On
Imports System.Data.SqlClient

Public Class Form1
    Dim sql_command As New SqlCommand()
    Dim SQLConnectString As String = "User Id=sa;Password=pass;Data Source=(local)\DB"
    Dim db_connection As New SqlConnection(SQLConnectString)
    Dim Result As SqlDataReader

    Private Sub ReadFromFile(ByVal filePath As String)
        Dim fso As New IO.StreamReader(filePath)
        Dim i As Integer
        Dim linelength As Integer
        Dim vendor As String
        Dim catnum As String
        Dim description As String
        While fso.Peek > -1
            Dim line As String = fso.ReadLine()
            linelength = Len(line)
            While i <> linelength
                i = 1
                vendor = Mid(line, i, 11)
                i = 11
                catnum = Mid(line, i, 13)
                i = 24
                description = Mid(line, i, 41)

                sql_command.CommandText = "INSERT INTO [dbo].[parse_videos] ([catnum], [description], [vendor]) VALUES ('" & catnum & "', '" & description & "', '" & vendor & "')"

                i = linelength
            End While
        End While
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        sql_command.Connection = db_connection
        sql_command.CommandType = CommandType.Text
        sql_command.CommandTimeout = 60


        sql_command.CommandText = "USE dbClientPOS"


        ReadFromFile("E:\Documents and Settings\mrhoades\Desktop\VIDEOS.txt")
    End Sub
End Class

to do a dts. go into the enterprise manager for sql and locate the database of choice. Then locate the icon that looks like a wand and select data tranformation and select importing then go from there.

really easy.



Ultimately I ended up using a CSV and parsing that.

Splitting points to those who helped.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.