Solved

Importing from text to tables

Posted on 2004-09-15
9
172 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 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