[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB6 - Read text file of orders and write to separate text files

Posted on 2007-07-31
6
Medium Priority
?
679 Views
Last Modified: 2012-06-27
I have a text file with multiple orders that I need to separate out into individual text files based on order number.

The text file consists of 3 segments:  
      1.   Header rows for all the orders
      2.   Detail lines for all the orders
        3.   Summary lines for all the orders

The number of detail lines will vary per order.  The number of orders will vary per text file.

Here is the structure of what the input order file would look like.

Order1Header
Order2Header
Order3Header
Order4Header
Order1DetailLine1
Order1DetailLine2
Order2DetailLine
Order3DetailLine1
Order3DetailLine2
Order3DetailLine3
Order4DetailLine
Order1SummaryLine
Order2SummaryLine
Order3SummaryLine
Order4SummaryLine

I need to parse out each order based on order number which will be in position 4 thru 10.
Base on the example above, the text file for order1 would look like this:
Order1Header
Order1DetailLine1
Order1DetailLine2
Order1SummaryLine

Im not sure of the best way to approach the logic.  Any help there would be a good start.  And/or sample code would be awesome.
0
Comment
Question by:Delta7428
  • 3
  • 3
6 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 2000 total points
ID: 19603056
Hi Delta,

What you'll want to do is read each line from your file separately, then loop through it looking at the order number for that line and if you haven't processed it yet (keeping the "used" ones in an array for easier looping) then looping from that line through the rest of the file looking for the order number, and outputting the line when found. I did write something for you, to give you a huge headstart into what you were looking for, and commented it to show you what each little subsection is doing so you should be easily able to make changes.  Normally I might have broken each part into smaller functions, but for the ease of showing you what it is doing I kept it in one subroutine. Of course don't hesitate to ask if you have any questions:
 
Sub DeltaOrderfile()
 Dim vFile As String, vFF As Long, FileCont() As String, Cnt As Long
 Dim vCurOrd As String, UsedOrders() As String, vOutFile As String, TempStr As String
 Dim i As Long, iUB As Long, j As Long
 
 vFile = "C:\orders\your entire order file.txt"
 vOutFile = "C:\orders\Order detail " 'prefix for output file names
 
 Cnt = 0 'array counter
 ReDim FileCont(0)
 vFF = FreeFile
 Open vFile For Input As #vFF
 Do Until EOF(vFF)
  'input lines in one at a time and add to array
  Line Input #vFF, TempStr
  If Len(Trim(TempStr)) > 0 Then 'only include non-blank lines
   ReDim Preserve FileCont(Cnt)
   FileCont(Cnt) = TempStr
   Cnt = Cnt + 1
  End If
 Loop 'until eof(vff)
 Close #vFF
 'FileCont now contains all your text file data
 
 iUB = Cnt - 1 'array upper bound
 Cnt = 0
 ReDim UsedOrders(0) 'array to store already-processed order numbers
 For i = 0 To iUB
  vCurOrd = Mid(FileCont(i), 4, 7) '"order number which will be in position 4 thru 10"
  For j = 0 To Cnt - 1 'loop through used orders
   If UsedOrders(j) = vCurOrd Then Exit For
  Next 'j
  If j = Cnt Then 'current order not processed yet
   'add order to usedorders array
   ReDim Preserve UsedOrders(Cnt)
   UsedOrders(Cnt) = vCurOrd
   Cnt = Cnt + 1
   'open output file for that order
   vFF = FreeFile
   Open vOutFile & vCurOrd & ".txt" For Output As #vFF
   'loop through remaining lines from array, when ord# matches put line into file
   For j = i To iUB
    If Mid(FileCont(j), 4, 7) = vCurOrd Then Print #vFF, FileCont(j)
   Next
   Close #vFF
  End If
 Next 'i
 MsgBox "Done! " & Cnt & " files created."
End Sub

Matt
0
 

Author Comment

by:Delta7428
ID: 19603737
Awesome!  It's very close to what I probably would have ended up with in a couple of days, minus a little sanity and hair!

The logic and the use of arrays is near exact to what I had conceived ... I thought there might be a better way, but this is perfect.  

I actually got goose bumps when I saw the files pop up in the folder.  Then I started shaking trying to open a   file to see if it wrote to them correctly.  Great!  Thank you!
0
 
LVL 35

Expert Comment

by:mvidas
ID: 19603806
Happy to help! There are a couple other ways this could be done; depending on the size of your text file you might want to do it differently.
With a big file, the fastest way would probably using regular expressions. The way I'd do that is
-Loop through lines in file to get unique list of order numbers (or if you had a pre-made list it would be much much faster)
-Load entire file into a single string variable, then use regular expression pattern matching to return all matches (using order number in the specific place in each line) of each order (by looping through unique list of order numbers)

But without knowing how big your files are or without getting too complicated (wasn't sure your level of knowledge) I figured the above was a good compromise.  I'd be happy to give you an example of using regular expressions (though you'd have to wait until tomorrow as I have plans this evening).

Another method might be, if you have to do this multiple times, to put this into a vbscript file and then send your "master" file to the .vbs file and have it do the parsing that way.

There really are a whole variety of different options you could take, really depends on your specific needs :)
Matt
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Delta7428
ID: 19603924
I honestly don't know how big the text files will be.  I was provided a small sample file from our EDI department to test with.  I suspect this will work fine for them.

I don't think a pre-made list or order numbers is feasible.  

What do you mean by regular expressions?

This will be a process that the EDI dept. will be running on a regular basis.  

I've not really delved into VBScript much.  That is something I've been planning on looking at.

I do appreciate you not getting too high level in your solution.  It wouldn't take much to lose me.

Thanks again!
0
 
LVL 35

Expert Comment

by:mvidas
ID: 19608339
"Regular Expressions" (RegExp) is an object that VB6 can use to pattern-match a string, it is quite fast and very powerful, though using it in your situation could be a little overkill (since you're always looking at positions 4-10).

To show you how you would do the same thing in VBScript, create a text file on your hard drive, and name it something with a .vbs extension.  Paste the following into it:


 Dim vFile, FileCont(), Cnt, vCurOrd, UsedOrders(), vOutFile, TempStr, i, iUB, j, FSO, TS, OrdNumStart, OrdNumLen
 
 OrdNumStart = 4
 OrdNumLen = 7
 
 If WScript.Arguments.Count = 0 Then
  MsgBox "You must send your text file to this script. Do so by click/dragging a text file 'into' this .vbs filea, or put this .vbs file into your SendTo directory and use the right-click context menu in explorer."
  WScript.Quit
 End If
 vFile = WScript.Arguments(0)
 vOutFile = Left(vFile, vInStrRev(vFile, "\")) & "Order detail " 'or hard code it like "C:\orders\Order detail "
 
 Cnt = 0 'array counter
 ReDim FileCont(0)
 Set FSO = CreateObject("scripting.filesystemobject")
 Set TS = FSO.OpenTextFile(vFile)
 Do Until TS.AtEndOfStream
  TempStr = TS.ReadLine
  If Len(Trim(TempStr)) > 0 Then 'only include non-blank lines
   ReDim Preserve FileCont(Cnt)
   FileCont(Cnt) = TempStr
   Cnt = Cnt + 1
  End If
 Loop
 TS.Close
 
 iUB = Cnt - 1 'array upper bound
 Cnt = 0
 ReDim UsedOrders(0) 'array to store already-processed order numbers
 For i = 0 To iUB
  vCurOrd = Mid(FileCont(i), OrdNumStart, OrdNumLen) '"order number which will be in position 4 thru 10"
  For j = 0 To Cnt - 1 'loop through used orders
   If UsedOrders(j) = vCurOrd Then Exit For
  Next 'j
  If j = Cnt Then 'current order not processed yet
   'add order to usedorders array
   ReDim Preserve UsedOrders(Cnt)
   UsedOrders(Cnt) = vCurOrd
   Cnt = Cnt + 1
   Set TS = FSO.CreateTextFile(vOutFile & vCurOrd & ".txt")
   For j = i To iUB
    If Mid(FileCont(j), OrdNumStart, OrdNumLen) = vCurOrd Then TS.WriteLine FileCont(j)
   Next
   TS.Close
  End If
 Next 'i
 Set vFile = Nothing
 Set vCurOrd = Nothing
 Set vOutFile = Nothing
 Set TempStr = Nothing
 Set i = Nothing
 Set iUB = Nothing
 Set j = Nothing
 Set TS = Nothing
 Set FSO = Nothing
 Set OrdNumStart = Nothing
 Set OrdNumLen = Nothing
 MsgBox "Done! " & Cnt & " files created."
 Set Cnt = Nothing

Function vInStrRev(ByVal vStringCheck, ByVal vStringMatch)
 Dim idx, iLen
 iLen = Len(vStringMatch)
 For idx = Len(vStringCheck) To 1 Step -1
  If Mid(vStringCheck, idx, iLen) = vStringMatch Then Exit For
 Next
 vInStrRev = idx
 Set idx = Nothing
 Set iLen = Nothing
End Function


Now to use it you have a couple options. One is to click/drag your orders text file into the vbs file in windows explorer. The other is to put it into your "C:\Documents and Settings\username\SendTo" folder and right-click your orders file, go to Send To, and select the .vbs file you created. I wrote it so the Split files will be put into the same directory as the main order file, but that can be changed easily in the code (the "vOutFile = ..." line). Just makes it a little more flexible, and especially so if the order file is put into a specific place every time the .vbs file can be put into windows scheduler for regular runs (assuming that specific place is hardcoded into the .vbs file).

Hope I didn't throw too much at you at once, just giving you another option :)

Matt
0
 

Author Comment

by:Delta7428
ID: 19612511
Thanks Matt.  I ran a test using the script and it works great.  There has been a 'slight' change to my file spec.  The order number will not always be in position 4-10.  Instead it will be based on field position which will be different on the header record and detail records.  I have already worked out the solution using your original script and sending the text to a function which returns the order number.

I wouldn't mind knowing how to use your vbscript in this new scenario.  I posted a new question with all the details with a link to this one.  So if you're up for more points, here it is.  

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_22735823.html

Thanks for all your help!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Introduction to Processes

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question