output to txt file

markc · Dec 10th 2004

Hi!

At end is some code a friend of mine helped me with
He is on vacation and unavailable

What I'm trying to do
1. List of orders is in Excel Order_Nmbrs2! begin at A2
2. Open Order_Archive.txt
(Note: a csv file output from SQL server e-mail'd to me weekly
I append each new file to the archive)
3. Open Output-orders.txt
4. If order number read from xl exists in archive, write to output file
Continue until blank in ColA
5. An order may or may not contain multiple lines
Sample of archive (I import first 9 chars from each line into xl)
"Status"
"O",5997
"O",5997
"O",5997
"O",5997
"O",5997
"O",5997
"O",5997
I read this into xl (Continues for about 6000 rows today)
I then use some text formulas and some macros to clean
up and remove any duplicate items

6. An order # sequence may occur multiple times in the archive file
Each line shown above for order 5997 should be written out to
Output-orders.txt However, if encountered in the archive a second
time, should be skipped

7. Maybe the problem is that after I import from the archive to get
the first9 chars from each line, I strip and clean so I end up with
a 4 digit value I then use the value as the comparison against the archive

At the moment,nothing is being written out to Output-Orders.txt
The file is created, but it is empty.
Appreciate any help

Code

Sub Write_Orders()
Dim datafile$
Dim a1 As String
Dim sRow As Long
Dim outputFile$


datafile$ = "C:\bug_fv\bug_fv-f.txt"
outputFile$ = "C:\bug_fv\output-orders.txt"


On Error GoTo DiskError


Open datafile$ For Input As #1
Open outputFile$ For Output As #2


sRow = 1
Worksheets("Order_Nmbrs2").Activate
With Range("A1")
        Do While Not EOF(1)
                Line Input #1, a1
                x = Left(a1, 4)
                If (x = .Offset(sRow, 0).Value) Then
                        Print #2, a1
                        Do While ((x = Left(a1, 4)) And Not EOF(1))
                                Line Input #1, a1
                                Print #2, a1
                        Loop
                        sRow = sRow + 1
                End If
        Loop
End With
Close #1
Close #2
Exit Sub


DiskError:
a = MsgBox("Disk Error." & Err.Number, vbExclamation)


End Sub

Display More

Thanks
marc

A9192Shark · Dec 10th 2004

Re: output to txt file

Marc,

I think the problem is that unless the xl file and the datafile have the same data in them you are trying to pass thrugh both at the same time, possibly missing a comparison that is required.

The problem is that because you want a list output that

Quote

4. If order number read from xl exists in archive, write to output file
Continue until blank in ColA

you need to either sequentially pass through the xl file and keep going through the text file for each cell in xl or
pass through the text file once, but use a search in excel and set a flag, then output those without a flag.

I presume that the text file is too long to import to xl? (say >20 000 rows?)

Can we set a flag in, say column B of the Order_Nmbrs2 sheet?

If so then we can propose a solution.

Thanks,

Alan.
PS
Can you post an example of your worksheet and a sample from the input file as attachments for us to test on?
A.

markc · Dec 10th 2004

Re: output to txt file

Thanks Alan.

Answers to some of your questions/observations :

1.) File may not be too long today. But will grow to be so in time
2.) Flag in Col B of Order_Nmbrs2! sounds like a possiblity
I'll leave that to you geniuses since I am but a rookie.

I tried uploading the files, forum responded too big
Send me e-mail address and I will pass them on.

Thanks much
marc

markc · Dec 13th 2004

Re: output to txt file

Hi!

I trimmed my files way down to bare essentials and was able to upload
Could ya'll kindly take a look and tell me where I went wrong?

I think Alan makes a good suggestion (above)
Also, maybe I'm not searching far enough into the string?

TIA
/marc

A9192Shark · Dec 13th 2004

Re: output to txt file

Marc,

Here is some code that should do what you want. It is not necessarily the optimal code since I do not know how many records there are in Excel or the archive file.

Let me know if you need a hand using it.

Alan.

Code

Option Explicit




Private rngXLOrders         As Range    'Range cobvering all order numbers in XL
Private rngOrderFound       As Range    'Range returned from the find function
Private rng                 As Range    'Temporary range object if required


'Change these to reflect your files
Private Const _
        strArchiveFilename  As String = "D:\Answers\txt files\Archive.txt", _
        strOutputFilename   As String = "D:\Answers\txt files\Output.txt"


Private intFileIn           As Integer  'File number for input file
Private intFileOut          As Integer  'File number for output file
Private strIn               As String   'String read from input file and output if reqd


'The following long numbers are used to track the order number and the number of
'orders read and the number of line read.
Private lngOrder            As Long, _
        lngLastOrder        As Long, _
        lngOrdersFound      As Long, _
        lngOrdersNotFound   As Long, _
        lngLinesRead        As Long


Public Sub CheckArchive()
    
    'Set range of order numbers
    Set rngXLOrders = Range("A2", Range("A65536").End(xlUp))
    
    On Error GoTo DiskError
    'Open input and output files
    intFileIn = FreeFile()
    Open strArchiveFilename For Input As #intFileIn
    
    intFileOut = FreeFile()
    Open strOutputFilename For Output As #intFileOut
    
    'Initialise some counters
    lngLastOrder = -1
    lngOrder = -1
    lngLinesRead = 0
    lngOrdersFound = 0
    lngOrdersNotFound = 0
    
    'Start a loop to read the file until the end
    Do While Not EOF(intFileIn)
        'start another loop to avoid repeated records in the archive
        Do
            lngLastOrder = lngOrder
            Line Input #intFileIn, strIn
            lngLinesRead = lngLinesRead + 1
            lngOrder = Val(Left(strIn, 4))
        Loop While (Not EOF(intFileIn)) And (lngOrder = lngLastOrder)
        
        'With each new order number found search the xl orders
        Set rngOrderFound = rngXLOrders.Find(lngOrder, , , xlWhole, xlByRows)
        
        If (rngOrderFound Is Nothing) Then
            'The archived order number is not in the excel file
            lngOrdersNotFound = lngOrdersNotFound + 1
          Else
            'The archived order number is in the excel file so output the order
            lngOrdersFound = lngOrdersFound + 1
            Print #intFileOut, strIn
        End If
    Loop
    
    'Close files
    Close #intFileIn
    Close #intFileOut
    
    MsgBox Format(lngLinesRead, "#,##0") & " Lines read" & vbNewLine & _
           Format(lngOrdersFound, "#,##0") & " Orders found" & vbNewLine & _
           Format(lngOrdersNotFound, "#,##0") & " Orders not found", vbOKOnly
    
    On Error GoTo 0
    
    Exit Sub
    
DiskError:
    MsgBox Err.Description, vbCritical + vbOKOnly, "Error in accessing files"
    
End Sub

Display More

markc · Dec 13th 2004

Re: output to txt file

Thanks Alan -

Pasted into new module in VBE
Updated for proper file paths and names
Ran it.

Output file is created, but empty.
Msg :
7,131 lines read
0 orders found
126 orders not found

I checked the archive file to make sure the order exists, it does.

I don't see in your code where you reference the sheet to
get the list of orders from, or did I just mis-read?

TIA
/marc

A9192Shark · Dec 13th 2004

Re: output to txt file

Marc,

The first line of code defines a range. However, you are right it does not pick the sheet, infact it would use the active sheet. Change the statement to:

Code

Set rngXLOrders = Worksheets("Order_Nmbrs2").Range("A2", Range("A65536").End(xlUp))

HTH,

Alan.

markc · Dec 13th 2004

Re: output to txt file

Thanks Alan -

Did not work
I checked the archive file
Final line is 12372 (Notepad)

The msgbox reports 7131 lines read
The order number I am testing for first appears
line #10752

The archive file is created by me running a little batch file
to append each week's text file to the archive file.

I opened the archive file with Textpad
It too shows 7131 lines

Why this 7131 line limit?

TIA
/marc

A9192Shark · Dec 14th 2004

Re: output to txt file

Marc,

What do lines 7130, 7131 and 7132 look like?

Have you inserted a break point and tested for lines read so that it breaks at, say, 7130 and then stepped through. Why is it exiting the do loop? Try using the wach and breakpoints to identify which condition is being met to exit the do loop.

Alan.
PS Can you zip the archive and excel files to less than 45kb and post it here?
A.

markc · Dec 15th 2004

Re: output to txt file

Hi Alan -
I replied on Tues, but I don't see it here - weird.

Anyway,
I don't know how to insert the breakpoint and step
through the code. I'll check help or google it - see if I can figure
it out.

Files are too big to post here, even zip'd
Archive zip'd is 423 KB

Laptop had meltdown
Last good archive backup a little different
But teaches something I think

Msgbox now reports 6,694 lines read
When I open with Textpad I get same
However, Notepad returns 11,902 lines

I copied several lines before and after 6694
Pasted into attached file
I added the line numbers

Thanks
/marc

A9192Shark · Dec 16th 2004

Re: output to txt file

Marc,

I too thought I posted something the other day, but might have been another thread....

Has this data come from unix? It sounds to me like there are some unusual characters in the file. It is odd that different editors will open up the file to different points. This suggests that one is seeing an end of file marker and the other is not.

I assume that the reason it is now reading more lines is because you have changed file after your 'meltdown'

To use watches and breakpoints check the help in the VBEditor. They are very useful, you should be able to find out why it is stopping.

Also learning to use the F8 button to step through code is very useful, this will allow you to track variables easily. Once you have your breakpoint/watch inserted and the code halts you can hover with the mouse over a variable and it will show you the value of that variable.

Once you know which line is causing the problem you could always delete that line, see if there are others.

Also you could cut out say 20 lines before and after the problem line and post that here as a text file. The best way to do this to presrve the file structure and problem is:
Copy the file
Open in a text editor
Delete the lines before the ones you want to send
Delete the lines after the ones you want to send.
Save the file with 4 or so lines in it.

Test the file through your programme, does it still fail?

Post the latest code and text file and I will have a look.

Unix?????

HTH,

Alan.

markc · Dec 23rd 2004

Re: output to txt file

Hi Alan -

Apolgies for the long delay
The folks that send me the file weekly tell me it is SQL running on Windows 2003.

I tried a variety of order numbers that I know exist in a truncated version
of the input file. Every time I receive the same Msgbox :
13 lines read
0 Orders found
2 orders not found

I'm working on the watch/breakpoint right now and will comeback a little later

A last thought on the line count difference

Maybe Notepad sees true lines where Textpad and Excel treat a wrapped line as 1 line. Not sure, but I think it may be a reasonable assumption.

Thanks
-marc

Code

Option Explicit
 
 
Private rngXLOrders         As Range 'Range cobvering all order numbers in XL
Private rngOrderFound       As Range 'Range returned from the find function
Private rng                 As Range 'Temporary range object if required
 
 'Change these to reflect your files
Private Const _
strArchiveFilename  As String = "C:\Bugs_fv\archive.txt", _
strOutputFilename   As String = "C:\Bugs_fv\output-orders.txt"
 
Private intFileIn           As Integer 'File number for input file
Private intFileOut          As Integer 'File number for output file
Private strIn               As String 'String read from input file and output if reqd
 
 'The following long numbers are used to track the order number and the number of
 'orders read and the number of line read.
Private lngOrder            As Long, _
lngLastOrder        As Long, _
lngOrdersFound      As Long, _
lngOrdersNotFound   As Long, _	
lngLinesRead        As Long
 
Public Sub CheckArchive()
     
     'Set range of order numbers
    Set rngXLOrders = Worksheets("Order_Nmbrs2").Range("A2", Range("A65536").End(xlUp))
     
    On Error GoTo DiskError
     'Open input and output files
    intFileIn = FreeFile()
    Open strArchiveFilename For Input As #intFileIn
     
    intFileOut = FreeFile()
    Open strOutputFilename For Output As #intFileOut
     
     'Initialise some counters
    lngLastOrder = -1
    lngOrder = -1
    lngLinesRead = 0
    lngOrdersFound = 0
    lngOrdersNotFound = 0
     
     'Start a loop to read the file until the end
    Do While Not EOF(intFileIn)
         'start another loop to avoid repeated records in the archive
        Do
            lngLastOrder = lngOrder
            Line Input #intFileIn, strIn
            lngLinesRead = lngLinesRead + 1
            lngOrder = Val(Left(strIn, 4))
        Loop While (Not EOF(intFileIn)) And (lngOrder = lngLastOrder)
         
         'With each new order number found search the xl orders
        Set rngOrderFound = rngXLOrders.Find(lngOrder, , , xlWhole, xlByRows)
         
        If (rngOrderFound Is Nothing) Then
             'The archived order number is not in the excel file
            lngOrdersNotFound = lngOrdersNotFound + 1
        Else
             'The archived order number is in the excel file so output the order
            lngOrdersFound = lngOrdersFound + 1
            Print #intFileOut, strIn
        End If
    Loop
     
     'Close files
    Close #intFileIn
    Close #intFileOut
     
    MsgBox Format(lngLinesRead, "#,##0") & " Lines read" & vbNewLine & _
    Format(lngOrdersFound, "#,##0") & " Orders found" & vbNewLine & _
    Format(lngOrdersNotFound, "#,##0") & " Orders not found", vbOKOnly
     
    On Error GoTo 0
     
    Exit Sub
     
DiskError:
    MsgBox Err.Description, vbCritical + vbOKOnly, "Error in accessing files"
     
End Sub

Display More

A9192Shark · Dec 23rd 2004

Re: output to txt file

MarkC,

The problem was in the middle Do loop. I did not use the correct characters to get the order number. The key lines are in bold. Once you have run the code and it works you can remove the Debug.Print line in italics.

Substitute the following code and it should work:

Code

Do
            lngLastOrder = lngOrder
            Line Input #intFileIn, strIn
            [I]Debug.Print Len(strIn), Left(strIn,20)[/I]
            [B]strIn = Trim(strIn)[/B]
            lngLinesRead = lngLinesRead + 1
            [B]strIn = Mid(strIn, 5, 4)[/B]
            [B]lngOrder = Val(strIn)[/B]
        Loop While (Not EOF(intFileIn)) And (lngOrder = lngLastOrder)

HTH,

Alan.

markc · Dec 23rd 2004

Re: output to txt file

Alan -

Thanks so much for your help.
The change to the inner Do-Loop was successful

However, only the number 6256 (my test) was written out to the
Output-Orders file (written 2x since appears in the input file 2x)

I need to write out the entire record 1 time only as the record may appear
again later in the input file. Hopefully Excel will handle line wrapping and continue to write until the end of a particular record.

Thanks!
-marc

A9192Shark · Dec 23rd 2004

Re: output to txt file

Marc,
Are all of the lines with the same order number the same?

Do you want the first or the last occurrence written out?

How many separate groups of occurrences of an order number might there be?

A.

markc · Dec 24th 2004

Re: output to txt file

Alan -

Each grouping of order numbers contain details about a specific order
The header information seemsto replicate, but further into down each line
there are specifie details about coffee, pastries, whatever each customer orders

The possibility multiple occurrences of an order is a safeguard.
I receive the order file via email.
I dump invoice detail from our financial database, scrub the data, and compare it to the order file.

Before I send the the files on for batch processing and loading to an SQL server I need to make sure there is eactly 1 matching order record for each invoice record.

First/Last -
I was thinking first, guess it does not matter, unless there are any speed factors involved

Groups of occurrences -
Unknown. I could have erred and appended the file multiple times.The people that send me the order file could have erred and done who knows what. That is why fault tolerance is the best handler for now and the future.

Thanks
-marc

The input file may contain replication of an order

A9192Shark · Dec 24th 2004

Re: output to txt file

Marc,

I think this code will do what you want. I have changed the code for the string that is output, it now outputs the first line of the occurrence of an order number. The code also changes the flag to "Exported" when an order number has been exported (therefore all order numbers n the Excel file without Exported flag were not in the archive).

Code

Option Explicit
 
 
Private rngXLOrders         As Range 'Range cobvering all order numbers in XL
Private rngOrderFound       As Range 'Range returned from the find function
Private rng                 As Range 'Temporary range object if required
 
 'Change these to reflect your files
Private Const _
strArchiveFilename  As String = "D:\Answers\27325\archive.txt", _
strOutputFilename   As String = "D:\Answers\27325\output-orders.txt"
 
Private intFileIn           As Integer 'File number for input file
Private intFileOut          As Integer 'File number for output file
Private strIn               As String  'String read from input file and output if reqd
Private strOut              As String  'String that will be written out
 
 'The following long numbers are used to track the order number and the number of
 'orders read and the number of line read.
Private lngOrder            As Long, _
lngLastOrder        As Long, _
lngOrdersFound      As Long, _
lngOrdersNotFound   As Long, _
lngLinesRead        As Long
 
Public Sub CheckArchive()
     
     'Set range of order numbers
    Set rngXLOrders = Worksheets("Order_Nmbrs2").Range("A2", Range("A65536").End(xlUp))
    Worksheets("Order_Nmbrs2").Range(Range("B2"), _
                                     Range("B2").Offset(rngXLOrders.Rows.Count - 1)).ClearContents
    On Error GoTo DiskError
     'Open input and output files
    intFileIn = FreeFile()
    Open strArchiveFilename For Input As #intFileIn
     
    intFileOut = FreeFile()
    Open strOutputFilename For Output As #intFileOut
     
     'Initialise some counters
    lngLastOrder = -1
    lngOrder = -1
    lngLinesRead = 0
    lngOrdersFound = 0
    lngOrdersNotFound = 0
         
    'Read the header line
    If Not EOF(intFileIn) Then Line Input #intFileIn, strIn
     'Start a loop to read the file until the end
    Do While Not EOF(intFileIn)
         'start another loop to avoid repeated records in the archive
        Do
            lngLastOrder = lngOrder
            Line Input #intFileIn, strIn
            If strOut = "" Then strOut = strIn
            lngLinesRead = lngLinesRead + 1
            lngOrder = Val(Mid(Trim(strIn), 5, 4))
            If lngOrder <> lngLastOrder Then strOut = strIn
        Loop While (Not EOF(intFileIn)) And (lngOrder = lngLastOrder)
         
         'With each new order number found search the xl orders
        Set rngOrderFound = rngXLOrders.Find(lngOrder, , , xlWhole, xlByRows)
         
        If (rngOrderFound Is Nothing) Then
             'The archived order number is not in the excel file
            lngOrdersNotFound = lngOrdersNotFound + 1
          Else
             'The archived order number is in the excel file
             'Check to see if it has been exported, if not doit, if it has move on!
             If rngOrderFound.Offset(0, 1) <> "Exported" Then
                lngOrdersFound = lngOrdersFound + 1
                Print #intFileOut, strIn
                rngOrderFound.Offset(0, 1) = "Exported"
             End If
        End If
    Loop
     
     'Close files
    Close #intFileIn
    Close #intFileOut
     
    MsgBox Format(lngLinesRead, "#,##0") & " Lines read" & vbNewLine & _
    Format(lngOrdersFound, "#,##0") & " Orders found" & vbNewLine & _
    Format(lngOrdersNotFound, "#,##0") & " Orders not found", vbOKOnly
     
    On Error GoTo 0
     
    Exit Sub
     
DiskError:
    MsgBox Err.Description, vbCritical + vbOKOnly, "Error in accessing files"
     
End Sub

Display More

I do hope that this works and you can go home a happy man for Christmas. I have just made a part of code work at the end of two weeks struggle and I am happy:) I will not think of it over Christmas!

Alan.

markc · Dec 24th 2004

Re: output to txt file

Thanks Alan -

Getting so close - I'm ready to start the party
I changed my test to order number 5997
Textpad shows 7 distinct lines - no wrapping
14 lines in Notepad with wrapping

Now outputing the first line of the order, but not subsequent lines
5997 (see archive file) in this instance should output 7 records, but if the grouping should occur later - ignore the second grouping.

As for the Christmas holiday - I've been away from the office since last Thurs
Don't go back 'til 01/03/05. We have to use 15 days of vacation/year or lose them.

Was handling everything from my home workstation, now back on laptop.
Hope you and yours have a great holiday!

BTW -
Added "MyTest" (w/o "") to end of line 1 output and opened wth Textpad
Does not wrap. Final "t" at space 1158

Thanks agains for all of the help
-marc

A9192Shark · Jan 4th 2005

Re: output to txt file

Marc,

Are you saying this is completed or that there is still something to do?

I can't quite make out if you actually want all the lines in the group with the first occurrence of the order number to be exported but subsequent groups to be ignored.

A.

markc · Jan 5th 2005

Re: output to txt file

Alan -
Yes, not quite there.
In the example of 5997, 7 lines should be output
At the moment, only 1 line is output

Thanks
-marc

output to txt file

Participate now!

Share