Endless loop while reading text into recordset 1

[kalle82]

Hi!

My problem first, im getting stuck in an endless loop while reading data into my recordset.

Plattform: Excel 2003

Okay so im working with this textfile. I need to read it, manipulate it and save it back with the new info.

After some thinking I think that using ADO technique is the best way to do this.. I have spent some time reading

http://msdn.microsoft.com/en-us/library/ms974559.aspx

which in turn covers the use of ADO when reading it into a textfile..

This is my textfile data

datum1, aktivitet, datum2
#2011-06-24#,XXXXXXXXXXX,2011-06-28
#2011-06-25#,XXXXXXXXX,2011-06-29
#2011-06-28#,XXXXXX XXXXXXXX,2011-07-06
#2011-07-01#,XXX XXX XXXX,2011-07-09
#2011-07-01#,XXXXX XXXX,2011-07-09
#2011-07-06#,XXXXX XXXXXXXXX,2011-07-24

To read this textfile into a recordset i use this code, but I get stuck in a neverending loop?!

Does someone have any tips on how to solve this?

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Set objConnection = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")

strPathtoTextFile = "k:\handläggningsstöd\Ärenden\"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""
MsgBox ("halvägs")
objrecordset.Open "SELECT * FROM logg-för-9001161778.ini", _
          objConnection, adOpenStatic, adLockOptimistic, adCmdText

MsgBox ("filen öppnad")

Do Until objrecordset.EOF
           
    Wscript.Echo "Name: " & objrecordset.Fields.Item("datum1")
    
    Wscript.Echo "Department: " & objrecordset.Fields.Item("aktivitet")
    Wscript.Echo "Extension: " & objrecordset.Fields.Item("datum2")
    
    objrecordset.MoveNext
Loop

 

[PHV]

Comment out the On Error Resume Next instruction.

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

 

[kalle82]

Thanks.. Well know i can see the errors.. First error has to do with the file im trying to open... it, says my FROM syntax is wrong.

i use variable to dynamicl tell my program which logfile to open... but this time i hardcoded it seems sql can handle this row..

objrecordset.Open "SELECT * FROM logg-för-9001161778.ini", _
          objConnection, adOpenStatic, adLockOptimistic, adCmdText

My filename "logg-för-9001161778.ini" seems to wreck the syntax.. can SQl handle "ö" and hyphens in a query?

Well i did some further testing..

I did a copy of that file renamed it into "korv.in" and pointed my code to that instead. Now my error message tells me that the program can't uppdate database or file because it's write protected?

Hmm this seems to be alot harder than the link suggested... Anyone ahve some spare code that have done this before?

Cheers!

 

[Andrzejek]

I need to read it, manipulate it and save it back with the new info

I see your text file is a simple comma delimited text file.

How do you want to manipulate it ?


Have fun.

---- Andy

 

[kalle82]

everything in the file is read into a listview control, and colored based on the second date´.

But sometimes these datevalues needs to be updated, for example...

I have built a sub who takes the index value from the listbox to find the correct row, and then loops down a shows the corerct date... What I need to do is to change that date into something else. That's when I came across ADO, if i read everything into a recordset, I can easily manipulate it (see below), and write i right back into the file...

What I want do to is to change to be able to change the datevalue on row 4 (2011-07-09) as an example

datum1, aktivitet, datum2
#2011-06-24#,XXXXXXXXXXX,2011-06-28
#2011-06-25#,XXXXXXXXX,2011-06-29
#2011-06-28#,XXXXXX XXXXXXXX,2011-07-06
#2011-07-01#,XXX XXX XXXX,2011-07-09
#2011-07-01#,XXXXX XXXX,2011-07-09
#2011-07-06#,XXXXX XXXXXXXXX,2011-07-24

Was that clear at all?!?! Thanks for looking into this!

/Carl

 

[Andrzejek]

So basically you want to change [blue]Blue[/blue]:

datum1, aktivitet, datum2
#2011-06-24#,XXXXXXXXXXX,2011-06-28
#2011-06-25#,XXXXXXXXX,2011-06-29
#2011-06-28#,XXXXXX XXXXXXXX,2011-07-06
#2011-07-01#,XXX XXX XXXX,[blue]2011-07-09[/blue]
#2011-07-01#,XXXXX XXXX,2011-07-09
#2011-07-06#,XXXXX XXXXXXXXX,2011-07-24

to [red]Red[/red]:

datum1, aktivitet, datum2
#2011-06-24#,XXXXXXXXXXX,2011-06-28
#2011-06-25#,XXXXXXXXX,2011-06-29
#2011-06-28#,XXXXXX XXXXXXXX,2011-07-06
#2011-07-01#,XXX XXX XXXX,[red]2012-08-26[/red]
#2011-07-01#,XXXXX XXXX,2011-07-09
#2011-07-06#,XXXXX XXXXXXXXX,2011-07-24

in your text file?

Have fun.

---- Andy

 

[kalle82]

yes exactly been trying to dothat for five days now emplying different approaches..

 

[Andrzejek]

To me this is just a text file.
To do replacing the blue part with the red date (above), try:

Dim strTextLine As String
Dim strFile As String
Dim s

strFile = "C:\TMP\logg-for-9001161778."
[green]
'Rename file to be deleted later[/green]
Name strFile & "ini" As strFile & "old"

Open strFile & "old" For Input As #1
[green]
'Open new empty file with original name[/green]
Open strFile & "ini" For Output As #2

Do While Not EOF(1)
    Line Input #1, strTextLine
    s = Split(strTextLine, ",")
    [green]
    'Modify anythig you want[/green]
    If s(2) = "2011-07-09" Then
        s(2) = "2012-08-26"
    End If
    
    Print #2, s(0) & "," & s(1) & "," & s(2)
Loop

Close #2
Close #1
[green]'Delete original old file[/green]
Kill strFile & "old"

End Sub

Have fun.

---- Andy

 

[kalle82]

Hmm cool! But I will have textfiles where several of the dates are the same. Is there a way to pick the right one.. Lets say we want the date on line 4 but lines 1,2,3 have the same date.. Then the if statement would go nuts is there a way to solve that?

 

[Andrzejek]

For example:

[blue]
Dim intL As Integer[/blue]
...
Do While Not EOF(1)
    Line Input #1, strTextLine[blue]
    intL = intL + 1[/blue]
    s = Split(strTextLine, ",")
    [green]
    'Modify anythig you want[/green][blue]
    MsgBox "You are messing with line number " & intL[/blue]
    If s(2) = "2011-07-09" Then
        s(2) = "2012-08-26"
    End If
    
    Print #2, s(0) & "," & s(1) & "," & s(2)
Loop
...

Have fun.

---- Andy

 

[Andrzejek]

In other words:

[blue]Dim intL As Integer[/blue]
...
Do While Not EOF(1)
    Line Input #1, strTextLine[blue]
    intL = intL + 1[/blue]
    s = Split(strTextLine, ",")
    [green]
    'Modify line 4[/green][blue]
    If intL = 4 Then[/blue]
        s(2) = "2012-08-26"
    End If
    
    Print #2, s(0) & "," & s(1) & "," & s(2)
Loop
...

Have fun.

---- Andy