Comma-delimited values

[Peter Brigham MD]

If you are stuck with trying to import from CSV format, here's a  
function that will convert any commas within quotes to another  
delimiter character.

function fixQuotedItems tText
    -- first make sure that you choose an escape char
    -- not contained in your data
    repeat for each item escapeChar in "§,•,ª,∞,™,º"
       -- or whatever list of odd characters you want
       if escapeChar is in tText then next repeat
       exit repeat
    end repeat
    put empty into adjText
    set the itemdelimiter to quote
    repeat for each line tLine in tText
       put 0 into counter
       put empty into adjustedLine
       repeat for each item i in tLine
          add 1 to counter
          if counter mod 2 = 1 then
             put i after adjustedLine
          else
             put i into temp
             replace comma with escapeChar in temp
             put temp after adjustedLine
          end if
       end repeat
       if char -1 of adjustedLine = comma then delete char -1 \
                 of adjustedLine
       put adjustedLine & cr after adjText
    end repeat
    if char -1 of adjText = cr then delete char -1 of adjText
    return adjText
end fixQuotedItems

this will take something like:

a,b,c,"1,2,3,4",d,e
"11,12,13",g,h,i
j,k,"22,23"

and return:

a,b,c,1§2§3§4,d,e
11§12§13,g,h,i
j,k,22§23

You can then parse your data and replace the escapeChar with comma  
after you're done.

-- Peter

Peter M. Brigham
pmbrig at gmail.com
http://home.comcast.net/~pmbrig



On Mar 8, 2010, at 2:55 PM, Gregory Lypny wrote:

> Thank you, Richmond.  Good stuff.  That would work here where only  
> the first to-be item of each line is quoted because it has commas  
> within it.  But if other files have quoted items in other locations  
> (e.g., the fifth and ninth items), it would mean first identifying  
> which chunks are quoted before I start converting from commas to tabs.
>
> Gregory
>
>
> On Mon, Mar 8 , 2010, at 1:00 PM, Richmond Mathewson wrote:
>
>>
>> On 08/03/2010 19:44, Gregory Lypny wrote:
>>> Hello everyone,
>>>
>>> I'm creating an app that imports comma-delimited tables.  A few  
>>> lines might look like this, where there are 14 items per line.
>>>
>>> "Mon, Jan 18 , 2010",9:14 AM,130557,4319,Trade,Buy,X, 
>>> 135,8.25,10,-82.5,1417.5,20,10
>>> "Mon, Jan 18 , 2010",9:14 AM,130558,4371,Accept,Your ASK,X, 
>>> 135,8.25,10,82.5,1582.5,0,10
>>>
>>> My problem is that Rev treats each date in quotes as three items  
>>> rather than one.  I convert the comma delimiters to tab by setting  
>>> the itemDelimiter to comma and then running the lines through  
>>> nested repeat-for-each loops as
>>>
>>> repeat for each line thisLine in dataTable
>>> repeat for each item thisItem in thisLine
>>> put thisItem&  tab after newLine
>>> end repeat
>>> -- more stuff here
>>> end repeat
>>>
>>> I end up with
>>>
>>> "Mon	(as the first item)
>>> Jan 18	(as the second)
>>> 2010"	(as the third)
>>>
>>> Any suggestions as how I might get the date treated as one item?
>>>
>>>
>>>
>> Yes, although it is so goofily obvious you have probably thought  
>> about
>> this one and rejected it:
>>
>> Change the commas for the bits inside the quotes to something else  
>> ( ^
>> *  %) - dunno, any old
>> thing that isn't a comma . . . :)
>>
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution