Text File reading Errors 2

[Junkie88]

The following code is suppose to read a text file.
The problem is that the code is only reading the first line. The other lines are not getting read.

Second problem is that the .Fields(Counter) = Now is not working. The error says data type conversion error, which I do not understand since the second data field is Date/Time type and the format is General Date (which is what Now returns).

And the third problem is that the date field from the text file does not get transferred correctly. It is converted to 1/1/2009 no matter what the date is.

Here is what the text file looks like:
105 1 1 4/12/2008 2 0 0 1
2009 2 3 4/13/2008 5 0 2 0
3004 4 2 3/27/2008 3 2 0 0

Actually, the date field is suppose to look like
4/12/2008 5:30pm instead of just 4/12/2008. But I can't event get the simple one read correctly. I tried putting quotes around the date ("4/12/08" and also "4/12/2008 5:30pm"). Can someone help, please?


Dim DataRead, db, rst, Counter

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM PDALOGS")
With rst

Open "pdalog.txt" For Input As #1
While Not EOF(1)
For Counter = 1 To 8
Input #1, DataRead
.AddNew
If Counter = 2 Then
.Fields(Counter) = Now
ElseIf Counter = 5 Then
.Fields(Counter) = CDate(DataRead)
Else
.Fields(Counter) = DataRead
End If
Next
.Update
Wend
Close
.Close
End With

 

[Remou]

Fields start numbering from 0, so field(2) is the third field.

DataRead is the full line:
105 1 1 4/12/2008 2 0 0 1

So do you want ?
.Fields(Counter) = DataRead

 

[PHV]

Furthermore I'd put the .AddNew line before the For loop.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Junkie88]

Well, each number on every line of the text file belongs to a data field of the table PDALOGS. I figured out the first question, that is how to read line by line from the text file and put every number of each line in the appropriate data field of the table. But I would like to figure out the date conversion confusion. If someone can help with that, it will be awesome.

 

[Remou]

Did you get a chance to read my post?

 

[Junkie88]

Yes, I read your post, but it is still not working. Infact, here is what I have done to read the entire line and then parse it after every number entry. And now none of the numbers is getting read properly, I am guessing because I am reading them in as a string. Not sure what do?

And another new error that I am getting is index or primary key cannot contain a null value and the error is at .Update.

Dim DataRead, db, rst, Counter, iPos

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM PDALOGS")
With rst

Open "pdalog.txt" For Input As #1
While Not EOF(1)
iPos = 0
Line Input #1, DataRead
iPos = InStr(iPos + 1, DataRead, "")
WordRead = Left(DataRead, iPos)
For Counter = 0 To 8
.AddNew
If Counter = 1 Then
.Fields(Counter) = Now
ElseIf Counter = 4 Then
.Fields(Counter) = CDate(WordRead)
Else
.Fields(Counter) = WordRead
End If
Next
.Update
Wend
Close
.Close
End With

 

[Remou]

Try Split on the string to get an array. I suggest you look at PHV's post above, too.

 

[PHV]

Did you get a chance to understand my post?

 

[Junkie88]

Okay, I have incorporated both suggestions in the code below. Now I am getting type mismatch error at .Fields(Counter) = CInt(arList(iPos)). I also tried it without CInt and with Int. Same error.

And when I tried to print out the arList(iPos), it shows the entire line of the text file not the individual numbers on the line.

Dim DataRead, db, rst, Counter, iPos
Dim arList As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM PDALOGS")
With rst

Open "pdalog.txt" For Input As #1
While Not EOF(1)
Line Input #1, DataRead
.AddNew
iPos = 0
For Counter = 0 To 8
arList = Split(DataRead, " ")
If Counter = 1 Then
.Fields(Counter) = Now
ElseIf Counter = 4 Then
.Fields(Counter) = CDate(arList(iPos))
iPos = iPos + 1
Else
.Fields(Counter) = arList(iPos)
iPos = iPos + 1
End If
Next
.Update
Wend
Close
.Close
End With

 

[Remou]

How about:

Dim DataRead, db, rst, Counter, iPos
    Dim arList 'As String
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM PDALOGS")
    With rst
    
    Open "C:\users\Fionnuala\Desktop\pdalog.txt" For Input As #1
            While Not EOF(1)
            Line Input #1, DataRead
            arList = Split(DataRead, "    ")
            .AddNew
            iPos = 0
                For Counter = 0 To 7
                  If Counter = 1 Then
                    .Fields(Counter) = Now
                  ElseIf Counter = 4 Then
                    .Fields(Counter) = CDate(arList(Counter))
                  Else
                    If arList(Counter) <> "" Then
                        .Fields(Counter) = Val(arList(Counter))
                    End If
                  End If
                Next
            .Update
            Wend
        Close #1
    .Close
    End With

 

[Junkie88]

I get datatype conversion error at

.Fields(Counter) = Val(arList(Counter))

 

[Remou]

What is the structure of your table? What is the value of Counter? What is the value of arList(Counter)?

 

[Junkie88]

The datatypes of the fields of the tables are:
Number (Field Size Integer)
Date/Time (Format General Date, dd/mm/yyy 00:00:00 AM/PM)
Number (Field Size Integer)
Number (Field Size Integer)
Date/Time (Format Short Date, dd/mm/yyyy)
Number (Field Size Integer)
Number (Field Size integer)
Number (Field Size Long Integer)
Number (Field Size Integer)

I am not sure what you mean by the value of Counter, Counter is suppose to be Integer. Originally, I had declared arList as an array of strings.

 

[Remou]

The value of counter will be between 0 and 7. Debug.Print is one way to get this information:

<...>
Debug.Print Counter; arList(Counter)
If arList(Counter) <> "" Then
      .Fields(Counter) = Val(arList(Counter))
End If
<...>

 

[Remou]

You show nine fields but the data only has eight, which field is not used?

 

[Junkie88]

The counter value is 0. The nineth field is added by the statement .Fields(Counter) = Now

 

[Remou]

The counter value is 0.
You do not show the value of arList. In your sample data, you show the fields separated by four spaces, so I changed the split to " " (4 spaces), however, your data may be separated by tabs or fewer spaces, in which case the split will not be returning proper data. Add this:

For i=0 to ubound(arlist)
Debug.Print i; "  "; arList(i)
Next

To the code after you create the array, to see the value of each element.

The nineth field is added by the statement .Fields(Counter) = Now
Not that I can see.

 

[Junkie88]

Okay, it is working now. The final version of the code is given below. The change that I made was replace all the tabs in the text file with single spaces.

Dim DataRead, db, rst, Counter, iPos
Dim arList

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM PDALOGS")
With rst

Open "C:\Documents and Settings\Entanglement\Desktop\pdalog.txt" For Input As #1
While Not EOF(1)
Line Input #1, DataRead
arList = Split(DataRead, " ")
.AddNew
iPos = 0
For Counter = 0 To 8
If Counter = 1 Then
.Fields(Counter) = Now
ElseIf Counter = 4 Then
.Fields(Counter) = CDate(arList(iPos))
iPos = iPos + 1
Else
If arList(iPos) <> "" Then
.Fields(Counter) = Val(arList(iPos))
iPos = iPos + 1
End If
End If
Next
.Update
Wend
Close #1
.Close
End With