Solved

Text File Parsing Into SQL

Posted on 2006-10-27
14
390 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now