manipulate text files

[r0cks]

Hi,
I have a text file that needs to be exported to a sql server table. the text file is something similar to this,
-------------
01.08.2007

06:00 go to work
12:00 lunch
17:00 go home
19:00 dinner
21:00 go to bed
09:00 go to leisure centre
--------------

I need to pull these details to a db table which has columns Date, Time, Description. The first record imported will be Date. A new record begins when a new time delimiter appears in the text file. A new date begins if a record falls after 06:00.
Does anyone have idea how to do this using vb.net?

Are -------------- used as delimiters? Your description is unclear.

[manoshailu]

hi,
Use the below codings.
in my text file
01.08.2007
06:00 go to work
12:00 lunch
17:00 go home
19:00 dinner
21:00 go to bed
09:00 go to leisure centre

Codings
imports system.io
imports system.data.sqlclient

Dim con As SqlConnection = New SqlConnection("server=shailu;uid=sa;pwd=;database=pubs")
Dim cmd As SqlCommand = New SqlCommand
Dim fs As FileStream
Dim sr As StreamReader
Dim p1 As String = "D:\Documents and Settings\j2ee\Desktop"
fs = New FileStream(p1 + "/test.txt", FileMode.Open)
sr = New StreamReader(fs)
Dim ddate, time, descp As String
ddate = sr.ReadLine()
While Not sr.Read
time = sr.ReadLine()
Dim s(), d As String
d = " "
Dim c() As Char
c = d.ToCharArray()
Dim i As Integer
For i = 0 To time.Length - 1
s = time.Split(c, i)
If s.Length = 2 Then
Exit For
End If
Next
time = s(0)
descp = s(1)
con.Open()
cmd.CommandText = "insert into test values('" & ddate & "','" & time & "','" & descp & "')"
cmd.Connection = con
cmd.ExecuteNonQuery()
con.Close()
End While
fs.Close()

best regards
shailu:)

Hi,
I have a text file that needs to be exported to a sql server table. the text file is something similar to this,
-------------
01.08.2007

06:00 go to work
12:00 lunch
17:00 go home
19:00 dinner
21:00 go to bed
09:00 go to leisure centre
--------------

I need to pull these details to a db table which has columns Date, Time, Description. The first record imported will be Date. A new record begins when a new time delimiter appears in the text file. A new date begins if a record falls after 06:00.
Does anyone have idea how to do this using vb.net?

[r0cks]

NO ----------- these are not delimeters. I have used those lines to show the text files seperately

[r0cks]

Are -------------- used as delimiters? Your description is unclear.

NO ----------- these are not delimeters. I have used those lines to show the text files seperately

r0cks

Are you saying you have more than one text file to read in then?

[r0cks]

Thanks Shailu,
It was bit helpful for me, How can I manipulate if the Description field has two or three lines? I mean if the text file looks like this?

in my text file
01.08.2007
06:00 go to work, bla bla bla.....
.... bla bla.
12:00 lunch bla bla....
bla bla....
... bla bla.
17:00 go home
19:00 dinner
21:00 go to bed
09:00 go to leisure centre

How Do I perform this? please help...

r0cks

[r0cks]

Iamthweek,

No, Just one text file,
my text file is,

01.08.2007
06:00 go to work, bla bla bla.....
.... bla bla.
12:00 lunch bla bla....
bla bla....
... bla bla.
17:00 go home
19:00 dinner
21:00 go to bed
09:00 go to leisure centre

[manoshailu]

hi,

Try the below codings:

Dim con As SqlConnection = New SqlConnection("server=shailu;uid=sa;pwd=;database=pubs")
Dim cmd As SqlCommand = New SqlCommand
Dim fs As FileStream
Dim sr As StreamReader
Dim p1 As String = "D:\Documents and Settings\j2ee\Desktop"
fs = New FileStream(p1 + "/test.txt", FileMode.Open)
sr = New StreamReader(fs)
Dim ddate, time, descp As String
ddate = sr.ReadLine()
Dim bl As Boolean
While Not sr.Read
descp = sr.ReadLine()
Dim chk As Char
For Each chk In descp
If next line is starts with character
If (Asc(chk) >= 65 And Asc(chk) <= 90) Or (Asc(chk) >= 97 And Asc(chk) <= 122) Then
bl = True
Exit For
If next line is starts with number
ElseIf Asc(chk) >= 48 And Asc(chk) <= 57 Then
If descp.Substring(2, 1) = ":" Then
GoTo 1
End If
Else
1:
bl = False
Dim s1 As String
Dim s(), d As String
d = " "
Dim c() As Char
c = d.ToCharArray()
Dim i As Integer
For i = 0 To descp.Length - 1
s = descp.Split(c, i)
If s.Length = 2 Then
Exit For
End If
Next
time = s(0)
descp = s(1)
Exit For
End If
Next
If bl = False Then
con.Open()
cmd.CommandText = "insert into test values('" & ddate & "','" & time & "','" & descp & "')"
cmd.Connection = con
cmd.ExecuteNonQuery()
con.Close()
Else
con.Open()
cmd.CommandText = "update test set descp=descp + ' ' + '" & descp & "' where tdate='" & ddate & "' and [time]='" & time & "'"
cmd.Connection = con
cmd.ExecuteNonQuery()
con.Close()
End If
End While
fs.Close()

Best Regards
Shailu:)

Iamthweek,

No, Just one text file,
my text file is,

01.08.2007
06:00 go to work, bla bla bla.....
.... bla bla.
12:00 lunch bla bla....
bla bla....
... bla bla.
17:00 go home
19:00 dinner
21:00 go to bed
09:00 go to leisure centre