Extracting BLOB data from Access mdb file


#1

We’ve got some equipment that we use to measure strengths of people exercising, but it’s software is pretty old & missing some functionality so I want to extract some data from their database

It’s a Microsoft Access MDB file (Versions 2.x, 3.x and 4.x). In Version 5, the application was extended to support ODBC database

The particular row I want to extract is the actual information recorded by the dynamometer (at 100Hz): “Data is collected from the dynamometer at a rate of 100 samples per second. When a test is completed, the application stores torque, position and velocity values as a BLOB structure in the appropriate row of the dataset (see Dataset table).”

The first 5 rows of the DataSet table are defined as follows (the 5th row, BLOB, is the one I’m interested in extracting as a table):
Field Name Data Type Description
[Record #] [int] Record number
[PATID] [float] Patient ID
[STUDYID] [float] Study ID
[DATAID] [float] Dataset ID
[BLOB] [image] dynamometer data

Where

BLOB structure:

struct StDataPoint
    {
        short   f_nTorque; // in foot-lbs * 10
        short   f_nPosition; // in degrees * 10
        short   f_nVelocity; // in degrees / second * 10
    };

Pulling the BLOBs out using PowerQuery was easy enough, but converting them proved to be a bit of a kludge, and I can’t help but feel there must be a better way.

My steps were: 1. Convert the BLOB to a binary with: Binary.ToText([BLOB]as binary, 1) as text), then
2. Split the resulting column into 4 character chunks (each short byte)
3. Since there are 3 values, I added an index column, then Mod(3) and a conditional column to name each row (in my case: Position, Torque, and Velocity)
4. Now I can convert the text back to a Binary Binary.FromText([Count.Custom],1)), which then allows me to convert the Binary to a List: each Binary.ToList([Binary]) as list), so now I can transform this list by combining pairs of rows, and finally convert this text list to an integer.

Here’s the M-Code:

let
Source = Access.Database(File.Contents(“F:\ODF\QSL Screening\biodexfemale.mdb”), [CreateNavigationProperties=true]),
_DataSet = Source{[Schema="",Item=“DataSet”]}[Data],
// Just 3 arbitrary rows (Record #'s 16,17, and 18) for this Test
#“Filtered Rows” = Table.SelectRows(_DataSet, each ([#“Record #”] = 16 or [#“Record #”] = 17 or [#“Record #”] = 18)),
#“Removed Other Columns1” = Table.SelectColumns(#“Filtered Rows”,{“Record #”, “BLOB”}),
#“Added Custom” = Table.AddColumn(#“Removed Other Columns1”, “Custom”, each Binary.ToText([BLOB]as binary, 1) as text),
#“Removed Columns” = Table.RemoveColumns(#“Added Custom”,{“BLOB”}),
#“Split Column by Position” = Table.ExpandListColumn(Table.TransformColumns(#“Removed Columns”, {{“Custom”, Splitter.SplitTextByRepeatedLengths(4), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), “Custom”),
#“Changed Type” = Table.TransformColumnTypes(#“Split Column by Position”,{{“Custom”, type text}}),
#“Added Index” = Table.AddIndexColumn(#“Changed Type”, “Index”, 1, 1),
#“Mod 3 To name rows” = Table.AddColumn(#“Added Index”, “Custom.1”, each Number.Mod([Index],3)),
#“Name Rows” = Table.AddColumn(#“Mod 3 To name rows”, “Custom.2”, each if [Custom.1] = 1 then “Torque” else if [Custom.1] = 2 then “Position” else if [Custom.1] = 0 then “Velocity” else null),
#“Removed Columns1” = Table.RemoveColumns(#“Name Rows”,{“Index”, “Custom.1”}),
#“Grouped Rows” = Table.Group(#“Removed Columns1”, {“Record #”, “Custom.2”}, {{“Count”, each , type table}}),
#“Expanded Count” = Table.ExpandTableColumn(#“Grouped Rows”, “Count”, {“Custom”}, {“Count.Custom”}),
#“Filtered Rows1” = Table.SelectRows(#“Expanded Count”, each true),
#“Renamed Columns” = Table.RenameColumns(#“Filtered Rows1”,{{“Custom.2”, “Channel”}}),
#“Changed Type1” = Table.TransformColumnTypes(#“Renamed Columns”,{{“Count.Custom”, type text}}),
#“Added Index1” = Table.AddIndexColumn(#“Changed Type1”, “Index”, 1, 1),
#“Reordered Columns” = Table.ReorderColumns(#“Added Index1”,{“Index”, “Record #”, “Channel”, “Count.Custom”}),
#“Binary From Text” = Table.AddColumn(#“Reordered Columns”, “Binary”, each Binary.FromText([Count.Custom],1)),
#“Binary to List” = Table.AddColumn(#“Binary From Text”, “List”, each Binary.ToList([Binary]) as list),
#“Combine List values” = Table.TransformColumns(#“Binary to List”, {“List”, each Text.Combine(List.Transform(
, Text.From)), type text}),
#“Convert text to numeric” = Table.TransformColumnTypes(#“Combine List values”,{{“List”, Int64.Type}}),
#“Removed Columns2” = Table.RemoveColumns(#“Convert text to numeric”,{“Count.Custom”, “Binary”}),
#“Renamed Columns1” = Table.RenameColumns(#“Removed Columns2”,{{“List”, “Value”}})
in
#“Renamed Columns1”


#2

Just reviewed this one and finding it a little tough to give anything really helpful here.

Just quite difficult with just the code and no context and seeing the data.

Possible to add some images.

I know passing over files isn’t much use with the query editor, so solving these one can sometimes be a little harder.

Are you attempting to just grab the 5th row consecutively? Is there maybe something you can filter on for that particular row?

Is there any reason you can use some DAX to run these calculations as well? Does it have to be done in Power Query?

I prefer DAX a lot of the time, as the formulas for me are easier to audit and control than M code at this present time.

Let me know.


#3

Thanks Sam - I’ve put some more work on this, and made some headway so that I can now extract and convert within Power Query, and the output looks correct, although there’s one final wrinkle. The position values should oscillate between, say, 100 and 1000 (the absolute range being 0 to 3600), but there appears to be an odd “cycling” problem going on where they seem to go up to ~6500 then back to 0. You can see this in the bottom image which is time (x-axis) against position


My suspicion is that there’s an issue in how I’ve converted the hex values back to decimal. It’s been a long time since I understood 2’s complement binary, and how this related to hex and decimal. I have extracted the hex values from the BLOB using
Binary.ToText([BLOB],1)),
and then converted the hex to decimal using:
Expression.Evaluate(“0x”&[Value.1])),
Which is probably where something is going wrong, but I’m not sure where to go from here, or even who I would ask about this…


#4

This looks really interesting.

Is it possible to have a look at the file? A really unique data set by the looks of it.