I tried using a name that more accurately describes my question but msg said I am limited to 150 chars.
Looking for assistance from someone who has advanced SQL skills. Ideally I want to do it in SQL to let the computer do the work. Too much manual manipulation is ripe with the possibility of mistakes. I've already searched for users groups within Google. All emails are being returned saying the email does not exist anymore. What I am using appears to be a proprietary version of Dremel SQL / Google SQL, however, someone experienced in Dremel SQL will probably be able to guide me in the right direction.
BACKGROUND INFO:
- Pulling a column that is an array column which holds another array (a notes column). I think maybe an array of arrays?
- I have not figured a way to do what I am trying to do with Google or Dremel SQL yet.
- So for now, I am doing it the hard way.
- As originally pulled, the data looks like this [{Array of arrays}, {Array of arrays}, {Array of arrays}, etc., repeat... : More specifically: [{4 or more text fields which could also hold numbers and separated by commas}, {another set of fields}, {another set of fields}...]
I.E. (this is all in just one column of data and hundreds of rows)
[
{"created":"1540236216969","notes": blah... blah... blah", "original_text_length":534, "User_email":"someone@emailaddress.com","user_shortname":"someone"},
{"created":"1540236216969","notes": blah... blah... blah", "original_text_length":1224, "User_email":"someone@emailaddress.com","user_shortname":"someone"},
{"created":"1540236216969","notes": blah... blah... blah", "original_text_length":1664, "User_email":"someone@emailaddress.com","user_shortname":"someone"}
...
]
The number of these is different for each row pulled and each has a specific ID # A typical row of data is: ID #, start_date, end_date, some other fields, notes_(the array field)
WHAT I AM DOING NOW is:
- SQL data pull,
- exporting to google sheets,
- make separate tabs for the different array columns.
- copying the notes column (the array column holding arrays) to a separate tab on Google Sheets, then
- Split Text To Columns using the first curly brace "{" as the separater.
Here is where my dillema is. Once pulled, I need to split all of those columns again to separate each of the individual elements in each array. Unable to Split text to Columns again with all of them highlighted. I can Split Text to Columns again one at a time but will really be a pain if I have to do that individually for each column and every row (hundreds of rows). Need to find a way to automate this.
I will also need to change each of unix dates to calendar dates within each array PLUS add rows to the spreadsheet depending on the number of columns from the first split. The columns are different for each row depending on how many notes have been added.
OR... do it with SQL (which appears to be a proprietary type of SQL similar to NoSQL but not the same). I have tried using syntax's for IBM SQL, Oracle SQL, SQL Server, and others found online but none work.
OR... do it with a looping function within Google Sheets.
Possibly re-add it to the database as a new table once both sets of arrays are completely split up.
END RESULT ID#, date1, date 2, first created date (right now a unix date), first note, first other field, etc...
Then add a new row with Same ID# from above, date1 from row above, date 2 from row above, next (2nd) created date (right now a unix date), 2nd note, 2nd other field, etc...
Add a new row... 3rd set of notes etc.
