question

PhilS-6684 avatar image
0 Votes"
PhilS-6684 asked PhilS-6684 commented

Is This Even Parse-able?

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

office-access-dev
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Two things come to mind.
1. Use the split function to create an array, splitting on the @ character. Then split each item of that array on the # character. Then process each item as necessary.
2. Use Regular expressions. Not my forte, but I'm sure would work. You'll need to research using them.

1 Vote 1 ·

Hello Alphonse

Thanks for taking the time to reply.

Can you clarify what you mean by "Use Regular expressions" please?

Phil

0 Votes 0 ·

Hi Phil. I agree using the Split() function should be easier for what you want to do. Regular Expressions (RegEx) rely on having a repeatable pattern in your data. I would only go with RegEx if you didn't have the @ and # delimiters that clearly identify the borders between your data.

1 Vote 1 ·

1 Answer

AlbertKallal-4360 avatar image
1 Vote"
AlbertKallal-4360 answered PhilS-6684 commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the detailed response AlbertKallal-4360 - and apologies for late acknowledgement.
Not done a lot of vba so far, but from your and other responses this seems the route I must take.

Phil

0 Votes 0 ·