Most streamlined method to get data

[Skip Kimpel]

This is why you have to love this group...... never knew that the code
csvToText existed.   It worked like a charm!!!

Thank you so much everybody for your help with this puzzle!

SKIP

On Sun, Aug 1, 2021 at 7:39 PM Alex Tweedly via use-livecode <
use-livecode at lists.runrev.com> wrote:

> It looks like all the "thousand dollar+" amounts are quoted - i.e. it's
> an example of a regular CSV file - ugly but predictable.
>
> Did you try the CSVToTab converter ?
>
>
> https://github.com/macMikey/csvToText
> <https://github.com/macMikey/csvToText>
>
> Alex.
>
> On 01/08/2021 20:39, Skip Kimpel via use-livecode wrote:
> > So to give you a full sense of the ugliness of the CSV I am working with,
> > here it is:  (trying to add certain columns but because some columns have
> > multiple "thousand dollar+ amounts, it throws doing an item count.  Need
> to
> > get rid of all dollar signs (easy) and all the commas that are part of a
> > thousand+ dollar amount)
> > -------------------
> >
> >
> > ,3rd Party,Bakery,Bar,Catering,Delivery,Dine In,Drive Thru,Take
> > Out,,Vending,Total,
> >
> > 8:00 AM,$0.00,$0.00,$0.00,$0.00,$0.00,$0.00,$0.00,$406.43,,$0.00,$406.43,
> >
> > 9:00 AM,$0.00,$0.00,$0.00,$0.00,$0.00,$0.00,$0.00,$0.00,,$0.00,$0.00,
> >
> > 10:00
> AM,$40.74,$0.00,$0.00,$0.00,$0.00,$52.41,$0.00,$200.69,,$0.00,$293.84,
> >
> > 11:00
> >
> AM,$538.14,$3.29,$0.00,$0.00,$0.00,$581.30,$0.00,$165.93,,$0.00,"$1,288.66",
> >
> > 12:00
> >
> PM,$184.11,$3.29,$0.00,$0.00,$0.00,$745.68,$0.00,$274.36,,$0.00,"$1,207.44",
> >
> > 1:00
> > PM,$89.06,$12.87,$0.00,$0.00,$0.00,$741.65,$0.00,$132.26,,$0.00,$975.84,
> >
> > 2:00
> > PM,$216.86,$12.47,$0.00,$0.00,$0.00,$604.86,$0.00,$90.32,,$0.00,$924.51,
> >
> > 3:00
> PM,$160.69,$0.00,$0.00,$0.00,$0.00,$404.66,$0.00,$37.86,,$0.00,$603.21,
> >
> > 4:00
> > PM,$68.31,$33.71,$0.00,$0.00,$0.00,$349.46,$0.00,$323.34,,$0.00,$774.82,
> >
> > 5:00
> >
> PM,$286.31,$0.00,$0.00,$0.00,$0.00,$625.92,$0.00,$253.94,,$0.00,"$1,166.17",
> >
> > 6:00
> >
> PM,$500.66,$36.11,$0.00,$0.00,$0.00,$790.25,$0.00,$609.95,,$0.00,"$1,936.97",
> >
> > 7:00
> >
> PM,$224.81,$26.95,$0.00,$0.00,$0.00,"$1,038.03",$0.00,$137.17,,$0.00,"$1,426.96",
> >
> > 8:00 PM,$0.00,$0.00,$0.00,$0.00,$0.00,$55.46,$0.00,$7.10,,$0.00,$62.56,
> >
> > 9:00 PM,$0.00,$0.00,$0.00,$0.00,$0.00,$2.75,$0.00,$0.00,,$0.00,$2.75,
> >
> > 10:00 PM,$0.00,$0.00,$0.00,$0.00,$0.00,$0.00,$0.00,$0.00,,$0.00,$0.00,
> >
> >
> Total,"$2,309.69",$128.69,$0.00,$0.00,$0.00,"$5,992.43",$0.00,"$2,639.35",,$0.00,"$11,070.16",
> >
> > ,,,,,,,,,,,,Page 1 of 1
> >
> > On Thu, Jul 29, 2021 at 1:41 PM Mark Wieder via use-livecode <
> > use-livecode at lists.runrev.com> wrote:
> >
> >> On 7/29/21 9:24 AM, Ben Rubinstein via use-livecode wrote:
> >>> As grepophile, I'd go for some variation on
> >> While I heartily approve of Ben's use of grep, here's another approach
> >> using an array. Note that is somewhat specific to your data set, and
> >> from the looks of the data my guess is that you're heading down the path
> >> of csv madness.
> >>
> >> on mouseUp pMouseBtnNo
> >>      local tVar, tHeader, tCount, tOriginal
> >>
> >>      put field 1 into tVar
> >>      put line 1 of tVar into tHeader
> >>      delete line 1 of tVar
> >>      split tVar by cr
> >>
> >>      repeat for each key tKey in tVar
> >>         # transform "$8,303.32" into "$8303.32"
> >>         repeat for each trueword tTrueWord in tVar[tKey]
> >>            put tTrueWord into tOriginal
> >>            replace comma with empty in tTrueWord
> >>            replace tOriginal with tTrueWord in tVar[tKey]
> >>         end repeat
> >>         # now we can deal with commas as itemDelimiters
> >>         put 1 into tCount
> >>         repeat for each item tWord in tVar[tKey]
> >>            put tWord into tVar[tKey][tCount]
> >>            add 1 to tCount
> >>         end repeat
> >>      end repeat # for each key tKey in tVar
> >>
> >>      # now pick out the desired total
> >>      local tTotal, tOffset
> >>      put itemoffset("Total", tHeader) into tOffset
> >>      put tVar[5][tOffset] into tTotal
> >>      breakpoint
> >> end mouseUp
> >>
> >> --
> >>    Mark Wieder
> >>    ahsoftware at gmail.com
> >>
> >> _______________________________________________
> >> 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
>