ExcelBanter - Reading dates from text file

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Reading dates from text file (https://www.excelbanter.com/excel-programming/322356-reading-dates-text-file.html)

Reading dates from text file

Hi

I have a comma-separated text file with several pieces of data in it. I'm
doing this sort of thing to read them in:

Dim ID
Dim DateVar
Dim Name
Dim CodeNo
Open "import.txt" For Input As #1
Do Until EOF(1)
Input #1, ID
Input #1, DateVar
Input #1, Name
Input #1, CodeNo
'Data processing code here
Loop
Close #1

The trouble is that the dates aren't reading in properly. The lines in the
file are in this format:

1,13/03/2004,Chris,4745
2,27/06/2004,Josh,5684
etc.

The problem is that after running the code above, the DateVar variable only
holds the day, not the month (ie. 13 instead of 13/03/2004). If I change Dim
DateVar to Dim DateVar As Date, then I get an Overflow (RTE 6).

Any ideas as to how to get the date reading properly?

Thanks
Chris

Reading dates from text file

I am not following your code. Input reads all the way up to the new line. So
how are you parsing the individual lines? For a CSV file (which is
essentially what you have here) you should look at using the split function.

HTH

"Chris Mahoney" wrote:

Hi

I have a comma-separated text file with several pieces of data in it. I'm
doing this sort of thing to read them in:

Dim ID
Dim DateVar
Dim Name
Dim CodeNo
Open "import.txt" For Input As #1
Do Until EOF(1)
Input #1, ID
Input #1, DateVar
Input #1, Name
Input #1, CodeNo
'Data processing code here
Loop
Close #1

The trouble is that the dates aren't reading in properly. The lines in the
file are in this format:

1,13/03/2004,Chris,4745
2,27/06/2004,Josh,5684
etc.

The problem is that after running the code above, the DateVar variable only
holds the day, not the month (ie. 13 instead of 13/03/2004). If I change Dim
DateVar to Dim DateVar As Date, then I get an Overflow (RTE 6).

Any ideas as to how to get the date reading properly?

Thanks
Chris

Reading dates from text file

It sure looks like input# sees that as some sort of special character.

I would either read the whole line in with "line input #" and parse it myself or
change the program that creates the text file to drop the slashes and then
convert it to a date in the code.

Chris Mahoney wrote:

Hi

I have a comma-separated text file with several pieces of data in it. I'm
doing this sort of thing to read them in:

Dim ID
Dim DateVar
Dim Name
Dim CodeNo
Open "import.txt" For Input As #1
Do Until EOF(1)
Input #1, ID
Input #1, DateVar
Input #1, Name
Input #1, CodeNo
'Data processing code here
Loop
Close #1

The trouble is that the dates aren't reading in properly. The lines in the
file are in this format:

1,13/03/2004,Chris,4745
2,27/06/2004,Josh,5684
etc.

The problem is that after running the code above, the DateVar variable only
holds the day, not the month (ie. 13 instead of 13/03/2004). If I change Dim
DateVar to Dim DateVar As Date, then I get an Overflow (RTE 6).

Any ideas as to how to get the date reading properly?

Thanks
Chris

--

Dave Peterson

Reading dates from text file

I expect the problem is the date format.

If the dates are read in as strings, then converted with CDate (see below),
VBA will TRY to interpret a date as mm/dd/yyyy whenever possible. If not
possible, it will try dd/mm/yyyy.

The following works for me and interprets his 2 dates correctly. However, I
added a line with the date 1/12/2004, which he would intend to be Dec 1. CDate
takes it as Jan 12.

The conclusion seems to be that the dates must be read in as text, then
manually converted to the appropriate date.

Sub Test()
Dim ID As Long
Dim DateVar As String
Dim Name As String
Dim CodeNo As Long
Open "import.txt" For Input As #1
Do Until EOF(1)
Input #1, ID
Input #1, DateVar
Input #1, Name
Input #1, CodeNo
'CDate interprets as mm/dd/yyyy whenever possible, only
'as dd/mm/yyyy when 1st number is 12
Debug.Print ID, CDate(DateVar), Name, CodeNo
Loop
Close #1
End Sub

On Thu, 03 Feb 2005 21:13:07 -0600, Dave Peterson
wrote:

It sure looks like input# sees that as some sort of special character.

I would either read the whole line in with "line input #" and parse it myself
or
change the program that creates the text file to drop the slashes and then
convert it to a date in the code.

Chris Mahoney wrote:

Hi

I have a comma-separated text file with several pieces of data in it. I'm
doing this sort of thing to read them in:

Dim ID
Dim DateVar
Dim Name
Dim CodeNo
Open "import.txt" For Input As #1
Do Until EOF(1)
Input #1, ID
Input #1, DateVar
Input #1, Name
Input #1, CodeNo
'Data processing code here
Loop
Close #1

The trouble is that the dates aren't reading in properly. The lines in the
file are in this format:

1,13/03/2004,Chris,4745
2,27/06/2004,Josh,5684
etc.

The problem is that after running the code above, the DateVar variable only
holds the day, not the month (ie. 13 instead of 13/03/2004). If I change
Dim
DateVar to Dim DateVar As Date, then I get an Overflow (RTE 6).

Any ideas as to how to get the date reading properly?

Thanks
Chris

Reading dates from text file

OK, I'll try to reply to all these comments in one go.

I am not following your code. Input reads all the way up to the new line. So
how are you parsing the individual lines? For a CSV file (which is
essentially what you have here) you should look at using the split function.

Input is reading up to each comma, it's not reading the entire line. I can't
find any information on the Split function (I typed Split into the VBA code
and pressed Help but it only gives me a seemingly-unrelated Split property).

I expect the problem is the date format.

If the dates are read in as strings, then converted with CDate (see below),
VBA will TRY to interpret a date as mm/dd/yyyy whenever possible. If not
possible, it will try dd/mm/yyyy.

The following works for me and interprets his 2 dates correctly. However, I
added a line with the date 1/12/2004, which he would intend to be Dec 1. CDate
takes it as Jan 12.

The conclusion seems to be that the dates must be read in as text, then
manually converted to the appropriate date.

It won't interpret either of those examples properly (eg. If I add MsgBox
DateVar where the comment is, then running the code gives me 13 and 27.

What country are you in (and therefore what international settings are you
using)? I'm in New Zealand and therefore everything defaults to
Day/Month/Year. In my International settings, I have the date separator set
to / (like it is in the text file). I have also tried it with "Leading zero
for month" both off and on, same results each time.

It sure looks like input# sees that as some sort of special character.

I would either read the whole line in with "line input #" and parse it myself
or
change the program that creates the text file to drop the slashes and then
convert it to a date in the code.

I'd forgotten about Line Input, I'll give it a go and see what happens.
Changing the text file is not really an option as I don't have the app that
created it, and if I tried to write some sort of converter I'd probably run
into the same problems I'm having now.

I'll try Line Input and let you know how I get on.

Thanks
Chris

Reading dates from text file

Split was added in xl2k. Tom Ogilvy posted a split97 that could make your life
a bit easier:

Option Explicit
Sub testme()
Dim ID As String
Dim DateVar As String
Dim myDate As Date
Dim Name As String
Dim CodeNo As String
Dim mySplit As Variant

Open "import.txt" For Input As #1
'Open "c:\my documents\excel\edit2.txt" For Input As #1
Do Until EOF(1)
Input #1, ID
Input #1, DateVar
Input #1, Name
Input #1, CodeNo

mySplit = Split97(DateVar, "/")

If UBound(mySplit) - LBound(mySplit) + 1 < 3 Then
'not a date, what should be done?
Else
'maybe some more validation????
myDate = DateSerial(mySplit(UBound(mySplit)), _
mySplit(LBound(mySplit) + 1), _
mySplit(LBound(mySplit)))
MsgBox myDate
End If
'Data processing code here
Loop
Close #1

End Sub

'from Tom Ogilvy
Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

Chris Mahoney wrote:

OK, I'll try to reply to all these comments in one go.

I am not following your code. Input reads all the way up to the new line. So
how are you parsing the individual lines? For a CSV file (which is
essentially what you have here) you should look at using the split function.

Input is reading up to each comma, it's not reading the entire line. I can't
find any information on the Split function (I typed Split into the VBA code
and pressed Help but it only gives me a seemingly-unrelated Split property).

I expect the problem is the date format.

If the dates are read in as strings, then converted with CDate (see below),
VBA will TRY to interpret a date as mm/dd/yyyy whenever possible. If not
possible, it will try dd/mm/yyyy.

The following works for me and interprets his 2 dates correctly. However, I
added a line with the date 1/12/2004, which he would intend to be Dec 1. CDate
takes it as Jan 12.

The conclusion seems to be that the dates must be read in as text, then
manually converted to the appropriate date.

It won't interpret either of those examples properly (eg. If I add MsgBox
DateVar where the comment is, then running the code gives me 13 and 27.

What country are you in (and therefore what international settings are you
using)? I'm in New Zealand and therefore everything defaults to
Day/Month/Year. In my International settings, I have the date separator set
to / (like it is in the text file). I have also tried it with "Leading zero
for month" both off and on, same results each time.

It sure looks like input# sees that as some sort of special character.

I would either read the whole line in with "line input #" and parse it myself
or
change the program that creates the text file to drop the slashes and then
convert it to a date in the code.

I'd forgotten about Line Input, I'll give it a go and see what happens.
Changing the text file is not really an option as I don't have the app that
created it, and if I tried to write some sort of converter I'd probably run
into the same problems I'm having now.

I'll try Line Input and let you know how I get on.

Thanks
Chris

--

Dave Peterson

Reading dates from text file

Split was added in xl2k. Tom Ogilvy posted a split97 that could make your
life
a bit easier:

Hmm, I'm running 2004 and Split doesn't seem to exist. Weird. I found an
example online, but I get Sub or function not defined when I try to use it.
I'll have to try out that 97 version.

Chris

Reading dates from text file

Hmm, I'm running 2004

The latest version is xl2003.

--
Regards,
Tom Ogilvy

"Chris Mahoney" wrote in message
...
Split was added in xl2k. Tom Ogilvy posted a split97 that could make
your
life
a bit easier:

Hmm, I'm running 2004 and Split doesn't seem to exist. Weird. I found an
example online, but I get Sub or function not defined when I try to use
it.
I'll have to try out that 97 version.

Chris

Reading dates from text file

My version clearly says 2004 on the box and CD, and in the About box. It's
version 11.1. Anyway, I have it all working now, so thanks everyone for all
your help :)

Chris

On 8/2/05 7:49 AM, in article , "Tom
Ogilvy" wrote:

Hmm, I'm running 2004

The latest version is xl2003.

Reading dates from text file

Those darn Macs are up to 2004 (if I believe the people who have corrected me
<vbg.)

Tom Ogilvy wrote:

Hmm, I'm running 2004

The latest version is xl2003.

--
Regards,
Tom Ogilvy

"Chris Mahoney" wrote in message
...
Split was added in xl2k. Tom Ogilvy posted a split97 that could make
your
life
a bit easier:

Hmm, I'm running 2004 and Split doesn't seem to exist. Weird. I found an
example online, but I get Sub or function not defined when I try to use
it.
I'll have to try out that 97 version.

Chris

--

Dave Peterson

Reading dates from text file