[Webinar] Streamline your web hosting managementRegister Today

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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 …
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…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

591 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