Denormalising CSV tables

Peter Haworth pete at lcsql.com
Sun Mar 15 11:30:45 EDT 2015


OK, then I think the array approach is the way to go. But you'll still need
to parse the files which could be pain. Get yourself a copy of Alex
Tweedly's csv file parser, it takes care of all the gotchas.

Another approach might be to create an sqlite database with the 4 tables in
it, load the data into it and then select it as mentioned and create the
denormalized csv file yourself

On Sun, Mar 15, 2015, 8:21 AM Keith Clarke <keith.clarke at me.com> wrote:

> Sadly, I have no control over the source CSVs - they are from a backup zip
> file.
>
> I’m trying to denormalise the key data down to the most detailed level in
> LiveCode to simplify a single import into Excel - to enable pivot table
> reporting without having to reconstruct the relationships in Excel using
> Vlookups.
> Best,
> Keith..
>
> > On 15 Mar 2015, at 15:14, Peter Haworth <pete at lcsql.com> wrote:
> >
> > Are you creating the csv file yourself? If so, it should be easy to use a
> > Select statement with JOINs to make one row for each group of related
> > entries from the four tables.
> >
> > On Sun, Mar 15, 2015, 6:59 AM Keith Clarke <keith.clarke at me.com> wrote:
> >
> >> Thanks for the steer, Mike. I’ve not really used arrays before, but this
> >> sounds a more efficient approach - so I’ll wok through the lessons &
> docs.
> >> Best,
> >> Keith..
> >>
> >>> On 15 Mar 2015, at 13:21, Mike Doub <mikedoub at gmail.com> wrote:
> >>>
> >>> Basically createa direct access to all the child tables.
> >>>
> >>> On Sunday, March 15, 2015, Mike Doub <mikedoub at gmail.com> wrote:
> >>>
> >>>> Would it not be better to make the child table into an array indexed
> by
> >>>> its ID, then make a single pass thru the parent table using the child
> ID
> >>>> into the array.
> >>>>
> >>>> Mike
> >>>>
> >>>> On Sunday, March 15, 2015, Keith Clarke <keith.clarke at me.com
> >>>> <javascript:_e(%7B%7D,'cvml','keith.clarke at me.com');>> wrote:
> >>>>
> >>>>> Folks,
> >>>>> Is there a ‘smart’ way to denormalise ‘child' & ‘parent' CSV tables
> >>>>> extracted from an RDBMS in LiveCode or is it down to iterating
> through
> >>>>> lists using chunk functions?
> >>>>>
> >>>>> I have four tables, related in the database as
> >>>>> Child-Parent-GrandParent-GreatGrandParent. Each file has its own
> >> record ID
> >>>>> & the ID of its direct parent. I need to create a ‘DenormalisedChild'
> >> file
> >>>>> with GrandParent & GreatGrandParent IDs appended to each row.
> >>>>>
> >>>>> I’m currently assuming the only way forward is to build a
> >>>>> tDenormalisedChild variable by repeated iteration at each ‘level’:
> >>>>> Iterating through lines of the Child file to compare its
> ChildParentId
> >>>>> with each row in the the Parent file, until ParentRowId =
> >> ChildParentId;
> >>>>> Repeat (1) on Parent file rows to find ParentGrandParent values;
> >>>>> Repeat (1) on GrandParent file rows to get GreatGrandParent values
> >>>>>
> >>>>> Is their a more efficient approach?
> >>>>> Best,
> >>>>> Keith..
> >>>>> _______________________________________________
> >>>>> use-livecode mailing list
> >>>>> use-livecode at lists.runrev.com
> >>>>> Please visit this url to subscribe, unsubscribe and manage your
> >>>>> subscription preferences:
> >>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
> >>>>
> >>>>
> >>> _______________________________________________
> >>> use-livecode mailing list
> >>> use-livecode at lists.runrev.com
> >>> Please visit this url to subscribe, unsubscribe and manage your
> >> subscription preferences:
> >>> http://lists.runrev.com/mailman/listinfo/use-livecode
> >>
> >>
> >> _______________________________________________
> >> use-livecode mailing list
> >> use-livecode at lists.runrev.com
> >> Please visit this url to subscribe, unsubscribe and manage your
> >> subscription preferences:
> >> http://lists.runrev.com/mailman/listinfo/use-livecode
> > _______________________________________________
> > use-livecode mailing list
> > use-livecode at lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode



More information about the use-livecode mailing list