Beckwith Electronics
Hamburg, Illinois 62045 (USA)
(618) 232-1139
(618) 232-1172 fax

Mixed Mode Data Using Delimited Strings

This is offered for Educational Purposes only.
History

Back in the early '80's, when microprocessors were first starting to be used in home computers, I purchased a S100 bus system from California Computer systems. It had a Z80 processor running at 4 Mhz and two 8" floppy disk drives. I also purchased a Macro Assembler and a CBasic program to run under CPM. I started writing an Inventory Control program with the CBasic prior to starting Beckwith Computers & Electronics. The Basic was called Compiled Basic. It was still interpreted but created a compressed file of tokens. It was not overly fast, but adequate. The only other option was to purchase a DEC Mini Computer and Inventory Software costing in the thousands of dollars.

After running the programs for several years, I was able to get CB80, a truly compiled basic that created object code and used the existing CBasic code. It was 60 times faster than the interpreted version. Years later, when I purchased my first PC, a 486, it came with Quick Basic, another interpreted basic running under DOS. I had several disk file issues with the Quick Basic in the way they stored data and read it back from the disk. It was not always read back as it was written. It turned out that when Microsoft converted QB45 to the Quick Basic Interpreter, there were several errors in the conversion. I found this out when I got the original QB45 compiler. It was a true compiler that generated object code. All the disk file issues magically went away. I am still running the QB45 on the work stations but I am slowly converting them over to Visual Basic.

Since, in the beginning, I didn't know how long to make each item, I decided not to use fixed length fields. If you allocate a length of 20 characters for a part number, you will soon find one that is 21 characters long and have to change the field length. By allowing variable length fields and setting the maximum length of a record, you can adjust the least important field, usually the description, to fit in the available space allocated when necessary. That is what I decided to do. Originally I used real numbers for prices and wrote them along with the strings to the disk. Eventually there were problems. Each time I changed Basic programs I had to reformat the entire Inventory file. This got old, even though I wrote a program each time to convert the file.

After years of having issues with storing the data on the disk and making it compatible with different versions of Basic, I seem to have finally found the most universal way of storing and retrieving the Inventory Data. This method, which seems the most compatible with various programs, is to use a delimited string. Over several generations of program revisions, using commas, quotes and various other characters for delimiters, each time finding problems with them sneaking into the strings, I finally decided to search for a character that couldn't be entered from the Keyboard. I have found that the character 127, depicted in this text as(), is the best choice. It is the Delete character. All the programs that I am aware of process this key from the keyboard as "Delete". Therefore, it is never added to the text, but removes the previous character. This keeps the strings from getting corrupted by the keyboard entry data.

File Structure

The format for each string that we use is as follows:

Inventory Number, Quantity, Minimum Quantity, On Order, Type, Part Number, Package, Cost, Price 1, Price Break 1, Price 2, Price Break 2, Price 3, Price Break 3, Price 4, Manufacturer, Description, Location, Weight, Image and some other options. We continue adding additional items as needed.

This was the original way each record was set up. I always keep the Inventory Number in the string for identification purposes. In the beginning, I had a bad disk controller and records were sometimes swapped. This also ensures that the record gets put back in the same place each time by reading the Inventory Number from the string before restoring it. Very important!

A typical record then looks like this:

39402250SQUARESTIX40-901-50EACH0.7351.4705001.29210001.17625001.029SMITH40 (1X40) SGL ROW HDR TIN 90 DEGA77A.01

Inventory Number = 3940
Quantity = 2
Minimum Quantity = 250
On Order = 0
Type = Squarestix
Part Number = 40-901-50
Package = Each
Cost = 0.735
Price 1 = 1.47
Price Break 1 = 500
Price 2 = 1.292
Price Break 2 = 1000
Price 3 = 1.176
Price Break 3 = 2500
Price 4 = 1.029
Manufacturer = Smith
Description = 40 (1X40) SGL ROW HDR TIN 90 DEG
Location = A77A
Weight = .01

The length of this String is 130 characters long. The Record Size was set at 256, since Strings require an extra 2 characters to hold the length, the maximum allowable length is 254 characters, which should be checked before trying to save the String. You could set the length at 384, 512 or whatever is needed. Always use an increment of 128, because of the Sector Size on the disk. This allows for faster Disk Access. Originally the Sector Size on a Disk was set for 128 bytes. Now they use a multiple of this for the larger disks, but the process is the same. In Visual Basic, the fixed length string is limited to aproximately 65536 because of the 2 bytes that hold the string length.

In creating disk files, a Fixed Length Record Oriented File allows Random Access. Since we use different manufacturers data, we convert their files to our string data for common processing. We also use different Record Sizes. If you set the First Record up, in the file, with the File Data, then you can determine the Record Size and various parameters by simply reading the First String in the file. We assume that the First Record length is less than 128 bytes. But that isn't important. Just so you read the entire First String. For the first example, we are predefining the Inventory Variables and setting their parameters in the routine. In the second example, this routine can be made universal by passing a File Name to it and returning the parameters in a String. I use the Val( ) function, and haven't had any problems with it, but you could also use the C???( ) functions.

Record Length, First Record Number, Last Record Number, First Inventory Number

256294421

Record Length = 256
First Record Number = 2
Last Record Number = 9442
First Inventory Number = 1

You can add other parameters here for your special needs.

Note: In my style of programming, I usually use the identifiers $&#!%@ for temporary variables and for illustration purposes.

Dim InventoryRecordLength as integer, FirstInventoryRecordNumber as integer, LastInventoryRecordNumber as long
Dim FirstInventoryNumber as integer, SourceFile as string, InventoryFile as integer

Public Sub OpenFile ( )

 InventoryFile = FreeFile (1)
100Open SourceFile For Random Shared As #InventoryFile Len = 128
    Get #InventoryFile, 1, a$
Close InventoryFile
110InventoryRecordLength = Val (GetItem (a$, 1))
FirstInventoryRecordNumber = Val (GetItem (a$, 2))
LastInventoryRecordNumber = Val (GetItem (a$, 3))
FirstInventoryNumber = Val (GetItem (a$, 4))
120Open SourceFile For Random Shared As #InventoryFile Len = InventoryRecordLength

End Sub

Public Function GetItem (ByVal a$, ByVal n%) As String

'Delimiter=chr$ (127)
'a$ is the entire string
'n% is the item requested
's%=start
'x%=match position

 GetItem = ""
If a$ = "" or n% = 0 Then Exit Function
'Default, null data returned
 x% = 0

100For i% = 1 To n%
    s% = x% + 1
    x% = InStr (s%, a$, Delimiter)
Next i%

If x%> 0 Then GetItem = Mid$ (a$, s%, x% - s%)

End Function

Function OpenRecordFile (F$) As String

 File% = FreeFile (1)

100Open F$ For Random Shared As #File% Len = 128
    Get #File%, 1, a$
Close File%

OpenRecordFile = a$

End Function

There you have it. Whatever the Record Length is of the file and the other data parameters. Just read in the First Record and decode it.
This may appear a slow way of obtaining data, but it is not. It's ease of use far out weighs any loss of speed.

I set up a function to calculate the Last Inventory Number, which could change at any time due to another work station entering a new record. You will have to lock the First Record, where the file parameters are kept, before the new record is added. Unlocking it allows another station to also add a new record.

Public Function LastInventoryNumber ( ) as long

 LastInventoryNumber = LastInventoryRecordNumber + FirstInventoryNumber - FirstInventoryRecordNumber

End Function

Public Function LockReadInventoryRecordNumber (ByVal Rn as long) As String

  On Error GoTo 1000

 LockReadInventoryRecordNumber = "" 'by interrogating the returned value, you can determine if the record was read or not

100 Lock InventoryFile, Rn
LockReadInventoryRecordNumber = GetInventoryRecord (Rn)
Exit Function

1000result = MsgBox ("Record Locked # " & Rn, vbOKCancel)
If result = vbOK Then Resume 100
Resume 9000

9000
End Function

Public Function GetInventoryRecord (ByVal RecordNumber as long) As String

 GetInventoryRecord = GetRecord (InventoryFile, RecordNumber)

'It assumed that "InventoryFile" is already defined

End Function

Public Function GetRecord (ByVal FileNumber%, ByVal RecordNumber as long) As String

 GetRecord = ""
Get #FileNumber%, RecordNumber, a$
GetRecord = a$

End Function

I have removed most of the Error Handling for the sake of clarity. This is why there are multiple functions in these examples. Below is the function used to calculate the Record Number, since the First Inventory Number may be set to something different other than 1. Just pass the Inventory Number to it to get the the Record Number. This may seem confusing, but there times when you need to access Data eithor by Inventory Number or Record Number. Our search functions sometimes go from the First Record Number to the Last Record Number rather than by Inventory Number. Other times you request an Inventory Number from the keyboard or scanner for processing.

Function InventoryRecordNumber (ByVal InventoryNumber as long) As Long

  InventoryRecordNumber = InventoryNumber - FirstInventoryNumber + FirstInventoryRecordNumber

End Function

We used to read and decode the entire record and then rewrite it if anything changed. There are times when I decode the entire string and display it, but rarely. When this is done, you can assign the data to an array and access it by the subscript.

Now consider this, by only replacing only the Data Item in the String that changed, you can also have a Varying Number of Items in each string. This will allow for adding new Items and not having to convert the entire Inventory File. Remember the GetItem will return a Null if there aren't enough Items in the String. So, here is the replacement function.

Function ReplaceItem (ByVal a$, ByVal b$, ByVal i%) As String

'a$ is the entire string
'b$ is the replacement string
'i% is the Item number to be replaced or added
's%=start
'x%=match position

 ReplaceItem = a$'always set up to return the same data
 if b$="" or i%=0 then exit function 'here, you could not allow specific Items to change
 x% = 0

For j% = 1 To i%
    s% = x%
    x% = InStr (s% + 1, a$, Delimiter)
    If x% = 0 Then
        a$ = a$ + Delimiter
        x% = Len (a$)
    End If
Next j%

ReplaceItem = Left$ (a$, s%) & b$ & Mid$ (a$, x%)

End Function

If you have only 10 Items in your string, and you request the replacement of Item 20, the above function will add the missing Delimiters to include Item 20.

Here are some additional routines to Read or Write the Data to/from the Inventory File.

Public Sub WriteUnlockInventoryNumber (ByVal InventoryNumber as long, ByVal a$)

 On Error GoTo 1000

rn& = InventoryRecordNumber (InventoryNumber)
WriteInventoryRecord rn&, a$

100Unlock #InventoryFile, rn&
Exit Sub

1000If Erl = 100 Then Resume Next

End Sub

Public Sub WriteInventoryRecord (ByVal RecordNumber as long, ByVal a$)

 Put #InventoryFile, RecordNumber, a$
'this is where we log the data to a log file indicating the Function/Program that changed the data

End Sub

Public Function LockReadInventoryNumber (ByVal InventoryNumber as long) As String

 On Error GoTo 1000

100rn& = InventoryRecordNumber (InventoryNumber)
Lock InventoryFile, rn&
LockReadInventoryNumber = GetInventoryRecord (rn&)
Exit Function

1000result = MsgBox ("Record Locked Inventory # " & InventoryNumber , vbOKCancel)
If result = vbOK Then Resume 100
Resume 9000

9000
End Function

Now that we have discussed the File Structure and the File Routines, consider the possibility of mixing Data Types. For years we struggled with Mixed Mode Data. We would first have to check to see how many more lines to read and hope that there wasn't an error in reading the previous Data. If there was an error, then everything was out of sync. Here is an example of our Purchase Order file, which is just a file of Varying String Data without a Sequential File Structure (non Record Oriented). Also, the order is not important for the Heading Data.

Po:40357
Date:07/27/2011
To:Smith Company123 N. 1st StreetSmall Town, USAfax 555-123-4567
Ship To:Beckwith ElectronicsRR1 Box 158Hamburg, IL 62045
For:07/27/2011ABC Corp po4543
#3141933062100SmithCM 02 E 20-27 P15.27
#7771831766100SmithCM 02 SD 2013.51
#9052733818002BrownCM 02 D 201.35
* 
For:07/27/2011DEFG-USA po725
#241920934126-100SmithCONNECTOR CA 6 LS13.77
#844120932325100SmithCA 6 GD14.79

You simply open the file and start reading in the lines.

Dim ShipTo(6), OrderTo(6), PurchaseOrder, OrderDate, PartNumber, Mannufacturer, Description as string
Dim InventoryNumber as long, Quantity, Cost as single

100Open FileName$ For Input As file%

110If EOF (file%) Then GoTo 200
 Input #file%, a$
Item$ = Ucase (GetItem (a$, 1))

'Here is the routine that decodes the file.

 Select Case Item$
    Case "PO:"
        PurchaseOrder = GetItem (a$, 2)

    Case "DATE:"
        OrderDate = GetItem (a$, 2)

    Case "TO:"
        For x% = 2 To 7
            OrderTo (x% - 1) = GetItem (x%)
        Next x%

    Case "SHIP TO:"
        For x% = 2 To 7
            ShipTo (x% - 1) = GetItem (x%)
        Next x%

     Case "#" 'Inventory Number
         InventoryNumber = Val (GetItem (a$, 2))
        Quantity = Val (GetItem (a$, 3))
        PartNumber = GetItem (a$, 4)
        Manufacturer = GetItem (a$, 5)
        Description = GetItem (a$, 6)
        
'Item 7 is the package quantity
        Cost = Val (GetItem (a$, 8))

    Case "*"
        
'comment

    Case "FOR:"
        
'The company the Order/Items were ordered for

End Select

'Processing code here

200Close file%

Since each line is read in as a String, interrogating the First Item in the String allows for the further processing of that String regardless of the number of Items in the String. Now you can't get out of sync with this Mixed Data Structure.

Also since this is just a Text File, you can use an Editor, such as NotePad, to change any mistakes, just as long as you don't remove any of the Delimiters.

Sort Routines

Sorting is very important. When you need to find a Part Number or Item, you need a method of finding it in a hurry. It is very frustrating when a routine doesn't work well. This was one of the major concerns when I set up our Inventory Software. I needed a method of finding things that were in the Inventory File and I didn't know where they were. So I divided them into two categories, Type and Manufacturer. By entering either or both in a Search Routine, you can narrow down your parts rather quickly. As the Search Routine finds the Parts, they are displayed on the screen with the Inventory Number, the Part Number, the Quantity, the Manufacturer, and the Description.

The original routine was set up to run on two 8" floppy disks, so it had to be efficient. Here is the structure of the files:

There are 3 files. The first two are ASCII files with either the Type or Manufacturers' Names and a starting Record Number in the Inventory File. After obtaining a starting record number, you open a second file, record oriented, containing matching information, parallel with the Inventory File, of Type and Manufacturer Data. Each record points to the next record of that same Type or Manufacturer.

For example, let's say that the following multiple Part Numbers exist in the Inventory File:

Inventory Record NumberPart NumberManufacturerType
100a123SmithConnector
101a124SmithSwitch
102a125SmithConnector
103b141BrownConnector
104a152SmithConnector
105a129SmithKnob
106a123BrownConnector
107a123JonesConnector
This then is what the Manufacturer File contains alphabetically:
NameStarting Record Number
Brown103
Jones107
Smith100
This then is what the Type File contains alphabetically:
NameStarting Record Number
Connector100
Knob105
Switch101
The third file, which is a record oriented file matches the Inventory File, record for record.
This then is what the matching file contains:
Record NumberNext ManufacturerNext Type
100101102
101102122
102104103
103106104
104105106
105110133
106150107
1071200
When we encounter a zero, that is the indicator for the end of that item, Manufacturer or Type. The actual Data is a Delimited String using Long Integers, "101102", Record length of 20. It could be a bit longer, but that is what I originally set it up to be running under CPM.

So we Daisy Chain through this file, either by Manufacturer or by Type and matching a partial Part Number or Keyword in the Description. With this method, you only read and check the least Number of Records. This improves performance and reduces the amount of data read. There is always a third option of "All". For this option we must we read each Record in the Inventory File and check for a match. This is a bit slower, but when you can't find it any other way or there is a spelling error it is very useful.

There are other search parameters that could be added so we set up the coding to allow for additions.
In the Visual Basic coding we can set it up as following:

Private Type EachSortRecord
    SortMfg As Long
    SortType As Long
End Type

Subscripts could also be used.
The Record Length is calculated, based on the number of Entries.

Const NumberOfEntries As Integer = 2
Const LengthInteger As Integer = 4
Const DataRecordLength As Long = LengthInteger * NumberOfEntries

This makes a Record Length of 8 bytes for just 2 entries.

Here is a typical search box and with the drop down menu shown.

I like to use the Keyboard for Entry and use the Default Option on the Command Button to move to the next Box. Here is a sample of that code:

Private Sub cmdSearch_Click()
    Select Case WhoHasFocus

        Case 1
            cmbMFG.SetFocus
            cmbMFG.SelStart = 0
            cmbMFG.SelLength = Len(cmbMFG.Text)

        Case 2
            cmbType.SetFocus
            cmbType.SelStart = 0
            cmbType.SelLength = Len(cmbType.Text)

        Case 3
            txtPartNumber.SetFocus
            txtPartNumber.SelStart = 0
            txtPartNumber.SelLength = Len(txtPartNumber.Text)

        Case 4
            txtKeyWord.SetFocus
            txtKeyWord.SelStart = 0
            txtKeyWord.SelLength = Len(txtKeyWord.Text)

        Case 5
            Call cmdSearch_MouseUp(0, 0, 0, 0)
            cmbTypeMfg.SetFocus
            cmbTypeMfg.SelStart = 0
            cmbTypeMfg.SelLength = Len(cmbTypeMfg.Text)
            WhoHasFocus = 0

    End Select

    WhoHasFocus = WhoHasFocus + 1
End Sub

In Case 5, we simulate a mouse click to start the search routine.

The Text Boxes could also be indexed, if there were more options.

The next method is a Tree Sort routine that I developed back in the mid 80's. I needed a method of keying in the Part Number and displaying only those records that matched it exactly. Also, there may be multiple matching Manufacturers Part Numbers which need to be displayed at the same time.

First the method. I read all the Part Numbers, removing any punctuation, placing them in a temporary file with their corresponding Inventory Numbers. If there are more than one Part Number associated with a part, then it is also placed in the temporary file. Since it is difficult to remember where some Manufacturers place Dashes and Periods, I decided to remove any non essential punctuation leaving only numbers and letters to sort through. This is extremely benifictual when customers don't know if it's is a dash or period and you can't remember. Next I read all the Part Numbers, counting the first character in the Part Number. The one with the highest count gets processed first. Each pass removes the items with the current character.
For example if we have the highest count of starting character "3". Then all Part Numbers starting with "3" get processed and removed form the temporary file. Then the next highest and so on.

I determined, through many simulations, that I needed 2 Branches for each Node and an Extension. I use Long Integers for the Record Numbers and 2 ASCII characters, one for each Branch.

Private Type EachBranch
    Character As String * 2
    Branch(1 To 2) As Long
    BranchExtension As Long
End Type

We could repalce the 2's with NumberOfBranches.
The Record Length is calculated, based on the number of Branches.

Const NumberOfBranches As Integer = 2
Const LengthInteger As Integer = 4, Discard As String = "-./\[]*?"
Const DataRecordLength As Long = NumberOfBranches + (NumberOfBranches + 1) * LengthInteger

This makes a Record Length of 14 bytes.

Character 1
Character 2
Branch 1
Branch 2
Branch Extension

Here is a crude representation of the branching process. Each box represents a Node.

When you reach the end of a branch, you need a special character to depict that the next value is an Inventory Number instead of another branch. Since I didn't allow spaces in the Part Number and this was in the 80's, I used a space as the defining character. I would probably use the character 127 now.

This is very fast. Anyone who has ever searched through the Windows Registry file knows how slow a cumbersome database can be. It may take several minutes to find what you are looking for. If you applied this sort routine to the Windows Registry, it would only take a couple of milliseconds to find anything. It certainly takes longer to enter the search line than to find it with this sort routine.

I have two versions, one that is real time and the other that is not. The version that is real time sorts as each character is entered and runs out all of the Branches until the next character is entered. Then it starts over again. It always returns the closest match in the tree if the exact Part Number is not found.

I hope you find this information useful. We will continue to add to this from time to time. Beckwith Electronics