Solved

how can i extract values from a text file( based on some condition)  into vb program?

Posted on 2004-09-08
25
172 Views
Last Modified: 2010-04-23
I am having a huge text file with set of records like

temp.txt
---------------------
empno,ename,salary
100,"name1",2000
101,"name2",3000
102,"name2",4000
103,"name5",1111
...
....
........
--------------------------------

in the above txtfile, all the fields are seperated by comma.
each record is seperated by a carriage return



based on the empno in my VB.NET program,i want to extract the ename from the .txt file.

for eg. in my visual basic program,

dim empno as string = "100"

'get the ename where empno=100
'open the temp.txt file,search for empno (here its 100) in all the records, and get the ename(name1).

any idea how can i do this using Visual basic.NET?


thanks for your reply,

reg
0
Comment
Question by:vihar123
  • 7
  • 6
  • 5
  • +1
25 Comments
 
LVL 8

Expert Comment

by:bramsquad
ID: 12008938
Dim reader As New StreamReader(path), strReadLine As String
dim empno as string
dim ename as string
dim third_val as string

Do While Not strReadLine Is Nothing
     'make sure to not pull in a null line
     If strReadLine <> "" Then
          'get all your info here        
     End If
     strReadLine = reader.ReadLine
Loop

~b
0
 
LVL 8

Expert Comment

by:bramsquad
ID: 12008964
note that the variable 'path' is the full path name to the textfile you want to import

and i forgot you have to have a value to get before the loop, so like this...

Dim reader As New StreamReader(path), strReadLine As String
dim empno as string
dim ename as string
dim third_val as string

strReadLine = reader.ReadLine
Do While Not strReadLine Is Nothing
     'make sure to not pull in a null line
     If strReadLine <> "" Then
          'get all your info here        
     End If
     strReadLine = reader.ReadLine
Loop

~b
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12009047
Public Class Form1
    Inherits System.Windows.Forms.Form

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim fileName As String
        fileName = Application.StartupPath & "\myData.txt"
        TextBox2.Text = getEmployeeName(fileName, TextBox1.Text)
    End Sub

    Private Function getEmployeeName(ByVal dataFile As String, ByVal employeeNumber As String) As String
        If System.IO.File.Exists(dataFile) Then
            Dim sr As New System.IO.StreamReader(dataFile)
            Dim values() As String
            Dim inputLine As String

            inputLine = sr.ReadLine
            While Not (inputLine Is Nothing)
                values = Split(inputLine, ",")
                If values(0).Equals(employeeNumber) Then
                    sr.Close()
                    Return values(1).Replace("""", "")
                End If
                inputLine = sr.ReadLine
            End While
            sr.Close()
        Else
            MsgBox(dataFile, MsgBoxStyle.Critical, "File Not Found")
        End If
    End Function

End Class
0
 
LVL 8

Expert Comment

by:bramsquad
ID: 12009151
and if your data will always have the same format, you can use this within the if statement to get your data

        empno = Microsoft.VisualBasic.Left(strreadline, strreadline.IndexOf(","))
        strreadline = Mid(strreadline, strreadline.IndexOf(",") + 2)
        ename = Microsoft.VisualBasic.Left(strreadline, strreadline.IndexOf(","))
        salary = Microsoft.VisualBasic.Left(strreadline, strreadline.IndexOf(","))

0
 
LVL 8

Expert Comment

by:bramsquad
ID: 12009168
oops...my bad, this is what you want

        empno = Microsoft.VisualBasic.Left(strreadline, strreadline.IndexOf(","))
        strreadline = Mid(strreadline, strreadline.IndexOf(",") + 2)
        ename = Microsoft.VisualBasic.Left(strreadline, strreadline.IndexOf(","))
        salary = Mid(strreadline, strreadline.IndexOf(",") + 2)
0
 
LVL 8

Accepted Solution

by:
bramsquad earned 168 total points
ID: 12009223
i guess i need to finish reading all of the question...:)

this will get all the values, if you want ename only when empno is equal to '100', just add a simple if statement

if empno  = "100" then
        strreadline = Mid(strreadline, strreadline.IndexOf(",") + 2)
        ename = Microsoft.VisualBasic.Left(strreadline, strreadline.IndexOf(","))
        salary = Mid(strreadline, strreadline.IndexOf(",") + 2)
end if

'now you can do whatever you want with those values
0
 
LVL 4

Expert Comment

by:srcalc
ID: 12010732
assuming that your text file is strictly in this format:

empno,ename,salary
100,"name1",2000
101,"name2",3000
102,"name2",4000
103,"name5",1111

Then you can use the following code, you need to change the "PUT SOURCE HERE" to the folder that your txt file is in. This will then put up a message box for each row that for which empno = "100"


                Dim empno As String = "100"
                Dim txtConnect As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PUT SOURCE HERE;Extended Properties='text;HDR=Yes;FMT=Delimited'")
                Dim txtAdapt As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM temp.txt WHERE empno = " & empno, txtConnect)
                Dim FillTable As New System.Data.DataTable
                txtAdapt.Fill(FillTable)
                For Each row As DataRow In FillTable.Rows
                    MsgBox(row.Item(0).ToString)
                Next

By using this code you dont have to worry about storing the entire text file in your memory, as you said it was very large. If you need more help, you may want to look here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp
0
 
LVL 4

Assisted Solution

by:srcalc
srcalc earned 166 total points
ID: 12010746
oops, small typo. This line:

MsgBox(row.Item(0).ToString)

should be this:

MsgBox(row.Item("ename").ToString)
0
 

Author Comment

by:vihar123
ID: 12011110
Hi,

is it possible to use some classes like arraylist,hashtable etc...
what my idea is:
store all the values in a arraylist ....
because within my program,i have to extract names based on empno. using a for loop..

something like

for each empno....
'open the text file check for the empno,and get ename.
next


which is good?
either uisng some arraylist etc..  or others?

but i dont know whether its possible or not  :(

I dont want to use any DATABASE Connection.but i dont know whether its possible or not  :(
I dont want to use any DATABASE Connection.


any ideas?


thanks for your replies...


reg
0
 
LVL 85

Assisted Solution

by:Mike Tomlinson
Mike Tomlinson earned 166 total points
ID: 12011494
If you are going to store all your data in memory then a HashTable would make the most sense since you are doing a lookup by a unique key.

Here is an example of how to read in the data and store it in a hashtable.  Lookups are done without a For...Next loop as the ID is type into TextBox1.

Regards,

Idle_Mind

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents TextBox2 As System.Windows.Forms.TextBox
    Friend WithEvents TextBox3 As System.Windows.Forms.TextBox
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.TextBox1 = New System.Windows.Forms.TextBox
        Me.TextBox2 = New System.Windows.Forms.TextBox
        Me.TextBox3 = New System.Windows.Forms.TextBox
        Me.Label1 = New System.Windows.Forms.Label
        Me.Label2 = New System.Windows.Forms.Label
        Me.Label3 = New System.Windows.Forms.Label
        Me.SuspendLayout()
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(64, 8)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(136, 20)
        Me.TextBox1.TabIndex = 0
        Me.TextBox1.Text = "TextBox1"
        '
        'TextBox2
        '
        Me.TextBox2.Enabled = False
        Me.TextBox2.Location = New System.Drawing.Point(64, 40)
        Me.TextBox2.Name = "TextBox2"
        Me.TextBox2.Size = New System.Drawing.Size(136, 20)
        Me.TextBox2.TabIndex = 1
        Me.TextBox2.Text = "TextBox2"
        '
        'TextBox3
        '
        Me.TextBox3.Enabled = False
        Me.TextBox3.Location = New System.Drawing.Point(64, 64)
        Me.TextBox3.Name = "TextBox3"
        Me.TextBox3.Size = New System.Drawing.Size(136, 20)
        Me.TextBox3.TabIndex = 3
        Me.TextBox3.Text = "TextBox3"
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(8, 8)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(48, 24)
        Me.Label1.TabIndex = 4
        Me.Label1.Text = "ID:"
        Me.Label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(8, 40)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(48, 16)
        Me.Label2.TabIndex = 5
        Me.Label2.Text = "Name:"
        Me.Label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        '
        'Label3
        '
        Me.Label3.Location = New System.Drawing.Point(8, 64)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(48, 16)
        Me.Label3.TabIndex = 6
        Me.Label3.Text = "Salary:"
        Me.Label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(208, 94)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.TextBox3)
        Me.Controls.Add(Me.TextBox2)
        Me.Controls.Add(Me.TextBox1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Class EmployeeData
        Public ID As String
        Public Name As String
        Public salary As String
    End Class

    Private employees As Hashtable = New Hashtable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim fileName As String
        fileName = Application.StartupPath & "\myData.txt"

        importEmployeeData(fileName)
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        Dim ed As EmployeeData

        If employees.ContainsKey(TextBox1.Text) Then
            ed = employees.Item(TextBox1.Text)
            TextBox2.Text = ed.Name
            TextBox3.Text = ed.salary
        Else
            TextBox2.Text = ""
            TextBox3.Text = ""
        End If
    End Sub

    Private Sub importEmployeeData(ByVal datafile As String)
        Dim ed As EmployeeData

        If System.IO.File.Exists(datafile) Then
            Dim sr As New System.IO.StreamReader(datafile)
            Dim values() As String
            Dim inputLine As String

            employees = New Hashtable
            inputLine = sr.ReadLine
            While Not (inputLine Is Nothing)
                values = Split(inputLine, ",")
                ed = New EmployeeData
                ed.ID = values(0)
                ed.Name = values(1).Replace("""", "")
                ed.salary = values(2)
                employees.Add(ed.ID, ed)
                inputLine = sr.ReadLine
            End While
            sr.Close()
            MsgBox("Done Importing Employee File: " & datafile, MsgBoxStyle.Information, "Import Complete")
        Else
            MsgBox(datafile, MsgBoxStyle.Critical, "File Not Found")
        End If
    End Sub

End Class
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12011519
>> I dont want to use any DATABASE Connection.but i dont know whether its possible or not  :(
>> I dont want to use any DATABASE Connection.

It would also be very easy to store all the data in memory at once using a DataSet.  This does not require any kind of external drivers or databases, it is all built into the .Net framework.  With a DataSet, you would add one Table and then setup your three columns in it.  Then you would add your data in rows to the table.  You can then easily bind your DataSet to a DataGrid and see all your data at once and edit it (add/remove/change).

Regards,

Idle_Mind
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 4

Expert Comment

by:srcalc
ID: 12011927
Are you *sure* that you don't want to use a database connection? It is certainly the easiest and cleanest way to access and store your data. It can also help you display it if necessary.
0
 

Author Comment

by:vihar123
ID: 12017819
Hi,

Thanks for your replies...

first i am trying to test using Hashtables with code from  our friend  "Idle_Mind"

The problem in my .txt file

some of empno's are redundant.
what i am trying to do is,if empno is redundant,dont add to hash table. and show some message that this empno is redundant ....


any idea?

0
 

Author Comment

by:vihar123
ID: 12017933
for example

if employees data is like

empno ename sal
100  name1    $ 1000
102  name3    $2320
105  name4    $5000
100  name1   $1000

record of name1 is redundant.

in this case i shouldNOT  add details of name1 to my hashtable as it is redundant.just display message that "name1 is redundant."



0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12018071
Just change the importEmployeeData() sub to this:

    Private Sub importEmployeeData(ByVal datafile As String)
        Dim ed As EmployeeData

        If System.IO.File.Exists(datafile) Then
            Dim sr As New System.IO.StreamReader(datafile)
            Dim values() As String
            Dim inputLine As String

            employees = New Hashtable
            inputLine = sr.ReadLine
            While Not (inputLine Is Nothing)
                values = Split(inputLine, ",")
                ed = New EmployeeData
                ed.ID = values(0)
                ed.Name = values(1).Replace("""", "")
                ed.salary = values(2)
                If Not employees.ContainsKey(ed.ID) Then
                    employees.Add(ed.ID, ed)
                Else
                    MsgBox(inputLine, MsgBoxStyle.Information, "Redundant ID Found")
                End If
                inputLine = sr.ReadLine
            End While
            sr.Close()
            MsgBox("Done Importing Employee File: " & datafile, MsgBoxStyle.Information, "Import Complete")
        Else
            MsgBox(datafile, MsgBoxStyle.Critical, "File Not Found")
        End If
    End Sub

Regards,

Idle_Mind
0
 

Author Comment

by:vihar123
ID: 12018440
Hi,

when i tried your code,
it still adding record atleast once if redundant record exists.

what i want is

if any redundant record exists(based on ID) dont add that record to the hashtable.

before adding it should check whether any record is redundant,if not add to hashtable else display some message

0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12020826
No problem.  Just change the If...Else...End If to this:

    If Not employees.ContainsKey(ed.ID) Then
        employees.Add(ed.ID, ed)
    Else
        employees.Remove(ed.ID)
        MsgBox(inputLine, MsgBoxStyle.Information, "Redundant ID Found")
    End If

Idle_Mind
0
 

Author Comment

by:vihar123
ID: 12038723
Hi,
ok now we have all the employees list in a hashtable ( empno,empname).

this i will do in form load event,because ,i have to check for plenty of files.

lest say i have an empno =100

dim empno as string = 100

get the name from the hashtable list where empno = 100               'this is my last step

any methods in hashtable to get the values?
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12038743
Dim ed As EmployeeData
Dim empno As String = "100"

If employees.ContainsKey(empno) Then
    ed = employees.Item(empno)
    ' Now you can do something with these values
    ' ed.ID
    ' ed.Name
    ' ed.salary
Else
    MsgBox(empno, MsgBoxStyle.Information, "ID Not Found")
End if
0
 

Author Comment

by:vihar123
ID: 12044908
hi,


still i am facing some problems with hashtable.


records are added to hashtable   ,if the text file is having redundant records,

i mean if  100th record is redundant,it still added once.

but i dont want any redundant records.i mean in this case even 100th record also should not be added.....

0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12045074
This should fix that problem:

    Private Sub importEmployeeData(ByVal datafile As String)
        Dim ed As EmployeeData

        If System.IO.File.Exists(datafile) Then
            Dim sr As New System.IO.StreamReader(datafile)
            Dim values() As String
            Dim inputLine As String
            Dim duplicateKey As String
            Dim duplicateKeys As ArrayList = New ArrayList

            employees = New Hashtable
            inputLine = sr.ReadLine
            While Not (inputLine Is Nothing)
                values = Split(inputLine, ",")
                ed = New EmployeeData
                ed.ID = values(0)
                ed.Name = values(1).Replace("""", "")
                ed.salary = values(2)
                If Not employees.ContainsKey(ed.ID) Then
                    employees.Add(ed.ID, ed)
                Else
                    duplicateKeys.Add(ed.ID)
                    MsgBox(inputLine, MsgBoxStyle.Information, "Redundant ID Found")
                End If
                inputLine = sr.ReadLine
            End While
            sr.Close()

            For Each duplicateKey In duplicateKeys
                If employees.ContainsKey(duplicateKey) Then
                    employees.Remove(duplicateKey)
                End If
            Next
            MsgBox("Done Importing Employee File: " & datafile, MsgBoxStyle.Information, "Import Complete")
        Else
            MsgBox(datafile, MsgBoxStyle.Critical, "File Not Found")
        End If
    End Sub
0
 
LVL 4

Expert Comment

by:srcalc
ID: 12244884
I think that Idle_Mind, bramsquad and I all gave vihar123 helpful information to complete his original question. I would suggest a split.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

706 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

17 Experts available now in Live!

Get 1:1 Help Now