Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

Importing from text to tables

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
JoeDW
Asked:
JoeDW
  • 5
  • 4
1 Solution
 
RobertRFreemanCommented:
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
 
JoeDWAuthor Commented:
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
 
RobertRFreemanCommented:
Sure.  I'm writing it now.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
RobertRFreemanCommented:
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
 
JoeDWAuthor Commented:
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
 
JoeDWAuthor Commented:
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
 
RobertRFreemanCommented:
   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
 
JoeDWAuthor Commented:
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
 
RobertRFreemanCommented:
Yeah, forgot that.  It's good form to open and close your connections around adapter fill/update or command executions.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now