TXT.Files

[Scott_Atkins]

Good Evening Guys and Girls,

Hope all is well...

Just looking for a bit more of an understanding on how i could possibly achieve this!
I have 47 TXT.Files and they roughly have about 500+ words iv taken a snippet of a bit of a TXT file so you can see what information that store in them please see below..

24/06/2010 19:18:23 /IServerManager.GetPublicKey (timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)

24/06/2010 19:18:23 /IServerManager.Login (timings: authenticate=0 authorise=0 execute=4524029 logError=0 teardown=468003 total=4992032)

24/06/2010 19:20:31 /IServerManager.GetPublicKey (timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)

As you can see there is alot of information but what i want to be able to do is reference keys words out of them,
Iv just finished writing a application within VB.net that allows me to read txt.files and then im giving the user the option to upload them, which will be connected to a SQL database through a connection string.

Iv already created the database in SQL see below and you will notice that the column names reference bits within the txt.file above.

CREATE TABLE [dbo].[MedWayFiles](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[Date and Time] [datetime] NULL,
[Commands] [nvarchar](50) NULL,
[Timings] [nvarchar](50) NULL,
[Authenticate] [nvarchar](50) NULL,
[Authorise] [nvarchar](50) NULL,
[Execute] [nvarchar](50) NULL,
[LogError] [nvarchar](50) NULL,
[TearDown] [nvarchar](50) NULL,
[Total] [nvarchar](50) NULL,
[SLA Breach] [nvarchar](50) NULL,
CONSTRAINT [PK_MedWayFiles] PRIMARY KEY CLUSTERED

what im trying to do is reference for example "authenticate=0" within the txt.file and then assign it across to the Authenticate column within SQL...

What and how would be the best solution to do this?

If you can help me it would be appreciated!

Thanks in Advance.

 

[kulrom]

Is this how to manipulate the strings getting the authenticate value?
This should give you idea how to substring the 0 from there:

Dim str As String = "24/06/2010 19:20:31 /IServerManager.GetPublicKey " & _
"(timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)"
MessageBox.Show(str.Substring(str.IndexOf("=") + 1, 1))

 

[Scott_Atkins]

Hi Thanks for the reply and example its appreciated,

Ok so say iv done
Dim str As String = "24/06/2010 19:20:31 /IServerManager.GetPublicKey " & _
"(timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)"
MessageBox.Show(str.Substring(str.IndexOf("=") + 1, 1))

how can i reference them to the collumns within SQL though im aware ill have to create a Connection String,
but i cant figure out how to say what value goes in what column, and as with my Authenticate column within SQL you can see the Authenticate value is 0 within the textfile but when i write this from VB to SQL i dont want the word Authenticate to appear in the column Authenticare i just want the value which is 0 to appear, this is the first time i have come across this....Also as you can see the authenticate, authorise,execute,logerror,teardown and total might change later down the line for example it could increase from 0 to 2,3,4,5??

Thanks in advance its appreciated

 

[kulrom]

It matters how you write the INSERT INTO Statement e.g.

Dim SqlString As String = "INSERT INTO MedWayFiles " & _
("[Date and Time],[Commands],[Timings],[Authenticate],[Authorise], etc.) " & _
"VALUES" & _
"(DateTime.Now, "CommandsValue", "TimingsValue", "AuthenticateValue", "AuthoriseValue", etc.)"

As you see it takes the values respectively

As per the string manipulation; hmmm check this out and see if it helps you to understand the things better:

        Dim InitialString As String = "24/06/2010 19:20:31 /IServerManager.GetPublicKey " & _
        "(timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)"
        ' hmm i'am afraid i will over-complicate this lol :)

        Dim TwoStrings() As String = InitialString.Split("("c)
        ' get only the portion that contains the values and remove the closing brackets
        Dim ValuesPortion As String = TwoStrings(1).Substring(9, TwoStrings(1).Length - 10)

        Dim Values() As String = ValuesPortion.Split(" "c)
        Dim AuthenticateValue = Values(0).Substring(Values(0).IndexOf("="c) + 1, Values(0).Length - (Values(0).IndexOf("="c) + 1))
        Dim AuthoriseValue = Values(1).Substring(Values(1).IndexOf("="c) + 1, Values(1).Length - (Values(1).IndexOf("="c) + 1))
        Dim ExecuteValue = Values(2).Substring(Values(2).IndexOf("="c) + 1, Values(2).Length - (Values(2).IndexOf("="c) + 1))
        Dim LogErrorValue = Values(3).Substring(Values(3).IndexOf("="c) + 1, Values(3).Length - (Values(3).IndexOf("="c) + 1))
        Dim TeardownValue = Values(4).Substring(Values(4).IndexOf("="c) + 1, Values(4).Length - (Values(4).IndexOf("="c) + 1))
        Dim TotalValue = Values(5).Substring(Values(5).IndexOf("="c) + 1, Values(5).Length - (Values(5).IndexOf("="c) + 1))

        ' test the values:
        Console.WriteLine("Authenticate: " & AuthenticateValue)
        Console.WriteLine("Authorise: " & AuthoriseValue)
        Console.WriteLine("Execute: " & ExecuteValue)
        Console.WriteLine("LogError: " & LogErrorValue)
        Console.WriteLine("Teardown: " & TeardownValue)
        Console.WriteLine("Total: " & TotalValue)

        ' use the values

        Dim connection As New SqlClient.SqlConnection("connectionstringhere")
        Try
            connection.Open()
            Dim command As SqlCommand = connection.CreateCommand
            command.CommandText = "INSERT INTO MedWayFiles " & _
            "([Date and Time],[Commands],[Timings],[Authenticate],[Authorise], [Execute], [LogError], [TearDown], [Total]) " & _
            "VALUES " & _
            "(@DateAndTime, @CommandsValue, @TimingsValue, @AuthenticateValue, @AuthoriseValue, @ExecuteValue, @LogErrorValue, @TearDownValue, @TotalValue)"
            command.Parameters.AddWithValue("@DateAndTime", DateTime.Now).SqlDbType = SqlDbType.DateTime
            command.Parameters.AddWithValue("@CommandsValue", "CommandsValue").SqlDbType = SqlDbType.NVarChar
            command.Parameters.AddWithValue("@TimingsValue", "TimingsValue").SqlDbType = SqlDbType.NVarChar
            command.Parameters.AddWithValue("@AuthenticateValue", AuthenticateValue).SqlDbType = SqlDbType.NVarChar
            command.Parameters.AddWithValue("@AuthoriseValue", AuthoriseValue).SqlDbType = SqlDbType.NVarChar
            command.Parameters.AddWithValue("@ExecuteValue", ExecuteValue).SqlDbType = SqlDbType.NVarChar
            command.Parameters.AddWithValue("@LogErrorValue", LogErrorValue).SqlDbType = SqlDbType.NVarChar
            command.Parameters.AddWithValue("@TearDownValue", TeardownValue).SqlDbType = SqlDbType.NVarChar
            command.Parameters.AddWithValue("@TotalValue", TotalValue).SqlDbType = SqlDbType.NVarChar
            command.ExecuteNonQuery()
        Catch ex As Exception
            Console.Write(ex.Message)
        Finally
            connection.Close()
        End Try

 

[Scott_Atkins]

Excellent absolute life saver!! i was going insane trying to figure this out A***

Thanks

 

[kulrom]

Nice. Btw, please mark the thread resolved if you found it helpful. Thanks