inputing data from text file

[FacilitiesCAD]

I am looking for an easyer way to do a task.
I want to maintain a database of aproximately 2000 drawings.
currently I run a .bat routine to generate a text file that has all the .dwg files on a paticular drive.
the routine is simple.

make_dwg_lst.bat

dir *.dwg /s >dwg_lst.txt


This generates a file like this:

Volume in drive * is ****
Volume Serial Number is ****-****


Directory of Drive:\path1

02/27/04 11:29a 39,501 File1.dwg
02/09/04 10:57a 295,727 File2.DWG
02/09/04 10:57a 461,956 File3.dwg
3 File(s) 797,184 bytes

Directory of Drive:\path2

08/06/02 08:01a 94,238 File4.dwg

etc

Since the data is spread out I used VB code in excel to organise the data before bringing it into access. At the bottom of this post is the code I used to organize the data as I am trying to put it into access.

First thing I want to do is make the code work. It doesn't because I don't know how to make/goto a new record. It was easy in excel because I'm more familiar with that.

Second I would like to compare the newly added data to data I already have because I have other fields that I input in like Project_Name1 etc that don't come from this automation. I would like to get rid of new duplicates.
(this doesn't need to be in the vb code it can be in a seperate querry)

Third I would like to get the routine to run my .bat code for me as well. I figure I could delete the existing .txt file, then run .bat then wait and then run the rest of the routine but I'm unsure how to implement that.

As you can see my BIG concer is how to create a new record in my VB code. All VB code I've done before this was just in one record.

Tim



Private Sub do_all_Click()
Dim L() As String
Dim Xa As String
Dim i As Integer
Dim m As Integer
Dim n As Integer

i = 0
Dim FileList As String


FileList = "Drive:\Path\File.txt"


Open FileList For Input As #1

Do While Not EOF(1)
i = i + 1
ReDim Preserve L(i) As String

Line Input #1, L(i)

Loop

Close #1

'-----------------------------------------

'Enter contents of Array into Column A (ideally a new table)

' Loop the number of lines found in FileList
m = 1
' start on 4th line
For n = 4 To i
' another loop to put L(#) into each cell 'record
' m = row number 'now record number
If Left(L, 14) = " Directory of " Then
Xa = Mid(L, 15) & "\"
L = ""
End If
If Mid(L, 18, 4) = "File" Then
L = ""
End If
If Mid(L, 1, 4) = " " Then
L = ""
End If

'ThisWorkbook.Sheets("Sheet1").Cells(n, 1) = L
' Cells(m, 1) = Xa
'Me.Text1 = Xa
'Me.Path1 = Xa 'moved to after if statement
' Cells(m, 2) = Mid(L, 1, 9)
Me.Saved_Date1 = Mid(L, 1, 9)
' Cells(m, 3) = Mid(L, 9, 8)
Me.Saved_Time1 = Mid(L, 9, 8)
' Cells(m, 4) = Mid(L, 17, 22)
Me.File_Size1 = Mid(L, 17, 22)
' Cells(m, 5) = Mid(L, 40)
Me.File1 = Mid(L, 40)
If L <> "" Then
' m = m + 1
Me.Path1 = Xa
'''Need Help Here
'''DoCmd.GoToRecord
'''DoCmd.GoToRecord , , acNext
End If
Next n


End Sub

 

[EBGreen]

If I were attempting to do this, I would loose the bat altogether. I'd use the Scripting.FileSystemObject to get a list of all of the files matching whatever criteria you want then query the db for the existing files. remove existing files from the list then parse through each of the remaining file names inserting them into the db.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[FacilitiesCAD]

This sounds like an interesting solution. I've never heard of Scripting.FileSystemObject before. could you give an example of how it works. (what you input what it outputs) Does this solution get around my main problem of creating new records? (I assume it does the way you describe it) I'll be searching this site and the web for more information about Scripting.FileSystemObject .

I will watch out for the second head of science, it is a dangerouse thing indeed. I do enjoy the Tick. Only saw it on TV never read the comics.

Tim

 

[EBGreen]

The FileSystemObject properly belongs to the Windows Script Host. You can find several examples of its use in the VBScript forum here at TT. Its use in your case would involve the GetFolder method and the Files collection of the folder object that was returned. Here is an example that would get all of the files into a dictionary object. I'll explain why I used the dictionary object later.

Dim oFSO As Object
Dim oFolder As Object
Dim colFiles As Collection
Dim oFileDict As Object

Set oFileDict = CreateObject("Scripting.Dictionary")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("C:\Blah")
Set colFiles = oFolder.Files
For Each oFile In colFiles
  if UCase(Right(oFile.Name, 3)) = "DWG" Then
     oFileDict.Add oFile.Path, oFile.DateLastModified
  End If
Next

This code snippet is untested and will not do subfolder recursion, but it should get you started. The reason I used the DIctionary object is that it has a .Exists method that would let you quickly determine if a file that is already in the database is also in the dictionary. It also has a .Remove method that will remove the file from the dictionary when you determine that the file is already in the DB. It also has a .Keys method that will allow you to use a for each loop to process any files remaining in the dictionary after you have remooved any that are already in the DB.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[FacilitiesCAD]

Thanks TomThumbKP for your quick response. I usually don't write code but I do have to debug it on occasion or modify it for my use.
I fully understand what I'm doing with my .bat file and that is what I like about it. I am trying to understand what I would be doing if I started with the code you are suggesting.

in the first section you are "dim"ing variables or basically defining them for the context of the program.

I don't know what a collection is.

In the second section you are "set"ting variables equal to objects. I don't understand what they are set to but I probable don't need to know. I believe that I don't change ("Scripting.Dictionary") or ("Scripting.FileSystemObject")
but I do change ("C:\Blah") to ("Drive:\Path1").

Once these things are set I am testing each file in the directory to see if it is a .dwg file.

In "For Each oFile In colFiles" should it be oFile or File(I'm guessing File)?

For the .dwg files I'm adding path and date last modified plus whatever else I want.

It sounds like in the end I would have a dictionary with all the files but it doesn't help me with getting them into records. It seems I could just as easily make a text file with my data in a form that access could import easily and then just reimport it.

Still a little confused.

Tim

 

[EBGreen]

You have it mostly figured out.

A collection is a group of objects.

You are correct that you would need to change C:\blah to whatever path you need.

For Each oFile In colFiles is the correct syntax. It basically says 'iterate through the objects in colFiles, call label each of them in turn oFile and execute the block of code between this line and the line with Next on it'.

The next step would be to query the db for existing files. Go through the results of this query one at a time checking to see if the file is in the dictionary. If the file is in the dictionary, then remove it's entry from the dictionary. After you have checked all of the files in the db against the dictionary, then the dictionary should only hold entries for new files. Now go through each entry in the dictionary executing a query to insert it into the db.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[FacilitiesCAD]

Thanks TomThumbKP. I have found the .addnew option to add to a recordset. I'm going to use this and my bat routine for now.

Tim

 

[EBGreen]

Whatever works for you. I'm glad you found a solution.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]