Open a file read it AND write back at next line? 3

[PWD]

Is it possible to open a file and insert data and re-save it? I have the following

 Open Filename For Input Access Read As #1

. Then I

Do Until EOF(1)
    Line Input #1

then loop through looking for a specific line, examine it & compare that part with an SQL View. So far, so good. What I want to be able to do is, where there's a match between the line and the SQL View, be able to write a field from the View back into the file at the next line (which is always blank at the moment). How can I write this data (that I've got working with my code OK) back to the file & save it?

Many thanks,
Des.

 

[Tom4511]

You are opening this file as Read only so you won't be able to Write to the file in this mode. You'd want to use the Read Write mode.

 

[PWD]

I think I would Tom. I can't use

 Output

as that wipes the file - so I'm lead to believe. All this data is part of our electronic invoicing so it can't be c*cked up. At the very worst I'd be prepared to have to completely replace each relevant line (prefixed with “INVC05”) as I went through the file. Any ideas?

Many thanks,
Des.

 

[HughLerwill]

>back into the file at the next line (which is always blank at the moment).

Unless that line has been intentionally padded out (with e.g. spaces) it will probably only consist of one or two characters i.e. a line feed or a carriage return or both. That will not give you adequate room to 'insert' anything into.

I suggest reading the whole file into a string which is then modified and saved back out again to the same or alternative file name.

You are dealing with sequential files which can only be opened for Input (Read), Output (Write) or Append. If you want to read and write to the same file you should be investigating Random Access files; this approach however dictates that each line (record) in the file has equal length so unless you have control over the program that intially created the input file it is unlikely to suit you.

 

[Tom4511]

Hi Des:

You really have two choices, as HughLerwill suggested.

1. Continue to access your data file sequentially. Read all data into variables. Do your modification on the variables and then write the file back to disk.

2. Convert your data file to Random Access where you can use the Read Write option. Once converted you can read and write individual records (lines). As was stated, you'll need to set a record size for each record.

Your choice may be dictated by the size of your data file.

Tom

 

[PWD]

Hi guys. These are .flt files with variable length lines, e.g.

ENV001^50135460555^INVOICE^^NNN Newcastle^50135460555^^XYZ LTD
INVH01^167 Road^Newcastle Upon Tyne^^^ABC 2BS^MONT010^P O BOX 999^555 LEEDS ROAD^HUDDERSFIELD, WEST YORKS  ABC 4XN^00141749^4994896
INVH02
INVH03^6980^0001^090415
INVC01^XYZ LTD-TYNESIDE^OWN COLLECTION^^^^0511/03755526^090414^090414^No Settlement Discnt^000000^000000^0000^0000^^033
INVC02
INVC03^351213912^318276^090409^03755526^090414^------
INVC04^P0000071^^^^^EA^^0000000002000^000222200^EA^00000111100^S^015000^13 x 2440 x 1220  CC WHITE OAK A/B^MDF^00000222200^000000000^000000
INVC05

I need to add a certain comment in line INVC05 based on the ‘value’ of the ‘P’ number on the previous line, i.e. we’ll want to add the data from the SQL View associated with that product. So if this is P0000071 then we want to add “FSC MIXED 80%” so that it will read

INVC04^P0000071^^^^^EA ……….. etc. 
INVC05^FSC MIXED 80%

I’m OK with getting to line INVC04 and getting the data from the SQL View based on the ‘P’ code, I just need to get “INVC05^FSC MIXED 80%” on the next line. (The “^” is used by the people who process our files and send them to our customers.)

Is there any mileage in trying to use FileSystemObject or some other way to read in the whole file? None of the files are going to be longer than a couple of hundred lines or so? Most are going to be considerably smaller.

Does removing the

 Access Read

have any impact at all – it seems to function OK?

Des.

 

[HughLerwill]

Something like;

Private Sub Command1_Click()

Dim v As Variant
Dim i As Long, f as long

f = FreeFile
Open "datafile.txt" For Input As f
v = Split(Input$(LOF(f), f), vbCrLf)
Close f

For i = 0 To UBound(v)
'Print v(i)
If i > 0 Then
If v(i) = "INVC05" And Mid$(v(i - 1), 8, 8) = "P0000071" Then
v(i) = v(i) & "^FSC MIXED 80%"
End If
End If
Next

f = FreeFile
Open "datafile2.txt" For Output As f
Print #f, Join(v, vbCrLf);
Close f

End Sub

 

[PWD]

Thanks Hugh. Food for thought. I'm just testing a basic variation on this (because I don't really need to do the

If

as I have resolved that part by accessing the view in SQL) but it doesn't like

Open "datafile2.txt" For Output As f

"Bad file name or number" I even created this file just in case that made any difference – but it didn’t.

My basic code is

Dim v As Variant
    Dim i As Long, f As Long

    f = FreeFile
    Open "c:\mernet\E-IN698017042009B.FLT" For Input As f
        v = Split(Input$(LOF(f), f), vbCrLf)
    Close f
    
    For i = 0 To UBound(v)
        Print v(i)
    Next
    
    f = FreeFile
    Open " c:\mernet\datafile2.txt" For Output As f
        Print #f, Join(v, vbCrLf);
    Close f

Did I do something wrong?

Des.

 

[MakeItSo]

Hi PWD,

just a minor error:

get rid of the space before "c:\" in the output line.

Open [u]" [/u]c:\mernet\datafile2.txt" For Output As f

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[PWD]

What a div! Copy & Paste over the original. Many thanks. Now for some more testing. -

Des.

 

[PWD]

Good morning. I can now get to the stage where my program reads in a file OK.

 ediFilenames(j) = strFoldername & "\" & edifilename
Open ediFilenames(j) For Input As #1
k = 0 'First line of the file
Do Until EOF(1)
    Line Input #1, strEditext(k)
    k = k + 1
Loop

    Close 1

This is probably very clumsy, but it’s really what I inherited – and it kinda works.

The next bit is to examine the lines looking for our Product Number, which always starts with “P0” (Pee Zero).

 For l = 0 To k
 
 ' Split the data into separate variables
strEdidata = Split(strEditext(l), "^")

If Left$(strEdidata(1), 2) = "P0" Then

Do some stuff & find suitable text (FSCDesc) associated with the relevant ‘P’ number.

Then, so I believe, I want to start building up the “Print”.

If I just leave it as

 Print strEditext(l)

then it will just reproduce the original file when I use

     Open strFoldername & "\datafile2.txt" For Output As 1
     Print #1, Join(strEditext, vbCrLf);
Close #1 ' Close the file

I’ve tried using something different

 MyPrintString(l) = strEditext(l)
Print MyPrintString(l)

I want to use the text that I obtained when found the data (above) so I also have

 MyPrintString(l) = strEditext(l + 1) & FSCDesc

This puts nothing in the file when I create the file.

     Open strFoldername & "\datafile2.txt" For Output As 1
    Print #1, Join(MyPrintString, vbCrLf);
Close #1 ' Close the file

Again it’s probably a very fundamental error but as this is the first time I’ve had to do this, I’ve no experience of trouble-shooting it.

Assistance would be gratefully received. Many thanks,

Des.

 

[HughLerwill]

You seem to have diverted a little from the original plan; how about something like;

Private Sub Command1_Click()

Dim v As Variant
Dim i As Long
Dim LookUp As String
Dim RequiredText As String

f = FreeFile
Open "datafile.txt" For Input As f
v = Split(Input$(LOF(f), f), vbCrLf)
Close f

For i = 0 To UBound(v)
If v(i) Like "*^P0######^*" Then
LookUp = Mid$(v(i), InStr(v(i), "^P0") + 1, 8)
'use LookUp to retrieve the required text from your database
'you must insert that code here
'then modify the next line in the file
v(i + 1) = v(i + 1) & RequiredText
End If
Next

f = FreeFile
Open "datafile2.txt" For Output As f
Print #f, Join(v, vbCrLf);
Close f

End Sub

 

[PWD]

Looking good, Hugh. I won't confess to fully understanding the whole process but it seems more efficient that what I had previously. Just refining so it can cope with all the files in the folder.

I did the following to identify the Part Number

 SplitLine = Split(v(i), "^")
        PartNumber = SplitLine(1)

so I could use this to get the text from the SQL View

Set cn = New ADODB.Connection
'specify the connectionstring
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=KerridgeExports;Data Source=BAMBI"
        'open the connection
        cn.Open
        'instantiate the recordset object
        Set rs = New ADODB.Recordset
        'open the recordset table - in this case the View "vwFIBRE"
        With rs
        .Open "vwFIBRE", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        'loop through the records until reaching the end or last record
            Do While Not .EOF
                If PartNumber = rs.Fields("Part") Then GoTo 10
                'Part Number matches the 'P' number from the SQL View

                'If Right$(PartNumber, 7) < Right$(rs.Fields("Part"), 7) Then GoTo 20
        'This line would be to stop looking through when the Part Number
        'is greater than largest 'P' number from the SQL View
        rs.MoveNext 'moves next record
            Loop
        End With
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

10:
 FSCDesc = rs.Fields("FSC Desc") 'FSC Description
            v(i + 1) = v(i + 1) & FSCDesc
        End If
    Next

Many thanks again,

Des.

 

[HughLerwill]

Your

SplitLine = Split(v(i), "^")
PartNumber = SplitLine(1)

is going to fail unless the line/ v(i) being read contains at least one "^".

 

[PWD]

Which it always does (if it contains the 'P' Part Number) - for this application of the program in our business anyway. This is SOOO good now. Just got a bit carried away with more testing. Star on its way.

Des.

 

[Andrzejek]

I would advise to NOT use GoTo, bad coding,

Instead of looping thru all records in your rs, you could use rs.Filter, it is much faster and easier in your logic:

rs.Open "vwFIBRE", cn, adOpenKeyset, adLockPessimistic, adCmdTable
...
rs.Filter = " Part = '" & PartNumber & "'"
FSDesc = rs.Fields("FSC Desc").Value
...

rs.Close

Have fun.

---- Andy

 

[PWD]

Hi Andy. I'm all for quicker. Smokin'!! Lord knows how much of the rest of the code I inherited is pants. A star for you too.

Des.

 

[PWD]

Dang. Just thought I'd better test when the 'P' number isn't on the list from the View - as not all products are on the FSC list. It falls over as it gets to the end of the file without finding a match. I thought about checking for EOF but the last record may be a match.

Des.

 

[PWD]

I just used

On Error Resume Next

but would have preferred to just exit the 'If'. Oh well. Upwards & Onwards!!

Des.

 

[Andrzejek]

So you haven't used my .Filter idea.....

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.CursorType = adOpenForwardOnly
rs.CursorLocation = adUseClient
rs.LockType = adLockReadOnly

rs.Open "Select * From vwFIBRE", Cn

...
rs.Filter = " Part = '" & PartNumber & "'"

Select Case rs.RecordCount
   Case 1
      FSDesc = rs.Fields("FSC Desc").Value
   Case 0
      FSDesc = "No Desc for " & PartNumber
   Case Else
      FSDesc = "More than one Desc for " & PartNumber
End Select
...

rs.Close
Set rs = Nothing

Have fun.

---- Andy