Reading in Dates from a text file - Help Please

[Peter]

Hello,

I am having alot of trouble reading in a date values from a text file
which is exported from another application.


My program is pretty basic, I read in values from a plain text file
which had comma seperated values. I use the input statement to read
the values from the file. eg. Input #1, name, dte1, tme1


The text file conists of lines like;


1.167,08/07/2006,02:16:56.2


If I have my variables defined as strings, name and tme1 read in fine,
the date (dte1) reads in as "8".


I have tried changing the data type of dte1 to date and variant, but
still no luck.


If the values in the text file have a "" around them the program works
fine. But the text file is output from a proprietry piece of software
which i can't change.


Any help or tips will be appreciated.


thanks


Peter

 

[Guest]

Hi Peter,

The following modification seems to work okay, if you remove the trailing
".2" from the indicated time component in the text file:


Option Compare Database
Option Explicit

Public Sub Test()
On Error GoTo ProcError

Dim strInputFile As String
Dim name As String
Dim strDate As String
Dim strTime As String
Dim dte1 As Date
Dim tme1 As Date

strInputFile = Nz(Win32OpenCommDlg _
(InitialDir:=CurrentProject.Path, DefaultExt:="*.txt", _
DialogTitle:="Select the Text File."), "")

If Len(strInputFile) = 0 Then
GoTo ExitProc ' User clicked on Cancel
End If

' Open strInputFile for reading
Open strInputFile For Input As #1



Do While (Not EOF(1))

Input #1, name, strDate, strTime
dte1 = CVDate(strDate)
tme1 = CVDate(strTime)

Debug.Print name
Debug.Print dte1
Debug.Print tme1

Loop


ExitProc:
Close
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Test..."
Resume ExitProc
End Sub


The Win32OpenCommDlg function can be found here:
API: Call the standard Windows File Open/Save dialog box
http://www.mvps.org/access/api/api0001.htm


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Albert D. Kallal]

It is not clear why you are writing code and using input# here?

Why not just use the built in input features of ms-access? (eg: use the
transfer text command????).

I just pasted your example data into a text file. I called it t.txt

t.txt
1.167,08/07/2006,02:16:56.2
1.167,08/07/2006,02:16:56.2
1.167,08/07/2006,02:16:56.2

the above data imports no problem into ms-access, so I don't see why you
need to use input#, or any code at all.

However, just to see if code could also import the data...

I just tried the following code:

Sub test56()

Dim intF As Integer
Dim strF As String
Dim a As String
Dim b As String
Dim c As String

Dim strLine As String

strF = "c:\t.txt"
intF = FreeFile()

Open strF For Input As #intF

Do While EOF(intF) = False
Input #intF, a, b, c
Debug.Print a, CDate(b), c
Loop

Close intF

End Sub

The above data also was imported without difficulty.

The output was:

1.167 08/07/06 02:16:56.2
1.167 08/07/06 02:16:56.2
1.167 08/07/06 02:16:56.2

So, your example data does not need the above input code. However, the
example code above did work just fine....

since your example data can be imported by using the file->get external
data, then you can even specify that the 2nd column is a date field during
import.

So, no need to use the above input# code, and the ms-access standard import
worked just fine for me. In fact, I was even able to set the 2nd field as a
date during import.

If you don't want/need users to use the file->get external data (which works
just fine), then that means you can use the transfertext command (which is
the equivalent of going file->get external data).

So, I don't think you need the input# code.....

 

[Albert D. Kallal]

If the code is writting as:

Debug.Print a, CDate(b), c, CDate(b) + CDate(Left(c, 8))

The output is:

1.167 08/07/06 02:16:56.2 08/07/06 2:16:56 AM
1.167 08/07/06 02:16:56.2 08/07/06 2:16:56 AM
1.167 08/07/06 02:16:56.2 08/07/06 2:16:56 AM

Note how the last expression produces a date+time field.

 

[Guest]

PS. I forgot to say that "name" is a reserved word. Although the code I
provided works (I tested it before posting), it is best to avoid using any
reserved words for things that you assign a name to in Access. Here is a nice
comprehensive list of reserved words published by Access MVP Allen Browne:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Allen also makes a nice utility available for examining existing databases
for reserved words, along with several other issues.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html