Posts by markc

    Re: output to txt file

    Hi Alan-

    Sorry to bother you again
    Have come across another problem with the archive file:

    CSV file saves as .txt contains order details from SQL server
    An order may contain 1 to 00 [google] lines [ I call this set a group]
    Each line needs to be written out

    If Grouping occurs again, it should be ignored [every time]
    Or deleted [one time]

    The file is not sorted, but needs to be
    Problem is, if I sort it, when I write out the order
    The order details will duplicate or more

    Current code stops writing when change in order number
    So when I reconcile the SQL detail file to dump file from
    accounting I have a variance

    Can anyone help me how to sort this file and either skip or delete
    duplicate groups.

    Sample file attached

    Re: Format row on change in A:A

    Sorry about that Ray -

    My example was poor

    The repitition pattern is every 6 rows (approx)
    123001 [no fill]
    123001 [no fill]
    123001 [no fill]
    123001 [no fill]
    123001 [no fill]
    123001 [this row is currently highlighted]
    123002 [this row should be highlighted]

    Of course, we want to write the formula to handle whatever we throw at it
    The repetion pattern may not always hold true


    Hi all -

    Using condi-format to format rows when change in unit number in ColA
    However, my formula is highlighting first instance. I would like the second

    Ex :

    A2 5890001 [currently highlight]
    A3 5890002 [this is the row I would like highlighted]

    Here's my formula in condi-format
    =IF($A2<>$A3, TRUE, FALSE)

    I tried flipping the 3 and 2. - no help


    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


    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

    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.


    The input file may contain replication of an order

    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.


    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.


    Re: output to txt file

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

    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


    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?


    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?


    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


    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)
    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