Is This Even Parse-able?

Question

Hi All

I have inherited an old FoxPro database which stores the Parts List for engineering drawings as a long text string.
Some elements of the string are plain text, defining e. g. item number and quantity, other elements are primary key entries for linked data.
Each string represents multiple items separated by the @ sign.
A typical example of the string is given below:

@#a#1#1#8409#0# #F#@#e#2#2#Bl##DIN1543#oZ#5##RSt37-2#1.2# #@#e#3#1#Bl##DIN1543#oZ#5##RSt37-2#3.8# #@#b#4#2#3#12#38# #40#80#F#@

So the first item in this sequence, between the first two @ characters is type a (which means it is a drawing and the following elements should be treated in a certain way).
This is item 1, quantity 1 with drawing pulled from field with primary key 8409 and revision status 0.
Second is type e which is a part and all elements are text.
This is item 2, quantity 2 and the rest is text defining the item, its material and weight.
Third is again type e giving...
Item 3, quantity 1 then loads of general info.
Fourth is type b which is a part but needs to be treated differently.
Item 4, quantity 2 [not sure what the number 3 relates to yet], 12 and 38 pull data from material tables, 40 and 80 are dimensions.

There are five different item types a - to - e that I have seen so far in the extracted data, all which will need to be handled differently by an extract process.
Not sure why the drawing items end with the F character.

I am hoping someone will be able to recommend an approach to deconstructing these string entries, mapping the relevant fields back to the appropriate tables and then restructuring the data in a more meaningful way.

Many thanks

Phil

Answer 1

You can write some code to break this data out, and then say add the data to a new table.

So, say we start with your example string. Lets assume that this string is one (some) column from a table that you imported into Access.

so the overall code will look much like this code (air code - just an idea how you could/would go about this).

Sub ProcessData()  
  
   Dim rstInData     As DAO.Recordset  
   Dim rstOutData    As DAO.Recordset  
   Dim strBuf        As String  
            
   Set rstInData = CurrentDb.OpenRecordset("InData")  
   Set rstOutData = CurrentDb.OpenRecordset("tblOutPut")         
     
   Do While rstInData.EOF = False  
     
      strBuf = rstInData!myData        ' "my data is the column with the long string'  
        
      Dim MyBuf()       As Variant  
      Dim MyBuf2()       As Variant  
        
      MyBuf = Split(strBuf, "@")            
      MyBuf2 = Split(MyBuf(0), "#")            
        
      With rstOutData  
         .AddNew  

         !itemType = MyBuf(1)  
         !PK = MyBuf2(4)  
         !Whatever = mybuf2(??)  
           
         .Update  
     End With         
      rstInData.MoveNext  
   Loop  
     
rstInData.Close  
rstOutData.Close  
  
End Sub  

So note how we use split command. the first split get us a "group" or array of all the "@" values in an array.

Then, from each of those, we use split on that token, and we split out the "#" elements into an array.

so you need some looping code to process each row of the main import table, and then for each input row, we split out by @, and then for each @ group, we split on #.

So, using split() and a combination of data table looping as per above, in which for each row of the input, we are now able to parse out the given values form that array into the target table.

I not 100% clear (I not looked really really close) that for each row of data, we wind up with one main record, and several child records. In that case (and it looks to be), then you need of course two target tables - one to hold the main record you add, and then a child table to add the repeating data you have for each string row. So, each "string" of data you have looks to be a main record, and then some child data records that belong to the one main "record".

so, this is not lot of code but it is somewhat tedious to write. But break out the above into a similar code loop as per above. So, using split() into a array, and then of course VBA reocrdset processing similar to the above is how you can chew away at this problem.

So without question, you need to be comfortable with VBA code, and also comfortable with using VBA recordsets in code.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada