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.

 

[PWD]

On the contrary I used it & it's magnificent - as long as the part number does appear in the View. This is what I'm using.

rs.Open "vwFIBRE", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        rs.Filter = " Part = '" & PartNumber & "'"
       'Part Number matches the 'P' number from the SQL View

On Error Resume Next
 FSCDesc = rs.Fields("FSC Desc") 'FSC Description
            v(i + 1) = v(i + 1) & "^" & FSCDesc

Without the 'On error' I get a

 Run-time error '3021': Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Des.

 

[Andrzejek]

Well, you are using it (the Filter), kind of....

By 'kind of' I mean - I hope you are not re-creating your rs every time you use the Filter (are you?). That would defeat the purpose of the Filter. Those little ... mean you put your logic in here, so you just create/open your rs once at the beginning, use Filter whenever you need the Desc, and close your rx when you done with your logic.

If you can use RecordCount after you use Filter, you may use the logic I provided to detect if you have 1 record, no records, or more than one record with Desc for your PartNumber.

Have fun.

---- Andy

 

[strongm]

Wouldn't it be nice if we could incorporate Regular Expressions as well ...

 

[HarleyQuinn]

Wouldn't it be nice if we could incorporate Regular Expressions as well ...

The return of wombats and recommending Regular Expression solutions? That's the strongm I've come to know in these forums...


Must admit (probably thanks to years on here with you ), a Regular Expression was one of my first thoughts as well.

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[strongm]

Oh, not just a normal regular expressions solution, but one using a little-documented feature (in fact, non-documented for VB as far as I am aware) It involves a technique I figured out about 10 years ago after reading this article.

 

[HarleyQuinn]

Ah yes! I know what you mean.

I tried your technique four or so years ago after seeing it in a thread to do the ROT13 (as the article), I was fairly new to Regex so I think I left in favour of learning the more standard ways to use Regex (and how to correctly create patterns).

Having come back to it today (and after reading the article ), it quickly became clear what it's doing now

Still took me a few go's to get it though as I generally late bind my RegExp objects, and it doesn't like that.

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[PWD]

Over my head, guys.

Des.

 

[strongm]

>a thread to do the ROT13
You mean thread222-1085831

 

[HarleyQuinn]

That I do (should have posted the link myself, that was a bit lazy on my part ).


HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[strongm]

Start a new vb project

Current Assumptions
1) product code always appears in the INVC04 line
2) returns description for first matching product code. i.e assumes that product code is unique in db

It's just a quick example, but I think that it meets the requirements laid out in OP and utilisess ideas already suggested and/or implemented in this thread (lookup product description in DB, recordset filter, using the filesystem object, reading the whole file into a string which is then modified and saved back out again to the same or alternative file name)

It adds a couple of bonus features, but I'll let you figure them out

How to use it:

Add references to:
Microsoft ActiveX Data Objects Library 2.x
Microsoft Scripting Runtime
Microsoft VBScript Regular Expressions 5.5

Add a module
Copy and paste the following code:

[blue]Option Explicit
 
Public Sub Process(InputFile As String, Optional Outputfile As String)
    If Outputfile = "" Then Outputfile = InputFile
    With New FileSystemObject
        .OpenTextFile(Outputfile, ForWriting, True).Write Products(.OpenTextFile(InputFile, ForReading).ReadAll)
    End With
End Sub
 
Private Function Products(ByRef strText As String) As String
    Dim myReplacer As Replacer
    
    Set myReplacer = New Replacer
    With New RegExp
        .Global = True
        .MultiLine = True
        .Pattern = "([\s\S]*?INVC04\^)(.*?)(\^[\s\S]*?)(INVC05)(.*?)($)" ' we can tweak this if some of the assumptions are wrong
        Products = .Replace(strText, myReplacer) ' This is where the feature gets used...
    End With
 
End Function[/blue]

Add a new class module and rename it 'Replacer'
Paste the following code into it:

[blue]Option Explicit
 
Public Function vbReplacerFunction(ParamArray a()) As String
    vbReplacerFunction = a(1) & a(2) & a(3) & a(4) & GetFSCDesc(CStr(a(2))) & a(6)
End Function

' Support function
Private Function GetFSCDesc(PartNumber As String) As String
    Dim cn As New ADODB.Connection
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Replacer\db_example.mdb" '"<your connection string goes here>"
    With New Recordset
        .Open "vwFIBRE", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        .Filter = " Part = '" & PartNumber & "'"
        If .RecordCount > 0 Then GetFSCDesc = "^" & .Fields("FSC Desc").Value ' Ignore if  we can't find a matching description for any reason
    End With
    
End Function[/blue]

Make vbReplacerFunction the Default method of the class


And that's it. All the code you need. To run it just call

Process "<invoice file>"

which will update the named <invoice file> with the product details, or

Process "<invoice file>", "<output_invoicefile>"

which will produce a modified copy of the original <invoice file>

 

[HarleyQuinn]

Knowing roughly what was coming doesn't make it any less impressive, very nice strongm

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[PWD]

Like, "Wow" man. It's coming up to 4 p.m. & I'm on the early shift so I'll have a look tomorrow (when I'm on the late shift).

Des.

 

[strongm]

I should just note that it isn't fully optimised for speed as it stands. In particular we might want to look at the lifespans of both the database connection and the recordset. On refelection I think I'd go for a disconnected recordset that existed for the lifetime of the Process function.

 

[strongm]

So, had a chance to have a look?

 

[PWD]

I have tried, but your version is so cheese to the original's chalk - which uses 'Designers' to specify the location (DataLink) of another database against which we check to see which of our customers this invoice belongs to - that I'm more than floundering. It's not that I don't want a Packard instead of an Edsel; it's that I just plain don't understand this stuff well enough.

Des.

 

[strongm]

I won't post the slightly enhanced version just yet then ...

 

[PWD]

LOL.

 

[PWD]

Morning chaps. I was very lax in my 'testing' and it's just come back to bite me. I did get an error message

"Either BOF or EOF is True, or the current record has been deleted".

but I think I managed to work that out.

I tried putting

 If Not rs.EOF And Not rs.BOF Then

round the

 FSCDesc = rs.Fields("FSC Desc") 'FSC Description
            v(i + 1) = v(i + 1) & "^" & FSCDesc

but now it runs sooooooooo slowly – it takes 48 seconds to run though a file with 239 lines. There around 800 lines in the View. This is the complete segment of the code

 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"
        rs.Open "vwFIBRE", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        
    For i = 0 To UBound(v)
        If i = 0 Then '1st line contains our Customer ref number
        SplitLine = Split(v(i), "^")
        edipartner = SplitLine(1)
        End If
        
        If i = 6 Then ' "0331/02683112" 5th line of the file
        SplitLine = Split(v(i), "^")
        docnumber = SplitLine(6) 'Document number
        End If
        
        If v(i) Like "*^P0######^*" Then
        
        SplitLine = Split(v(i), "^")
        PartNumber = SplitLine(1)
        
        rs.Filter = " Part = '" & PartNumber & "'"
                'Part Number matches the 'P' number from the SQL View
            If Not rs.EOF And Not rs.BOF Then
            FSCDesc = rs.Fields("FSC Desc") 'FSC Description
            v(i + 1) = v(i + 1) & "^" & FSCDesc
            End If
        End If

    Next
    
rs.Close
Set rs = Nothing

It works just fine, I feel I must have done something to cause it to run so slowly. Any ideas?

Des.

 

[strongm]

Des,

Actually, I'm beginning to wonder why you can't use my code instead.

The block of your code in your last post presumably takes as input an array v() (given all the previous posts in this thread) containing all the lines that you have read from the file. Oh, and additionally you are pulling out the trading partner EDI identity number and the document number (and given that you get those from the invoice file you don't really need to identify them in the database lookup section) which are presumably the bits you need to look up the customer using the DataLink that you mentioned earlier.

On top of that, around that block you must have

a) a bit of code that identifies the file(s)
b) and opens the file(s)
c) reads it into v()

then, after the lookup and replacement block you must have some code that writes the modified v() back into a file (either replacing the original file, or creating a new one).

Now, my code basically does everything described above apart from actually identifying the file we want to work on.

You just need to drop your connection string ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=KerridgeExports;Data Source=BAMBI") into cn.Open in my GetFSCDesc function.

Assuming that yiou know the file to work in is called "c:\datafile1.txt"

You can then simply call

Process "c:\datafile1.txt"

which will achieve your original goal (rewriting the invoice file with the product descriptions now included), or

Process "c:\datafile1.txt", "c:\datafile2.txt"

which will write the modified version to a new file instead.

And to get the edipartner and docnumber needs only a teeny bit of extra code, something like the following:

[blue]    Dim edipartner As String
    Dim docnumber As String
    Dim temp() As String
    Dim strEdiFile As String
    
    strEdiFile = "c:\invoice1.txt"
    Process strEdiFile ' assumes that at this point we know the filename
    
    With New FileSystemObject
        ' Assuming each line in invoice file is maximum of 200 chars ... helps keep things quick
        temp = Split(.OpenTextFile(strEdiFile, ForReading).Read(1000), vbCrLf)
    End With
    edipartner = Split(temp(0), "^")(1)
    docnumber = Split(temp(4), "^")(6)
    Stop ' at this point  file has been updated and we know edi and doc...[/blue]

 

[PWD]

Yes. It all starts with

 strFoldername = "c:\mernet"
Set Folder = fso.GetFolder(strFoldername)
    Set Files = Folder.Files

    For Each File In Files
    
edifilename = File.Name

Then ‘j’ gets included in the mix

 For j = 1 To Chkfile.Ubound

Then the ‘v’ appears as part of

 ediFilenames(j) = strFoldername & "\" & edifilename
    f = FreeFile
Open ediFilenames(j) For Input As f
        v = Split(Input$(LOF(f), f), vbCrLf)
    Close f

Then we have the code I posted.

After which we have

    f = FreeFile
    Print #f, Join(v, vbCrLf);
    Close f

to write the file back.

So I’ll probably try to incorporate my bits into your code and see how it goes.

Des.