At 4:09 PM -0700 4/27/00, Richard Gaskin wrote:
>I'm working with some legacy data in (ugh!) CSV format (comma-separated
>values).
>
>Anyone have a quick-n-dirty method if turning that into the more sane
>tab-delimited format?
Hi Richard
If you can be sure that commas won't occur within the field values,
then "replace comma with tab in tData" should do the trick.
If commas are/could be included in the field values, such data
usually uses quotes around values that contain commas. For example:
123,abd,"John,Mary"
Also, if quotes can be included in field values, they are often
represented with an extra set of quotes. For Example:
123,abd,"John,Mary",""What do you mean, Sue?""
In this case, I guess you want to end up with this:
123<tab>abd<tab>John,Mary<tab>"What do you mean, Sue?"
If this is how the CSV data is structured, then the script below is a
quick try that might work. It sets the itemdel to quote, and then
assumes you want to replace commas with tabs only in the odd numbered
items of each line. (It's early, so I'm not sure if this reasoning is
sound.) First, it replaces extra quotes with a marker that is
unlikely to be used in the data. (I used a high ascii number, but
change to something that suits).
function commaToTab pData
put numToChar(200) into tMarker ## change to whatever
replace quote & quote with quote & tMarker in pData
put the itemdel into tSavedDel
set the itemdel to quote
repeat for each line tLine in pData
put 1 into tItemCount
repeat for each item tItem in tLine
if tItemCount mod 2 = 1 then
replace comma with tab in tItem
end if
put tItem after tNewData
add 1 to tItemCount
end repeat
put cr after tNewData
end repeat
replace tMarker with quote in tNewData
delete char -1 of tNewdata ## dangling return
set the itemDel to tSavedDel
return tNewData
end commaToTab
Cheers
Dave Cragg
Archives: http://www.mail-archive.com/metacard%40lists.best.com/
Info: http://www.xworlds.com/metacard/mailinglist.htm
Please send bug reports to <[EMAIL PROTECTED]>, not this list.
