Improve company productivity with a Business Account.Sign Up

x
?
Solved

Importing from text to tables

Posted on 2004-09-15
9
Medium Priority
?
182 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 16

Accepted Solution

by:
RobertRFreeman earned 2000 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
 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
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 …
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …

608 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