In Access 2000 and 2002, I have created an import specification to import the
fixed-width recordset below into an existing table. I am having strange
problems with the import of the date and time fields.

177 102003 16:43:12 102003 18:43:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
165 102003 17:43:12 102003 18:44:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
177 102003 16:41:18 102003 18:45:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
118 102003 16:41:17 102003 18:46:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
104 102000 16:29:50 102000 18:47:12 00000000 N 0000 0000 0000 0000 61930 4HGA800
130 102000 16:29:56 102000 18:43:12 00000000 N 0000 0000 0000 0000 61930 4HGA800
104 102000 16:28:56 102000 16:38:56 00000000 N 0000 0000 0000 0000 61930 4HGA800

The table design is as follows(there are no key restraints):
FLT Integer
STDATE DATE/TIME SHORTDATE
STTIME DATE/TIME LONGTIME
NDDATE DATE/TIME SHORTDATE
NDTIME DATE/TIME LONGTIME
BattID Text(9)
C Yes/No
NFO1 Text(4)
NFO2 Text(4)
NFO3 Text(4)
NFO4 Text(4)
CHGID Long Integer
VehicleID Text(9)

The import specification's Dates, Times, and Numbers selections are:
Date Delimeter = no delimeter
Time Delimeter = :
Four Digit Years = unchecked
Leading Zeros in Dates = checked


With the above values selected in the Import Spec., the STTIME and NDTIME fields
fail the import and the STDATE and NDDATE fields import correctly. If I change
the import spec. values for dates, times, and numbers to:
Date Delimeter = /
Time Delimeter = :
Then the STDATE and NDDATE fields fail the import and the STTIME and NDTIME
fields import correctly.

This makes no sense to me why the date delimeter has to be set with "/" for the
Time Delimeter to work. I have tried recreating the import spec. and everything
else I can think of and nothing seems to work. I removed the date formats on
the table as a test but it did not help. I also imported to a new table using
the import spec but the date or time fields would fail the import.

Am I missing something or is this software not going to work?

The problem appears to be with the date format. I could only get it to
work properly by first munging your date data in the file from 102003
to 10/20/03 and then setting the date delimeter to / at which point it
imported properly. Is munging your date format a reasonable step? Any
macro enabled editor could do it, or a fairly simple procedure to read
from the file, split each line on spaces into an array, then taking
the date elements and adding the foreslashes.

I can help you with this if need be.

-John


mark <ms (AT) nospam (DOT) comcast.net> wrote

Thanks John. I did modify the date field by adding /'s to the raw data. But
while were talking, what macro enabled editor do you use? Also, what would the
array look like. I am interested in learning a better way any time I can.

Thank you.
mark

John wrote:

I use PFE but the author has stopped supporting it. Still it's a good
editor as is. There are others search google for "Programming text
editor download".

the array.. hrm, well you must mean the vba code. which of course
depends on what your flawed data looks like heres a routine im using
to read/munge/write/import a file that is not normalized. in this case
an apache servers log file. (yours would be much simpler since it's
very nearly normalized already.) you can ignore my form stuff and the
queries i run after the import. My process takes awhile and i want a
screen updating the progress. Some of the variables i use here are to
support the form updating. weed through

John
jobrien at acscience dot com



Private Sub cmdOpenFile_Click()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse =
0

Dim strLine As String
Dim strFileSpec As String
Dim fs, f, ts
Dim x

strFileSpec = Me.txtFileSpec.Value

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(strFileSpec)
Set f2 = fs.GetFile("c:\tempdata.txt")
Set ts = f.OpenAsTextStream(ForReading, TristateFalse)
Set ts2 = f2.OpenAsTextStream(ForWriting, TristateFalse)
iBytes = f.Size

DoCmd.OpenForm ("frmProgress")
Forms!frmProgress!lblBlah.Caption = "Importing Data from File: " &
strFileSpec
iProgressBarBGWidth = Forms!frmProgress!boxProgressBG.Width
iProgressBarTick = iProgressBarBGWidth / 100
iProgressBarWidth = Forms!frmProgress!boxReadProgress.Width

iTick = iBytes * 0.01
iTick = Round(iTick)

x = 0
i = 0
Do While Not ts.AtEndOfStream
x = x + 1
If x Mod 100 = 0 Then
Forms!frmProgress!lblReadProgressMsg.Caption = "
Reading Record From File: " & x
Forms!frmProgress!lblBlah.Caption = "Reading Data from
File: " & strFileSpec
End If
DoEvents
strLine = ts.ReadLine
If x Mod 100 = 0 Then
Forms!frmProgress!LblModProgressMsg.Caption = "Processing
Record Structure: " & x
Forms!frmProgress!lblBlah.Caption = "Modifying Data
Structure "
End If

arFields = Split(strLine, Chr(34))
arIPDTTZ = Split(arFields(0), " ")
strDateTime = Right(arIPDTTZ(3), Len(arIPDTTZ(3)) - 1)
arRetCodeBytes = Split(arFields(2), " ")
If arRetCodeBytes(1) = "-" Then arRetCodeBytes(1) = 0
If arRetCodeBytes(2) = "-" Then arRetCodeBytes(2) = 0
arMethodRequestHTTPVer = Split(arFields(1), " ")

strIP = arIPDTTZ(0) & vbTab
strReqDate = Left(strDateTime, 11) & vbTab
strReqTime = Right(strDateTime, 8) & vbTab
strMethod = Chr(34) & arMethodRequestHTTPVer(0) & Chr(34) &
vbTab
strRequest = Chr(34) & arMethodRequestHTTPVer(1) & Chr(34) &
vbTab
strHTTPVer = Chr(34) & arMethodRequestHTTPVer(2) & Chr(34) &
vbTab

iRetCode = arRetCodeBytes(1) & vbTab
iBytes = arRetCodeBytes(2) & vbTab
strReferer = Chr(34) & arFields(3) & Chr(34) & vbTab
strClient = Chr(34) & arFields(5) & Chr(34) & vbTab
If x Mod 100 = 0 Then
Forms!frmProgress!lblWriteProgressMsg.Caption = "
Writing Record To File: " & x
Forms!frmProgress!lblBlah.Caption = "Writing Data to File:
c:\tempdata.txt"
End If
ts2.WriteLine strIP & strReqDate & strReqTime & strMethod &
strHTTPVer & strRequest & iRetCode & iBytes & strReferer & strClient

i = i + Len(strLine)
If i > iTick Then
iProgressBarWidth = iProgressBarWidth + iProgressBarTick
Forms!frmProgress!boxReadProgress.Width =
iProgressBarWidth
Forms!frmProgress!boxModProgress.Width = iProgressBarWidth
Forms!frmProgress!boxWriteProgress.Width =
iProgressBarWidth
i = 0
End If

Loop

ts.Close
ts2.Close
Forms!frmProgress!boxReadProgress.Width = iProgressBarBGWidth
Forms!frmProgress!boxModProgress.Width = iProgressBarBGWidth
Forms!frmProgress!boxWriteProgress.Width = iProgressBarBGWidth

Forms!frmProgress!lblReadProgressMsg.Caption = " Reading Record
From File: " & x
Forms!frmProgress!LblModProgressMsg.Caption = "Processing Record
Structure: " & x
Forms!frmProgress!lblWriteProgressMsg.Caption = " Writing
Record To File: " & x
DoEvents
Forms!frmProgress!lblBlah.Caption = "Importing Data from File:
c:\tempdata.txt"
DoCmd.TransferText acImportDelim, "specApacheLog", "tblApacheLog",
"c:\tempdata.txt"
MsgBox x & " Records successfully Imported to tblApacheLog"

Forms!frmProgress!lblBlah.Caption = "Updating tblIP"
DoEvents
CurrentDb().Execute "aqryDistinctIP"

Forms!frmProgress!lblBlah.Caption = "Updating tblReqMethod"
DoEvents
CurrentDb().Execute "aqryDistinctReqMethod"

Forms!frmProgress!lblBlah.Caption = "Updating tblHTTPVer"
DoEvents
CurrentDb().Execute "aqryDistinctHTTPVer"

Forms!frmProgress!lblBlah.Caption = "Updating tblRequest"
DoEvents
CurrentDb().Execute "aqryDistinctRequest"

Forms!frmProgress!lblBlah.Caption = "Updating tblReferer"
DoEvents
CurrentDb().Execute "aqryDistinctReferer"

Forms!frmProgress!lblBlah.Caption = "Updating tblClient"
DoEvents
CurrentDb().Execute "aqryDistinctClient"

Forms!frmProgress!lblBlah.Caption = "Updating tblRetCode"
DoEvents
CurrentDb().Execute "aqryDistinctRetCode"

Forms!frmProgress!lblBlah.Caption = "Updating tblSessions"
DoEvents
CurrentDb().Execute "aqryFillSessions"

Forms!frmProgress!lblBlah.Caption = "Updating tblImportSummary "
DoEvents
CurrentDb().Execute "aqryInsertImportSummary"

Forms!frmProgress!lblBlah.Caption = "Done Updating"

Set fs = Nothing

End Sub