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@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@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 > >> ahsoftw...@gmail.com > >> > >> _______________________________________________ > >> use-livecode mailing list > >> use-livecode@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode