Get text file content into SQL table

[mabond]

Hi

My text file varies in size on each occasion it is processed (i.e different
number of lines)
Contents are coma separated, giving data for each column in my sql table
(number of columns is constant)

My problem is not knowing how many lines are in my file.

I want to use this

"fileopen(1, filename, openmode.binary)"
myrecord = lineinput(1)
dim x = split(myrecord, ",")

Then, while looping through each line of the file, use the x array values to
populate sql parameter for use in a parameterized insert command. Open my sql
connection, execute my insert command, close the connection and move onto the
next line. Works perfect on another file I have ..... but that file has a
header line when received which tells me how many records are in the file

so, how will I know the end point of the loop if I don't know the number of
lines in the file

or is there another way of acheiving the same thing with the text file
format I have

Regards

Michael Bond

[Tom Shelton]

mabond wrote:

Hi

My text file varies in size on each occasion it is processed (i.e different
number of lines)
Contents are coma separated, giving data for each column in my sql table
(number of columns is constant)

My problem is not knowing how many lines are in my file.

I want to use this

"fileopen(1, filename, openmode.binary)"
myrecord = lineinput(1)
dim x = split(myrecord, ",")

Then, while looping through each line of the file, use the x array values to
populate sql parameter for use in a parameterized insert command. Open my sql
connection, execute my insert command, close the connection and move onto the
next line. Works perfect on another file I have ..... but that file has a
header line when received which tells me how many records are in the file

so, how will I know the end point of the loop if I don't know the number of
lines in the file

or is there another way of acheiving the same thing with the text file
format I have

Regards

Michael Bond

' 2003 syntax (2005 has a using statement that would make this a bit
cleaner)
Imports System.IO

Dim sr As StreamReader

Try
sr = new StreamReader (pathToFile)
Dim line As String = sr.ReadLine ()
While Not Line Is Nothing
' process record
line = sr.ReadLine ()
End While
Catch
' handle exception
Finally
sr.Close ()
End Try

Something like this should suffice... I seriously would avoid the
built in VB.NET functions for file io. I normally don't steer people
away from functionality that is built into VB.NET - but I make
exception for the file IO. It is slow - though you would notice it to
much with small files, but if there is any possiblity that this might
be a file of any substantial size, then get to know System.IO and
forget FileOpen and friends....

--
Tom Shelton

[mabond]

Tom

thanks for that, and the welcome advice. The file I refer to is not
particularly big, but the other file I referred to could have upwards of
30,000 lines. So I'll review my method in light of your advice.

Many thanks

Michael Bond

"Tom Shelton" wrote:

>
mabond wrote:

Hi

My text file varies in size on each occasion it is processed (i.e different
number of lines)
Contents are coma separated, giving data for each column in my sql table
(number of columns is constant)

My problem is not knowing how many lines are in my file.

I want to use this

"fileopen(1, filename, openmode.binary)"
myrecord = lineinput(1)
dim x = split(myrecord, ",")

Then, while looping through each line of the file, use the x array values to
populate sql parameter for use in a parameterized insert command. Open my sql
connection, execute my insert command, close the connection and move onto the
next line. Works perfect on another file I have ..... but that file has a
header line when received which tells me how many records are in the file

so, how will I know the end point of the loop if I don't know the number of
lines in the file

or is there another way of acheiving the same thing with the text file
format I have

Regards

Michael Bond

' 2003 syntax (2005 has a using statement that would make this a bit
cleaner)
Imports System.IO

Dim sr As StreamReader

Try
sr = new StreamReader (pathToFile)
Dim line As String = sr.ReadLine ()
While Not Line Is Nothing
' process record
line = sr.ReadLine ()
End While
Catch
' handle exception
Finally
sr.Close ()
End Try

Something like this should suffice... I seriously would avoid the
built in VB.NET functions for file io. I normally don't steer people
away from functionality that is built into VB.NET - but I make
exception for the file IO. It is slow - though you would notice it to
much with small files, but if there is any possiblity that this might
be a file of any substantial size, then get to know System.IO and
forget FileOpen and friends....

--
Tom Shelton

[GhostInAK]

Hello mabond,

I agree wholly with Tom. Try to make your code as BCL-reliant as possible
and avoid language-specific code as much as possible.

Just to be compleate though, the answer to the question asked is:

Dim tFileID as integer=FreeFile
OpenFile (tFileID, filename, openmode.binary)
Do until EOF(tFileID)
lineinput..
Loop
FileClose(tFileID)
As I mentioned to another poster on Monday, comma-delimited files are best
read with ADO.

-Boo

Tom

thanks for that, and the welcome advice. The file I refer to is not
particularly big, but the other file I referred to could have upwards
of 30,000 lines. So I'll review my method in light of your advice.

Many thanks

Michael Bond

"Tom Shelton" wrote:

>mabond wrote:

>>Hi

My text file varies in size on each occasion it is processed (i.e
different
number of lines)
Contents are coma separated, giving data for each column in my sql
table
(number of columns is constant)
My problem is not knowing how many lines are in my file.

I want to use this

"fileopen(1, filename, openmode.binary)"
myrecord = lineinput(1)
dim x = split(myrecord, ",")
Then, while looping through each line of the file, use the x array
values to populate sql parameter for use in a parameterized insert
command. Open my sql connection, execute my insert command, close
the connection and move onto the next line. Works perfect on another
file I have ..... but that file has a header line when received
which tells me how many records are in the file

so, how will I know the end point of the loop if I don't know the
number of lines in the file

or is there another way of acheiving the same thing with the text
file format I have

Regards

Michael Bond

' 2003 syntax (2005 has a using statement that would make this a bit
cleaner)
Imports System.IO
Dim sr As StreamReader

Try
sr = new StreamReader (pathToFile)
Dim line As String = sr.ReadLine ()
While Not Line Is Nothing
' process record
line = sr.ReadLine ()
End While
Catch
' handle exception
Finally
sr.Close ()
End Try
Something like this should suffice... I seriously would avoid the
built in VB.NET functions for file io. I normally don't steer people
away from functionality that is built into VB.NET - but I make
exception for the file IO. It is slow - though you would notice it
to much with small files, but if there is any possiblity that this
might be a file of any substantial size, then get to know System.IO
and forget FileOpen and friends....

--
Tom Shelton

[mabond]

Boo

thanks, that gives me what I asked for, but I'm grateful to you both for
adding that little bit extra. Gives me a new direction to explore, especially
if it will save processing time in the long run

Appreciate all the help

Regards

Michael Bond

"GhostInAK" wrote:

Hello mabond,

I agree wholly with Tom. Try to make your code as BCL-reliant as possible
and avoid language-specific code as much as possible.

Just to be compleate though, the answer to the question asked is:

Dim tFileID as integer=FreeFile
OpenFile (tFileID, filename, openmode.binary)
Do until EOF(tFileID)
lineinput..
Loop
FileClose(tFileID)
As I mentioned to another poster on Monday, comma-delimited files are best
read with ADO.

-Boo

Tom

thanks for that, and the welcome advice. The file I refer to is not
particularly big, but the other file I referred to could have upwards
of 30,000 lines. So I'll review my method in light of your advice.

Many thanks

Michael Bond

"Tom Shelton" wrote:

mabond wrote:

Hi

My text file varies in size on each occasion it is processed (i.e
different
number of lines)
Contents are coma separated, giving data for each column in my sql
table
(number of columns is constant)
My problem is not knowing how many lines are in my file.

I want to use this

"fileopen(1, filename, openmode.binary)"
myrecord = lineinput(1)
dim x = split(myrecord, ",")
Then, while looping through each line of the file, use the x array
values to populate sql parameter for use in a parameterized insert
command. Open my sql connection, execute my insert command, close
the connection and move onto the next line. Works perfect on another
file I have ..... but that file has a header line when received
which tells me how many records are in the file

so, how will I know the end point of the loop if I don't know the
number of lines in the file

or is there another way of acheiving the same thing with the text
file format I have

Regards

Michael Bond

' 2003 syntax (2005 has a using statement that would make this a bit
cleaner)
Imports System.IO
Dim sr As StreamReader

Try
sr = new StreamReader (pathToFile)
Dim line As String = sr.ReadLine ()
While Not Line Is Nothing
' process record
line = sr.ReadLine ()
End While
Catch
' handle exception
Finally
sr.Close ()
End Try
Something like this should suffice... I seriously would avoid the
built in VB.NET functions for file io. I normally don't steer people
away from functionality that is built into VB.NET - but I make
exception for the file IO. It is slow - though you would notice it
to much with small files, but if there is any possiblity that this
might be a file of any substantial size, then get to know System.IO
and forget FileOpen and friends....

--
Tom Shelton

[mabond]

Tom

This is what I have put in place

Using sr As New StreamReader(pathtoFile)
Dim lineno As Integer = 0
Try
Dim line As String = sr.ReadLine()
While Not line Is Nothing
Dim x = Split(line, ",")
'my code to pass to sql table here
lineno += 1
line = sr.ReadLine()
End While
Catch e As Exception
MessageBox.Show(e.ToString)
Finally
sr.Close()
End Try
End Using

Wasn't sure about the correct usage of the "Using" statement but I think i
got it right.

Thanks for the help

Regards

Michael Bond

"Tom Shelton" wrote:

>
mabond wrote:

Hi

My text file varies in size on each occasion it is processed (i.e different
number of lines)
Contents are coma separated, giving data for each column in my sql table
(number of columns is constant)

My problem is not knowing how many lines are in my file.

I want to use this

"fileopen(1, filename, openmode.binary)"
myrecord = lineinput(1)
dim x = split(myrecord, ",")

Then, while looping through each line of the file, use the x array values to
populate sql parameter for use in a parameterized insert command. Open my sql
connection, execute my insert command, close the connection and move onto the
next line. Works perfect on another file I have ..... but that file has a
header line when received which tells me how many records are in the file

so, how will I know the end point of the loop if I don't know the number of
lines in the file

or is there another way of acheiving the same thing with the text file
format I have

Regards

Michael Bond

' 2003 syntax (2005 has a using statement that would make this a bit
cleaner)
Imports System.IO

Dim sr As StreamReader

Try
sr = new StreamReader (pathToFile)
Dim line As String = sr.ReadLine ()
While Not Line Is Nothing
' process record
line = sr.ReadLine ()
End While
Catch
' handle exception
Finally
sr.Close ()
End Try

Something like this should suffice... I seriously would avoid the
built in VB.NET functions for file io. I normally don't steer people
away from functionality that is built into VB.NET - but I make
exception for the file IO. It is slow - though you would notice it to
much with small files, but if there is any possiblity that this might
be a file of any substantial size, then get to know System.IO and
forget FileOpen and friends....

--
Tom Shelton

[Tom Shelton]

mabond wrote:

Tom

This is what I have put in place

Using sr As New StreamReader(pathtoFile)
Dim lineno As Integer = 0
Try
Dim line As String = sr.ReadLine()
While Not line Is Nothing
Dim x = Split(line, ",")
'my code to pass to sql table here
lineno += 1
line = sr.ReadLine()
End While
Catch e As Exception
MessageBox.Show(e.ToString)
Finally
sr.Close()
End Try
End Using

Wasn't sure about the correct usage of the "Using" statement but I think i
got it right.

Well, with the useing statement you don't have to call sr.Close at all.
It will be called for you when the using block exits. So, you it
would probably look like:

Try
Using sr As New StreamReader (pathToFile)
' do your stuff
End Using ' sr is closed here
Catch e As Exception
MessageBox.Show (e.ToString)
End Try

Anyway, it is really syntactic sugur for try/finally/end try, but it
makes for some cleaner code...

--
Tom Shelton

[Tom Shelton]

mabond wrote:

Tom

This is what I have put in place

Using sr As New StreamReader(pathtoFile)
Dim lineno As Integer = 0
Try
Dim line As String = sr.ReadLine()
While Not line Is Nothing
Dim x = Split(line, ",")
'my code to pass to sql table here
lineno += 1
line = sr.ReadLine()
End While
Catch e As Exception
MessageBox.Show(e.ToString)
Finally
sr.Close()
End Try
End Using

Wasn't sure about the correct usage of the "Using" statement but I think i
got it right.

Well, with the useing statement you don't have to call sr.Close at all.
It will be called for you when the using block exits. So, you it
would probably look like:

Try
Using sr As New StreamReader (pathToFile)
' do your stuff
End Using ' sr is closed here
Catch e As Exception
MessageBox.Show (e.ToString)
End Try

Anyway, it is really syntactic sugur for try/finally/end try, but it
makes for some cleaner code...

--
Tom Shelton

[Paul Clement]

On Wed, 23 Aug 2006 15:02:01 -0700, mabond <ma****@discussions.microsoft.comwrote:

¤ Hi
¤
¤ My text file varies in size on each occasion it is processed (i.e different
¤ number of lines)
¤ Contents are coma separated, giving data for each column in my sql table
¤ (number of columns is constant)
¤
¤ My problem is not knowing how many lines are in my file.
¤
¤ I want to use this
¤
¤ "fileopen(1, filename, openmode.binary)"
¤ myrecord = lineinput(1)
¤ dim x = split(myrecord, ",")
¤
¤ Then, while looping through each line of the file, use the x array values to
¤ populate sql parameter for use in a parameterized insert command. Open my sql
¤ connection, execute my insert command, close the connection and move onto the
¤ next line. Works perfect on another file I have ..... but that file has a
¤ header line when received which tells me how many records are in the file
¤
¤ so, how will I know the end point of the loop if I don't know the number of
¤ lines in the file
¤
¤ or is there another way of acheiving the same thing with the text file
¤ format I have

Using a single SQL INSERT...SELECT statement to perform the import or BULK INSERT would probably be
much more efficient.

http://msdn2.microsoft.com/en-us/library/ms188365.aspx
Paul
~~~~
Microsoft MVP (Visual Basic)

[mabond]

Thanks Paul

My skills in SQL are pretty limited and I've only just managed to overcome
the difficulties associated with getting the correct date format into an
"insert" statement by using parameters. That's why I was doing it line by
line. The solutions to this particular question provided by Tom and Boo meet
my needs at present but I appreciate your observation that Bulk Insert is
something I should look at and I'll follow that up as part of my steep
learning curve. Thanks for the feedback and suggestion. The help from all
contributors, both to this question, and others I've posted is very much
appreciated.

Regards

Michael Bond

"Paul Clement" wrote:

On Wed, 23 Aug 2006 15:02:01 -0700, mabond <ma****@discussions.microsoft.comwrote:

¤ Hi
¤
¤ My text file varies in size on each occasion it is processed (i.e different
¤ number of lines)
¤ Contents are coma separated, giving data for each column in my sql table
¤ (number of columns is constant)
¤
¤ My problem is not knowing how many lines are in my file.
¤
¤ I want to use this
¤
¤ "fileopen(1, filename, openmode.binary)"
¤ myrecord = lineinput(1)
¤ dim x = split(myrecord, ",")
¤
¤ Then, while looping through each line of the file, use the x array values to
¤ populate sql parameter for use in a parameterized insert command. Open my sql
¤ connection, execute my insert command, close the connection and move onto the
¤ next line. Works perfect on another file I have ..... but that file has a
¤ header line when received which tells me how many records are in the file
¤
¤ so, how will I know the end point of the loop if I don't know the number of
¤ lines in the file
¤
¤ or is there another way of acheiving the same thing with the text file
¤ format I have

Using a single SQL INSERT...SELECT statement to perform the import or BULK INSERT would probably be
much more efficient.

http://msdn2.microsoft.com/en-us/library/ms188365.aspx
Paul
~~~~
Microsoft MVP (Visual Basic)