Adding Lines to Access Database

27 Oct 2006 12:56 PM

Justin Fancy

Hi everyone,

I need some help. I'm placing text files into a created database using
vb.Net. The problem is that, i need two seperate sql statements to add
both files because they are in different loops. My output comes out to
be as Follows:

TABLE
----------------------------------------------------
Field1                      Field2

outputfile1
outputfile1
outputfile1
outputfile1
outputfile1
                              outputfile2
                              outputfile2
                              outputfile2
                              outputfile2
-----------------------------------------------------

I declared both fields Nullable so I wouldn't get an error, complaining
that the fields cannot be Null.

I need the "outputfile2" lines to be adjacent to the "outputfile1"
files. I understand why this is happening, I just can't think of
another way of adding the fields.

My code is as follows:

Sub ReadFiles()

        File1 = "intDirectory.txt"
        'Open the first file

        sr = IO.File.OpenText(File1)
        'Initialize counter

        'Loop through Directory Listing file to load filepaths into an
array

        Do While sr.Peek <> -1

            lineFile1 = sr.ReadLine

            Dim sb As New System.Text.StringBuilder(lineFile1)
            sb.Replace("C:", "")
            sb.Replace("x:", "")
            sb.Replace("\", "/")
            sb.Replace("wwwroot", "")
            lineFile1 = sb.ToString()

            'INSERT INTO TABLE
            sSQL = "INSERT INTO Comparison(Internet_Directory)
VALUES('" & lineFile1.ToString & "')"
            CreateStoredProc(sSQL)

            counter += 1
        Loop
        sr.Close()

        File2 = "IISLog.txt"
        sr = IO.File.OpenText(File2)
        sSQL = ""

        Do While sr.Peek <> -1

                        lineFile2 = sr.ReadLine

            'String Manipulation (Replace some unneeded text)

            Dim sb2 As New System.Text.StringBuilder(lineFile2)
            sb2.Replace("C:", "")
            sb2.Replace("x:", "")
            sb2.Replace("\", "/")
            sb2.Replace("wwwroot", "")
            lineFile2 = sb2.ToString()

            'INSERT INTO TABLE
            sSQL = "INSERT INTO Comparison(IIS_Logs) VALUES('" &
lineFile2.ToString & "')"
            CreateStoredProc(sSQL)

            counter2 += 1

        Loop

        sr.Close()

    End Sub

27 Oct 2006 2:02 PM

Tim Patrick

Why don't you read both input files at the same time? If you are confident
that they contain the same number of elements, then it should work.

   Sub ReadFiles()
      Dim File1 As String = "intDirectory.txt"
      Dim File2 As String = "IISLog.txt"
      Dim sSQL As String
      Dim lineFile1 As String
      Dim lineFile2 As String

      'Open the input files
      Dim sr1 As StreamReader = IO.File.OpenText(File1)
      Dim sr2 As StreamReader = IO.File.OpenText(File2)

      'Loop through Directory Listing file to load filepaths
      Do While sr.Peek <> -1
         lineFile1 = PrepareContent(sr1.ReadLine)
         lineFile2 = PrepareContent(sr2.ReadLine)

         sSQL = "INSERT INTO Comparison(Internet_Directory, IIS_Logs) " & _
            "VALUES ('" & lineFile1 & "', '" & lineFile2 & "')"
         CreateStoredProc(sSQL)
      Loop
      sr1.Close()
      sr2.Close()
   End Sub

   Sub PrepareContent(ByVal origString As String) As String
      Dim workArea As String

      workArea = origString.Replace("C:", "")
      workArea = workArea.Replace("x:", "")
      workArea = workArea.Replace("\", "/")
      workArea = workArea.Replace("wwwroot", "")
      Return workArea
   End Sub

Of course, you should add in error handling just in case the files don't
match. Also, I beg of you to use Option Strict and Option Explicit. It will
mean more typing for you, but it will also help resolve hidden errors.

-----
Tim Patrick
Start-to-Finish Visual Basic 2005

Show quoteHide quote

> Hi everyone,
>
> I need some help. I'm placing text files into a created database using
> vb.Net. The problem is that, i need two seperate sql statements to add
> both files because they are in different loops. My output comes out to
> be as Follows:
>
> TABLE ---------------------------------------------------- Field1
> Field2
>
> outputfile1
> outputfile1
> outputfile1
> outputfile1
> outputfile1
> outputfile2
> outputfile2
> outputfile2
> outputfile2
> -----------------------------------------------------
> I declared both fields Nullable so I wouldn't get an error,
> complaining that the fields cannot be Null.
>
> I need the "outputfile2" lines to be adjacent to the "outputfile1"
> files. I understand why this is happening, I just can't think of
> another way of adding the fields.
>
> My code is as follows:
>
> Sub ReadFiles()
>
> File1 = "intDirectory.txt"
> 'Open the first file
> sr = IO.File.OpenText(File1)
> 'Initialize counter
> 'Loop through Directory Listing file to load filepaths into an
> array
>
> Do While sr.Peek <> -1
>
> lineFile1 = sr.ReadLine
>
> Dim sb As New System.Text.StringBuilder(lineFile1)
> sb.Replace("C:", "")
> sb.Replace("x:", "")
> sb.Replace("\", "/")
> sb.Replace("wwwroot", "")
> lineFile1 = sb.ToString()
> 'INSERT INTO TABLE
> sSQL = "INSERT INTO Comparison(Internet_Directory)
> VALUES('" & lineFile1.ToString & "')"
> CreateStoredProc(sSQL)
> counter += 1
> Loop
> sr.Close()
> File2 = "IISLog.txt"
> sr = IO.File.OpenText(File2)
> sSQL = ""
> Do While sr.Peek <> -1
>
> lineFile2 = sr.ReadLine
>
> 'String Manipulation (Replace some unneeded text)
>
> Dim sb2 As New System.Text.StringBuilder(lineFile2)
> sb2.Replace("C:", "")
> sb2.Replace("x:", "")
> sb2.Replace("\", "/")
> sb2.Replace("wwwroot", "")
> lineFile2 = sb2.ToString()
> 'INSERT INTO TABLE
> sSQL = "INSERT INTO Comparison(IIS_Logs) VALUES('" &
> lineFile2.ToString & "')"
> CreateStoredProc(sSQL)
> counter2 += 1
>
> Loop
>
> sr.Close()
>
> End Sub
>

27 Oct 2006 2:13 PM

Justin Fancy

But they definately won't contain the same number of elements. One is a
file system directory and one is the iis log for that file system
directory. I'm trying to compare these two files and get a result.
Then, create a report.

Tim Patrick wrote:
Show quoteHide quote

> Why don't you read both input files at the same time? If you are confident
> that they contain the same number of elements, then it should work.
>
>    Sub ReadFiles()
>       Dim File1 As String = "intDirectory.txt"
>       Dim File2 As String = "IISLog.txt"
>       Dim sSQL As String
>       Dim lineFile1 As String
>       Dim lineFile2 As String
>
>       'Open the input files
>       Dim sr1 As StreamReader = IO.File.OpenText(File1)
>       Dim sr2 As StreamReader = IO.File.OpenText(File2)
>
>       'Loop through Directory Listing file to load filepaths
>       Do While sr.Peek <> -1
>          lineFile1 = PrepareContent(sr1.ReadLine)
>          lineFile2 = PrepareContent(sr2.ReadLine)
>
>          sSQL = "INSERT INTO Comparison(Internet_Directory, IIS_Logs) " & _
>             "VALUES ('" & lineFile1 & "', '" & lineFile2 & "')"
>          CreateStoredProc(sSQL)
>       Loop
>       sr1.Close()
>       sr2.Close()
>    End Sub
>
>    Sub PrepareContent(ByVal origString As String) As String
>       Dim workArea As String
>
>       workArea = origString.Replace("C:", "")
>       workArea = workArea.Replace("x:", "")
>       workArea = workArea.Replace("\", "/")
>       workArea = workArea.Replace("wwwroot", "")
>       Return workArea
>    End Sub
>
> Of course, you should add in error handling just in case the files don't
> match. Also, I beg of you to use Option Strict and Option Explicit. It will
> mean more typing for you, but it will also help resolve hidden errors.
>
> -----
> Tim Patrick
> Start-to-Finish Visual Basic 2005
>
> > Hi everyone,
> >
> > I need some help. I'm placing text files into a created database using
> > vb.Net. The problem is that, i need two seperate sql statements to add
> > both files because they are in different loops. My output comes out to
> > be as Follows:
> >
> > TABLE ---------------------------------------------------- Field1
> > Field2
> >
> > outputfile1
> > outputfile1
> > outputfile1
> > outputfile1
> > outputfile1
> > outputfile2
> > outputfile2
> > outputfile2
> > outputfile2
> > -----------------------------------------------------
> > I declared both fields Nullable so I wouldn't get an error,
> > complaining that the fields cannot be Null.
> >
> > I need the "outputfile2" lines to be adjacent to the "outputfile1"
> > files. I understand why this is happening, I just can't think of
> > another way of adding the fields.
> >
> > My code is as follows:
> >
> > Sub ReadFiles()
> >
> > File1 = "intDirectory.txt"
> > 'Open the first file
> > sr = IO.File.OpenText(File1)
> > 'Initialize counter
> > 'Loop through Directory Listing file to load filepaths into an
> > array
> >
> > Do While sr.Peek <> -1
> >
> > lineFile1 = sr.ReadLine
> >
> > Dim sb As New System.Text.StringBuilder(lineFile1)
> > sb.Replace("C:", "")
> > sb.Replace("x:", "")
> > sb.Replace("\", "/")
> > sb.Replace("wwwroot", "")
> > lineFile1 = sb.ToString()
> > 'INSERT INTO TABLE
> > sSQL = "INSERT INTO Comparison(Internet_Directory)
> > VALUES('" & lineFile1.ToString & "')"
> > CreateStoredProc(sSQL)
> > counter += 1
> > Loop
> > sr.Close()
> > File2 = "IISLog.txt"
> > sr = IO.File.OpenText(File2)
> > sSQL = ""
> > Do While sr.Peek <> -1
> >
> > lineFile2 = sr.ReadLine
> >
> > 'String Manipulation (Replace some unneeded text)
> >
> > Dim sb2 As New System.Text.StringBuilder(lineFile2)
> > sb2.Replace("C:", "")
> > sb2.Replace("x:", "")
> > sb2.Replace("\", "/")
> > sb2.Replace("wwwroot", "")
> > lineFile2 = sb2.ToString()
> > 'INSERT INTO TABLE
> > sSQL = "INSERT INTO Comparison(IIS_Logs) VALUES('" &
> > lineFile2.ToString & "')"
> > CreateStoredProc(sSQL)
> > counter2 += 1
> >
> > Loop
> >
> > sr.Close()
> >
> > End Sub
> >

27 Oct 2006 2:41 PM

Tim Patrick

In that case, I would need additional information about the data stored in
the two source files and how you want to report the final data. For instance,
from what you said it sounds like the first file contains a listing of all
directories in your web site, while the second file contains a list of the
files accessed within that same set of directories. Perhaps your goal is
to get a count, per directory, of file hits. But I may have interpreted your
report incorrectly.

-----
Tim Patrick
Start-to-Finish Visual Basic 2005

Show quoteHide quote

> But they definately won't contain the same number of elements. One is
> a file system directory and one is the iis log for that file system
> directory. I'm trying to compare these two files and get a result.
> Then, create a report.

27 Oct 2006 2:49 PM

Justin Fancy

Ok, here's the situation.

I need to crawl the website to make a list of all the files on the
site.

The website list is sampled as followed:

/en/menu.htm
/exampl/examples/you/justin.htm

Also, i have to crawl one IIS log.

I Created a temp log file wherein sample entries are as follows:

/en/menu.htm
/thisisexample/you.html
/example/tom/test.html

There could be 1000 files in the file directory, but 678 in the IIS
Log. Get what i'm saying?

I am taking both these files, looping through them, and displaying them
in different fields, in one table in access. From there I can run a
query to compare them. Which ever entry in the website directory
listing does not match any entries in the IIS Log, I want to report on
those.

Anyway, that's my whole assignment. I certainly don't expect you to go
beyond what I requested first.

I hope you have better understood.

Justin

27 Oct 2006 3:11 PM

Tim Patrick

In this case I would create two distinct tables in your database, one for
each file. Load the contents of the first file into the first table, and
the second file into the second table. Then run queries that involve both
tables.

To get a list of all items in table 1 that are not in table 2, use this query.

SELECT DISTINCT FilePath FROM Table1
MINUS
SELECT DISTINCT FilePath FROM Table2

At least that works in Oracle. In SQL Server, use the EXCEPT keyword instead
of MINUS. I can't recall if Access uses either of these keywords. If neither
is supported, the following query will work as well.

SELECT DISTINCT FilePath FROM Table1
WHERE FilePath NOT IN (
SELECT DISTINCT FilePath FROM Table2)

-----
Tim Patrick
Start-to-Finish Visual Basic 2005

Show quoteHide quote

> Ok, here's the situation.
>
> I need to crawl the website to make a list of all the files on the
> site.
>
> The website list is sampled as followed:
>
> /en/menu.htm
> /exampl/examples/you/justin.htm
> Also, i have to crawl one IIS log.
>
> I Created a temp log file wherein sample entries are as follows:
>
> /en/menu.htm
> /thisisexample/you.html
> /example/tom/test.html
> There could be 1000 files in the file directory, but 678 in the IIS
> Log. Get what i'm saying?
>
> I am taking both these files, looping through them, and displaying
> them in different fields, in one table in access. From there I can run
> a query to compare them. Which ever entry in the website directory
> listing does not match any entries in the IIS Log, I want to report on
> those.
>
> Anyway, that's my whole assignment. I certainly don't expect you to go
> beyond what I requested first.
>
> I hope you have better understood.
>
> Justin
>