Need help changing programming

[Suzi]

Hi

I have inherited a database and the only way I can make this module work is
if it doesn't import the data properly go in and change the code lines. How
do I change it so I don't have to keep going into the module? The dateline
with the comment normal one usually works but sometimes I have to change it
for the files without a ctoload file in the archive, and once I even needed
a 3rd option. I find out where to change it by counting characters from the
locals window.

Thanks

Suzi

Public strFile As String
Public LineCounter As Integer
Public Dataline As String
Public RSTPar As Recordset
Public Linecount As Integer
Public Dateline As String
Public archivedate As Date



Public Function UPDPA()

Dim RSTPar As Recordset 'recordset name
Dim intFile As Integer

Set dbs = CurrentDb
Set RSTPar = CurrentDb.OpenRecordset("TblPrintArchive", dbOpenDynaset)
'select table and db
strFile = "C:\Archive\PDIR.txt" 'specifies file name

Open strFile For Input As #1

Do While Not EOF(1)
Line Input #1, Dataline

'Dateline = Mid(Dataline, 61, 2) & "/" & Mid(Dataline, 58, 2) &
"/" & Mid(Dataline, 64, 4)
Dateline = Mid(Dataline, 55, 2) & "/" & Mid(Dataline, 52, 2) &
"/" & Mid(Dataline, 58, 4) 'normal one
'Dateline = Mid(Dataline, 41, 2) & "/" & Mid(Dataline, 44, 2) &
"/" & Mid(Dataline, 47, 4) 'Required for archives without ctoload

If IsDate(Dateline) Then

'UPDATE RECORD (Parse Record)
With RSTPar
.AddNew
!DirName = Mid(Dataline, 10, 4)
!FileCreationDate = Dateline
!Filename = Trim(Mid(Dataline, 15, 37)) 'normal one
'!Filename = Trim(Mid(Dataline, 15, 26)) 'Required
for archives without ctoload
!archivedate = Now()
.Update
End With
Else
End If

Loop
Close #1

Kill strFile
MsgBox ("Records for " & Mid(Dataline, 10, 4) & " are added to the
database")

End Function

 

[Stefan Hoffmann]

hi Suzi,

The dateline
with the comment normal one usually works but sometimes I have to change it
for the files without a ctoload file in the archive, and once I even needed
a 3rd option.

What is a "ctoload file"?


mfG
--> stefan <--

 

[Suzi]

A directory listing of the archive just created from our mainframe.

Suzi

 

[Stefan Hoffmann]

hi Suzi,

A directory listing of the archive just created from our mainframe.

From your code:

!Filename = Trim(Mid(Dataline, 15, 37)) 'normal one
'!Filename = Trim(Mid(Dataline, 15, 26)) 'Required
for archives without ctoload

How can these two files be distinguished?


mfG
--> stefan <--

 

[Suzi]

Hi Stefan

The one I call normal one has an entry for the actual ctoload archive
record. The others just have the rest of the archived files similar to the
2nd line shown.
I think the code works on the length of the filename (which would be
CTOLOAD/0000265/0000266/000PRINTFILE, the numbers in the filename will
change)

(ARCHIVE)PA53/CTOLOAD/0000265/0000266/000PRINTFILE 07/19/2005 @
17:16:51.6874
(ARCHIVE)PA53/SF100/0029884/000PRINT 09/28/2006 @
22:32:16.6826

Sorry its in capitals thats how the mainframe sends it.

Suzi

 

[Stefan Hoffmann]

hi Suzi,

(ARCHIVE)PA53/CTOLOAD/0000265/0000266/000PRINTFILE 07/19/2005 @
17:16:51.6874
(ARCHIVE)PA53/SF100/0029884/000PRINT 09/28/2006 @
22:32:16.6826

Sorry its in capitals thats how the mainframe sends it.

OK. The following may work:

Public Function UPDPA()

Dim RSTPar As Recordset
Dim intFile As Integer

Dim lngPos As Long
Dim strPath As String
Dim strFile As String
dim strDate As String

Set dbs = CurrentDb
Set RSTPar = CurrentDb.OpenRecordset("TblPrintArchive", dbOpenDynaset)
strFile = "C:\Archive\PDIR.txt"

Open strFile For Input As #1

Do While Not EOF(1)
Line Input #1, Dataline
lngPos = InStr(Dataline, " ")
strPath = Mid(Dataline, 10, 4)
strFile = Trim(Mid(15, lngPos - 1))
strDate = Left(Trim(Right(Dataline, Len(Dataline) - lngPos - 1)), 10)

If IsDate(strDAte) Then
With RSTPar
.AddNew
!DirName = strPath
!FileCreationDate = CDate(strDate)
!Filename = strFile
!archivedate = Now()
.Update
End With
End If
Loop

Close #1

Kill strFile

MsgBox "Records for " & Mid(Dataline, 10, 4) & _
" are added to the database"

End Function



mfG
--> stefan <--

 

[Suzi]

Hi Stefan

It tells me the strfile value is ""

Wendy

 

[Stefan Hoffmann]

hi Suzi,

It tells me the strfile value is ""

Wendy

Now i'm a little bit confused...
What value has lngPos?
Must be

strFile = Trim(Mid(15, lngPos - 16))


mfG
--> stefan <--

 

[Suzi]

Hi Stefan

I've taken over from Suz, without any changes ingpos is 34. Could it be
because the 2nd line of text in the file should be ignored? It just has the
words Name at the left and Creation time above the filedate.


Wendy

 

[Stefan Hoffmann]

hi Wendy,

I've taken over from Suz, without any changes ingpos is 34. Could it be
because the 2nd line of text in the file should be ignored? It just has the
words Name at the left and Creation time above the filedate.

Please post the first three or four lines of your file.


mfG
--> stefan <--

 

[Suzi]

Ok, unfortunately filenames vary in length. But the line with ctoload in it
(if there is one) is always the longest filename.

Wendy

PDIR Version 50.150.0009 Monday, October 23, 2006 15:08:02
Name CreationTime
(ARCHIVE)PA56/BACS/PROCESSED/4172 10/18/2006 @
20:29:30.2557
(ARCHIVE)PA56/MA170/0048355/000PRINT 10/18/2006 @
20:31:56.1435
(ARCHIVE)PA56/SF100/0037316/000PRINT 10/16/2006 @
22:03:07.3055

 

[Stefan Hoffmann]

hi Wendy,

PDIR Version 50.150.0009 Monday, October 23, 2006 15:08:02
Name CreationTime
(ARCHIVE)PA56/BACS/PROCESSED/4172 10/18/2006 @
20:29:30.2557
(ARCHIVE)PA56/MA170/0048355/000PRINT 10/18/2006 @
20:31:56.1435

Is the line break between date and time part from your mailer?


mfG
--> stefan <--

 

[Suzi]

Yes its all one string on the text file

Wendy

hi Wendy,

Is the line break between date and time part from your mailer?


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Wendy,

Yes its all one string on the text file

Okay, the following is tested and works:

Public Sub ImportData(AFile As String)

Dim rs As DAO.Recordset

Dim dn As String ' Directory name
Dim dt As Date ' Creation date
Dim ds As String ' Date part of ln
Dim fh As Integer ' File handle
Dim fn As String ' File name
Dim lc As Long ' Line counter
Dim ln As String ' Actual line
Dim sp As Long ' Position of first space

Set rs = CurrentDb.OpenRecordset("TblPrintArchive", dbOpenDynaset)

fh = FreeFile
lc = 0
Open AFile For Input As #fh
Do While Not EOF(fh)
Line Input #fh, ln
lc = lc + 1
If lc > 2 Then ' Skip the first two lines
sp = InStr(ln, " ")
dn = Left(ln, sp - 1)
fn = Right(dn, Len(dn) - 13)
dn = Mid(dn, 10, 4)
ds = Trim(Mid(ln, sp, Len(ln) - sp))
dt = CDate(Left(ds, 10))
rs.AddNew
rs!DirName = dn
rs!FileCreationDate = dt
rs!FileName = fn
rs!archivedate = Now()
rs.Update
End If
Loop
Close #fh

Kill AFile

MsgBox "Records for " & dn & " are added to the Database"

End Sub


Obviously, you should add some error handling.

mfG
--> stefan <--

 

[Suzi]

Thanks Stefan.

Wendy & Suzi

hi Wendy,

Okay, the following is tested and works:

Public Sub ImportData(AFile As String)

Dim rs As DAO.Recordset

Dim dn As String ' Directory name
Dim dt As Date ' Creation date
Dim ds As String ' Date part of ln
Dim fh As Integer ' File handle
Dim fn As String ' File name
Dim lc As Long ' Line counter
Dim ln As String ' Actual line
Dim sp As Long ' Position of first space

Set rs = CurrentDb.OpenRecordset("TblPrintArchive", dbOpenDynaset)

fh = FreeFile
lc = 0
Open AFile For Input As #fh
Do While Not EOF(fh)
Line Input #fh, ln
lc = lc + 1
If lc > 2 Then ' Skip the first two lines
sp = InStr(ln, " ")
dn = Left(ln, sp - 1)
fn = Right(dn, Len(dn) - 13)
dn = Mid(dn, 10, 4)
ds = Trim(Mid(ln, sp, Len(ln) - sp))
dt = CDate(Left(ds, 10))
rs.AddNew
rs!DirName = dn
rs!FileCreationDate = dt
rs!FileName = fn
rs!archivedate = Now()
rs.Update
End If
Loop
Close #fh

Kill AFile

MsgBox "Records for " & dn & " are added to the Database"

End Sub


Obviously, you should add some error handling.

mfG
--> stefan <--