We help IT Professionals succeed at work.

Use Access to Manipulate a Text File

Jeffrey Coachman
on
219 Views
Last Modified: 2012-06-27
Hello to All!

Assume the following Data is in a plain text file:

audit 01/23/06
shipto05521780097             1   1  TARGET DC 0552  0082-6048446-0552
order 48446-05520552
pack  000072588200005879361780097
item  1    3    190028771
order 48446-05520552
pack  000072588200005879431780097
item  1    3    190028771
order 48446-05520552
pack  000072588200005879501780097
item  1    3    190028771
order 48446-05520552
pack  000072588200005879671780097
item  1    3    190028771
order 48446-05520552
pack  000072588200005879741780097
item  1    3    190028771
order 48446-05520552
pack  000072588200005879811780097
item  1    3    190028771
order 48446-05520552
pack  000072588200005879291780097
item  3    3    190028776
order 48446-05520552
pack  000072588200005879361780097
item  3    3    190028776
order 48446-05520552
pack  000072588200005879431780097
item  3    3    190028776
order 48446-05520552
pack  000072588200005879501780097
item  3    3    190028776
order 48446-05520552
pack  000072588200005879671780097
item  3    3    190028776
order 48446-05520552
pack  000072588200005879741780097
item  3    3    190028776
order 48446-05520552
pack  000072588200005879811780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005879981780097
item  1    3    190028771
invttl1
audit 01/23/06
shipto05531780097             1   1  TARGET DC 0553  0082-6048446-0553
order 48446-05530553
pack  000072588200005880011780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005880181780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005880251780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005880321780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005880491780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005880561780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005880631780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005880701780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005880871780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005880941780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005881001780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005881171780097
item  1    3    190028771
order 48446-05530553
pack  000072588200005879981780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005880011780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005880181780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005880251780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005880321780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005880491780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005880561780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005880631780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005880701780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005880871780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005880941780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005881001780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005881171780097
item  2    3    190028783
order 48446-05530553
pack  000072588200005879981780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005880011780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005880181780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005880251780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005880321780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005880491780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005880561780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005880631780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005880701780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005880871780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005880941780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005881001780097
item  3    3    190028776
order 48446-05530553
pack  000072588200005881171780097
item  3    3    190028776
order 48446-05540554
pack  000072588200005881241780097
item  1    3    190028771
invttl1


The "Main Sections" start at the "audit" line and end with "invttl1" line.
The "Sub-Sections" are the "Order", "Pack" and "Item" rows.

In my situation, the duplicate "sub-sections" need to be deleted, for example:

In the above example, the first “Main Section” contains a line (Line 4) that reads:
<pack  000072588200005879361780097>
This exact line is repeated again on line 25

So one of the repeated sub-sections must be deleted:
<order 48446-05520552>
<pack  000072588200005879361780097>
<item  1    3    190028771>

Make sense?

This is the Customer’s data and I must manipulate it to remove the dupes.
(I’m guessing it will require a function to loop and store the values for comparison?)

I’ll happily provide any more info that you may need.


Thanks

Jeff
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Author

Commented:
Thanks,

I'll try your suggestion tomorrow and let you know.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Author

Commented:
Thanks to you both!


I won't have time try this out this week.

But I will look at it over the weekend
(got sidetracked)

jeff
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Author

Commented:
mbizup,

can we leave this open for another week?

I am still "Sidetracked", I hopefully can get to it this week
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
No problem here.

/gustav
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
No problem... just please continue to provide timely feedback as a courtesy to the folks assisting you.

Thanks,

mbizup
EE Cleanup Volunteer
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Author

Commented:
Will do!

Thanks mbizup
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Author

Commented:
OK,

Sorry for the delay...

Anyway, I seem to be having trouble with the Code that creates the text file. I feel it would be better if I could fix the code so that output file was correct. Rather than manipulating the output file to make it correct.

Loops are not my specialty
:)

Here is the code. I creates the file that appears in my first post. If you go back to my first post the duplicated sub-sections need to be deleted.

To make matters worse, if you look at the last subsection in the first main section, you will see that it belongs in the next Main section. For example, in my above output text file notice the last sub setion:
order 48446-05530553
pack  000072588200005879981780097
item  1    3    190028771

Note the "Order" line ends with "553"

This last Subsection actually belongs in the Next Main section. (Where all the other "553"'s live.

So I guess My first concern is to get all the subsections together (All the 552's together, all the 553's together)*then* concentrate on removing theduplicated subsections.

Here is the entire module that creates the output text file (I can uderstand how it creates each subsection, but I can't figure out how to fix the counters so that the last sub section is included in the next main section)
If you need any more info, I will be happy to provide.

Thanks!
:)

Option Compare Database

Type EDIASN
ASN As Recordset
ASNt As Recordset
asnout As Recordset
line As Variant
control As Variant
line2 As Variant
po As Variant
po2 As Variant
pack As Variant
pack1 As Variant
pack2 As Variant
ord1 As Variant
ord2 As Variant
inv1 As Variant
inv2 As Variant
markfor1 As Variant
markfor2 As Variant
lastline As Variant


End Type
Public Function asnprocessing()
Set ASN = CurrentDb
Set ASNt = ASN!ASNheadert.openrecordset()
Set asnout = ASN!asnoutt.openrecordset()

'Why is this needed twice?
'Set ASN = CurrentDb
'Set ASNt = ASN!ASNheadert.openrecordset()
'Set asnout = ASN!asnoutt.openrecordset()

control = [ASNt]![cust-po#]
markfor1 = [ASNt]![shipto-#]
po2 = 1
ord2 = 1
ASNt.MoveFirst
Do Until ASNt.EOF
    po = po2
    ord1 = ord2
If control = ASNt![cust-po#] Then
    po2 = po + 1
    lastline = po2
Else
    control = [ASNt]![cust-po#]
    po2 = 1
End If
'groups by purchase order number  TESTED OK
If po <= 1 Then
    asnout.AddNew
        asnout!Field1 = "invttl1"
    asnout.Update
    asnout.AddNew
        asnout!Field1 = "audit " & Format([ASNt]![inv-date], "mm/dd/yy")
    asnout.Update
    asnout.AddNew
        asnout!Field1 = "shipto" & [ASNt]![shipto-#] & Format(ASNt!bol, "!@@@@@@@@@@@@@@@@@@@@") & Format(ASNt!Weight, "!@@@@") & Format(ASNt!Cartons, "!@@@") & Format(ASNt![sh-Name], "!@@@@@@@@@@@@@@@@") & [ASNt]![cust-po#]
    asnout.Update
End If
'groups by invoice (markfor store) TESTED OK
    asnout.AddNew
        asnout!Field1 = "order " & Format([ASNt]![cust-po#], "!@@@@@@@@@@") & [ASNt]![shipto-#]
    asnout.Update
    asnout.AddNew
        asnout!Field1 = "pack  " & "0000725882" & ASNt!UCC & ASNt!bol
    asnout.Update
    asnout.AddNew
        asnout!Field1 = "item  " & Format([ASNt]![line-#], "!@@@") & Format([ASNt]![qty-shipped], "@@@") & "   " & Format([ASNt]![part-num], "@@@@@@@@@@")
    asnout.Update
ASNt.MoveNext
Loop
    asnout.AddNew
    asnout!Field1 = "invttl1"
    asnout.Update
asnout.MoveFirst
asnout.Delete
End Function
 
Public Function asnpreprocess()
Set ASN = CurrentDb
Set ASNt = ASN!ASNheadert.openrecordset()
Set asnout = ASN!asnoutt.openrecordset()

If ASNt![cust-po#] = "nopo" Then
    DoCmd.Quit
End If

End Function
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.