449,054 Members | 1,322 Online
Need help? Post your question and get tips & solutions from a community of 449,054 IT Pros & Developers. It's quick & easy.

# Extracting from Long Binary field

 P: n/a I'm wondering if anyone has come across something like this before and could offer some suggestions. I need to extract all the Single values packed in a Long Binary field (4k chunks) and then export to Excel. The challenge is arranging the values in columns and rows so they can be exported and land in Excel properly for charting. The way the data is packed in the LB field can be illustrated like this: 1 3 5 2 4 6 7 9 11 8 10 12 I have to get this series of values into 2 tables looking like this: 1 | 3 | 5 7 | 9 | 11 2 | 4 | 6 8 | 10 | 12 In reality it's more complicated, but the idea is separating the series based on every 3rd (or Nth) value in the series, and stacking them up in columns (N is variable and has to be inferred by other parameters). After I get the values into a table the way I want them, I can easily dump it out to Excel. What I'm experimenting with now looks something like this: Const CHUNKSZ = 4 Do While Not rstTd.EOF lngOffset = 0 Set colTd = New Collection lngBsize = LenB(rstTd!MeasData) ReDim abytB(lngBsize) abytB = rstTd!MeasData.GetChunk(0, lngBsize) lngMid = rstTd!MeasurementId Do While lngOffset < lngBsize CopyMemory sngBval, abytB(lngOffset), 4 colTd.Add sngBval lngOffset = lngOffset + CHUNKSZ Loop 'insert values into table here Set colTd = Nothing rstTd.MoveNext Loop Once the values are extraced into colTd, I pass the collection to another function that massages the data into a table based on whatever the layout requirements are. But how to split the series of values on every Nth item in the collection? Is this something that could be done with SQL based on an Autonumber ID in the table? Other suggestions? Thanks in advance. Nov 13 '05 #1