• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
code to unpack query array to tab-delimited data?
#1
Hello. I am very new to and still much in the dark about arrays. I am looking for efficient code to unpack the output from a query to lines of tab-delimited data (one record per line). Here is the code I've come up with so far. I'd guess there MUST be a more efficient approach.

on mouseup
## query constructed above
 put cdb_batchQuery(tInputA,"cloud","logicalAND","recordData") into tOutputA
 put flattenArray( tOutputA) into flattened -- see flattenArray function below
end mouseup

function flattenArray A , d1, d2, T,  X
   if T is empty then
      if d1 is empty then put cr into d1
      if d2 is empty then put tab into d2
   end if
   if A is not an array then return T  & X & A & d1
   repeat for each key K in A
      put flattenArray( A[K],  d1, d2, T,  X & K & d2 )  into T
   end repeat
end flattenArray

This yields the queried records in this format (just one record of many shown here).

[CDB Table ID]    1    [CDB Record ID]    ATTRIBUTES    fopn
[CDB Table ID]    1    [CDB Record ID]    LAST_NAME    Jordan
[CDB Table ID]    1    [CDB Record ID]    STATUS    linnm
[CDB Table ID]    1    [CDB Record ID]    RANK    2
[CDB Table ID]    1    [CDB Record ID]    CITY    San Francisco
[CDB Table ID]    1    [CDB Record ID]    YEAR    2019
[CDB Table ID]    1    [CDB Record ID]    ROLE    5
[CDB Table ID]    1    [CDB Record ID]    NAME    Sam Jordan
[CDB Table ID]    1    [CDB Record ID]    DATE    2019-02-17
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbDateModified    1562677087
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbDateCreated    1562677087
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbRecordID    [CDB Record ID]
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbTableID    [CDB Table ID]
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbRecordVersion    1
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbTableName    stats
[CDB Table ID]    1    [CDB Record ID]    cdb    cdbCloudSyncVersion    1

I then loop through the lines and remove the first two columns, then I filter out the lines with "cdb" keys not needed in my app as long as I preserve the record ID, and get a list of records like this…

[CDB Record ID]    ATTRIBUTES    fopn
[CDB Record ID]    LAST_NAME    Jordan
[CDB Record ID]    STATUS    linnm
[CDB Record ID]    RANK    2
[CDB Record ID]    CITY    San Francisco
[CDB Record ID]    YEAR    2019
[CDB Record ID]    ROLE    5
[CDB Record ID]    NAME    Sam Jordan
[CDB Record ID]    DATE    2019-02-17

Finally, I loop through the lines again and put the data for each record into the tab-delimited format I need, which is something roughly like this…

fopn    Jordan    linnm    2    San Francisco    2019    Sam Jordan    2019-02-17    [CDB Record ID]
ucpn    Smith    linnm    1    Los Angeles    2019    Susan Smith    2019-06-23    [CDB Record ID]

efrain.c and mark_talluto's suggestions for batch record creation and updates were VERY helpful. Thank you.

Does anybody have a simpler solution for this problem? Pretty sure there must be one (many). Thanks in advance.
  Reply


Messages In This Thread
code to unpack query array to tab-delimited data? - by sltfn - 08-19-2019, 07:26 PM

Forum Jump:


Users browsing this thread: 1 Guest(s)