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.

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?

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

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?

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

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

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