Most streamlined method to get data

[Alex Tweedly]

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