Re: Get text file content into SQL table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


.

Relevant Pages

  • Re: Get text file content into SQL table
    ... giving data for each column in my sql table ... Dim sr As StreamReader ... built in VB.NET functions for file io. ... exception for the file IO. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Which identity?
    ... > Some postings I read suggested using MAXto retrieve the inserted record ... > each of these 3 SQL features? ... Another way to do it if using the .AddNew method on a Jet based ADO ... Dim rsTest As ADODB.Recordset ...
    (microsoft.public.access.queries)
  • Re: display data to user question
    ... I created a project that has an embedded SQl table with the scenario you ... Dim cn As New SqlConnection("Data ... Protected Sub Page_Load(ByVal sender As Object, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: display data to user question
    ... I created a project that has an embedded SQl table with the scenario you ... Dim cn As New SqlConnection("Data ... Protected Sub Page_Load(ByVal sender As Object, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: display data to user question
    ... I created a project that has an embedded SQl table with the scenario you ... Dim cn As New SqlConnection("Data ... Protected Sub Page_Load(ByVal sender As Object, ...
    (microsoft.public.dotnet.framework.aspnet)