Solved

Text File Parsing Into SQL

Posted on 2006-10-27
14
410 Views
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.
0
Comment
Question by:sc230
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 8

Assisted Solution

by:bman9111
bman9111 earned 200 total points
ID: 17824177
0
 
LVL 5

Accepted Solution

by:
DEEPESH earned 50 total points
ID: 17824738
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)
       i=19
       secondpart = mid(line ,i,53)
  Wend
End While

  fso.Close()

End Sub

Something like this might help you
0
 
LVL 5

Assisted Solution

by:consulteware
consulteware earned 200 total points
ID: 17824997
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 50 total points
ID: 17825041
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))
            result.Columns.Add(dc)
        Next

        '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)))
            Next
            'add row to table
            result.Rows.Add(dr)
            'skip new line chars
            br.ReadChars(endLine)
        Loop

        br.Close()

        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.

Roger
0
 

Author Comment

by:sc230
ID: 17826408
@bman9111
One of those links did not work and the other was no help, thanks.

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

@consulteware
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.

@Sancler
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.
0
 
LVL 8

Expert Comment

by:bman9111
ID: 17826449
try the one link again. It is working for me..
0
 
LVL 8

Expert Comment

by:bman9111
ID: 17826454
0
 
LVL 8

Expert Comment

by:bman9111
ID: 17826463
0
 
LVL 8

Expert Comment

by:bman9111
ID: 17826477
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.

0
 

Author Comment

by:sc230
ID: 17836226
This is a one time thing.

how would I go about going into the dts wizzard and just importing?
0
 
LVL 5

Expert Comment

by:consulteware
ID: 17836364
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.
0
 

Author Comment

by:sc230
ID: 17836892
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?

FULL PROJECT CODE:

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
                Application.DoEvents()
                catnum = Mid(line, i, 13)
                i = 24
                Application.DoEvents()
                description = Mid(line, i, 41)
                Application.DoEvents()

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

                i = linelength
            End While
        End While
        fso.Close()
    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

        db_connection.Open()

        sql_command.CommandText = "USE dbClientPOS"
        sql_command.ExecuteNonQuery()

        Application.DoEvents()

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

Expert Comment

by:bman9111
ID: 17847599
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.

http://www.sqldts.com/?276,1
0
 

Author Comment

by:sc230
ID: 17852843
Ultimately I ended up using a CSV and parsing that.

Splitting points to those who helped.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question