Adding Lines to Access Database

[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

 

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

 

[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]

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.

 

[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

 

[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)