Solved

Importing from text to tables

Posted on 2004-09-15
9
171 Views
Last Modified: 2012-05-05
I will do my best to explain what I am trying to accomplish. We run a script during domain logins that collects misc data on users such as IP and computer name etc. I use an access database to import this information and store it into a table...this procedure was easy to setup. I then take that table and import it into a SQL database for an app I wrote to query this information. Below is the code to do this...but what I was wondering is if someone would be kind enough to help me put it into vb.net so I can do this and skip the access database altogether.

// Begin Access Code

Private Sub Command0_Click()
On Error GoTo Error_Command0_Click
Dim rsTemp As DAO.Recordset
Dim rs As DAO.Recordset

Set rsTemp = CurrentDb.OpenRecordset("tbl_Import")
Set rs = CurrentDb.OpenRecordset("tbl_Users")
rsTemp.MoveFirst
Do Until rsTemp.EOF

 With rs
      .AddNew
      .Fields("domain") = rsTemp.Fields(0)
       rsTemp.MoveNext
      .Fields("username") = rsTemp.Fields(0)
       rsTemp.MoveNext
      .Fields("computer") = rsTemp.Fields(0)
       rsTemp.MoveNext
      .Fields("ip") = rsTemp.Fields(0)
       rsTemp.MoveNext
      .Update
   End With
   
   
If Not rsTemp.EOF Then
    Do While Trim(rsTemp.Fields(0)) = "" Or IsNull(rsTemp.Fields(0)) And Not rsTemp.EOF
      rsTemp.MoveNext
    Loop
End If

Loop
Set rsTemp = Nothing
Set rs = Nothing
Set rsDB = Nothing

Error_Command0:
    Exit Sub

Error_Command0_Click:
    MsgBox Err.Description
    Resume Error_Command0

End Sub

// End Code

Thanks for any help guys. I appreciate it
0
Comment
Question by:JoeDW
  • 5
  • 4
9 Comments
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 12070177
You will need to import directly from the file into sql server using .net.
To help you do this I would need the file format.
0
 
LVL 1

Author Comment

by:JoeDW
ID: 12071440
The files are .log files that I manually go through in get the lines and put them into a .txt file for import. They look like this:

Each log file contains 1 emtpy line then 4 lines like this.

~space
domain
username
computername
ip

I copy the 4 lines from all 150 files and put them into a txt file that looks like this.

domain
username
computername
ip
domain
username
computername
ip

etc...

Does that help at all?
0
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 12075065
Sure.  I'm writing it now.
0
 
LVL 16

Accepted Solution

by:
RobertRFreeman earned 500 total points
ID: 12076233
Here is the code.  You'll have to change the table name, column names, parameter datatype/length, database connection, and file path.

        'The calling code
        Dim ds As DataSet
        ds = GetData("C:\temp\testimport.txt")
        Me.DataGrid1.DataSource = ds.Tables("DataTable").DefaultView
        SaveData(ds)

    Private Function GetData(ByVal path As String) As DataSet
        Dim ds As New DataSet("Data")
        ds.Tables.Add("DataTable")
        Dim myTable As DataTable = ds.Tables("DataTable")
        myTable.Columns.Add("DomainValue")
        myTable.Columns.Add("Username")
        myTable.Columns.Add("Computername")
        myTable.Columns.Add("IP")
        Dim myString As String
        Dim myRow As DataRow
        Dim myItem As Object
        Dim myColumnIndex As Integer = 0
        Dim sr As New System.IO.StreamReader(path)
        myString = sr.ReadLine()
        Do Until myString Is Nothing
            myRow = myTable.NewRow()
            For myColumnIndex = 0 To myTable.Columns.Count - 1
                myRow(myColumnIndex) = CStr(myString)
                myString = sr.ReadLine()
            Next
            myTable.Rows.Add(myRow)
        Loop
        Return ds
    End Function

    Private Sub SaveData(ByVal ds As DataSet)
        Dim myConnection As New SqlClient.SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=localhost")
        Dim myCommand As New SqlClient.SqlCommand("Insert DataTable (DomainValue, Username, Computername, IP) Values (@DomainValue, @Username, @Computername, @IP)")
        Dim myAdapter As New SqlClient.SqlDataAdapter
        myCommand.Connection = myConnection
        myAdapter.InsertCommand = myCommand
        myAdapter.TableMappings.Add("DataTable", "DataTable")
        myCommand.Parameters.Add("@DomainValue", SqlDbType.VarChar, 50, "DomainValue")
        myCommand.Parameters.Add("@Username", SqlDbType.VarChar, 50, "Username")
        myCommand.Parameters.Add("@ComputerName", SqlDbType.VarChar, 50, "ComputerName")
        myCommand.Parameters.Add("@IP", SqlDbType.VarChar, 50, "IP")
        myAdapter.Update(ds.Tables("DataTable"))
    End Sub
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 1

Author Comment

by:JoeDW
ID: 12078330
Everything works great...I am impressed with how easy it was to get going and how flawless it works. Thanks a million, you deserve these points.
0
 
LVL 1

Author Comment

by:JoeDW
ID: 12078440
I have a small question as well if you wouldnt mind answering...How would I go about clearing the table first before importing...the contents of the table just need to be cleared so when I import this into there will be fresh information.

Thanks again
0
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 12078481
   Private Sub ClearData()
        Dim myConnection As New SqlClient.SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=localhost")
        Dim myCommand As New SqlClient.SqlCommand("Delete DataTable", myConnection)
        myCommand.ExecuteNonQuery()
    End Sub
0
 
LVL 1

Author Comment

by:JoeDW
ID: 12079393
Thanks, but there was a little problem with it wanting an open sql connection to perform ExecuteNonQuery...it may have been the nature of the app Im working on but I added

myCommand.Connection.Open()

above the ExecuteNonQuery and it seemed to work fine...just thought I would let you know..Thanks again for the help and quick responses.
0
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 12079418
Yeah, forgot that.  It's good form to open and close your connections around adapter fill/update or command executions.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
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

18 Experts available now in Live!

Get 1:1 Help Now