> 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" 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
>
> 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" 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
>
>
>

"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
>
> 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.

> 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.

"Paul Clement" wrote:

> On Wed, 23 Aug 2006 15:02:01 -0700, mabond <mab***@discussions.microsoft.com> 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
>
> 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)
>