Most streamlined method to get data

Tom Glod tom at makeshyft.com
Sun Aug 1 16:13:45 EDT 2021


yikes.

You probably did the same thing, but whenever I deal with this kind of
scenario, I always work first to gain control of the delimiter without
losing any data, So I set the rules of processing accordingly.

Then when I know I have correctly changed the item delimiter, only then do
I try to process data.

good luck.




I change the delimiter to what I need, and THEN I process the data.



On Sun, Aug 1, 2021 at 3:40 PM Skip Kimpel via use-livecode <
use-livecode at lists.runrev.com> 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
>


-- 
Tom Glod
Founder & Developer
MakeShyft R.D.A (www.makeshyft.com)
Mobile:647.562.9411



More information about the use-livecode mailing list