Export to CSV - proper format

[Flavius Moldovan]

Hello Brian,


> Who says? As in what standard decrees that?

I am aware of the fact that there are multiple variants or CSV, however most of them have one thing in common: "If double-quotes are used to enclose fields, then a double-quote  appearing inside a field must be escaped by preceding it with  another double quote. (RFC 4180) (please read "double-quotes" = "field delimiters")

Thank you very much for taking the time to answer, write the code  and explain it in detail. You have just answered a question I was about to ask: "How can I deal with regular expression in FilePro?".

Thanks a lot,
Flavius.



On Oct 21, 2010, at 3:21 AM, Brian K. White wrote:

> On 10/20/2010 10:22 AM, flavius m wrote:
>> 
>> Hello,
>> 
>> What is the best (most efficient) way to export data from FilePro to a proper CSV file?
>> 
>> I use the following:
>> export word aaa=/tmp/exported_file.txt f=, r=\n
>> 
>> ... but my problem is that the inner quotes are not escaped
>> (per CSV format, the quotes inside a csv record should be duplicated: e.g. "this ""is"" my test").
> 
> Who says? As in what standard decrees that?
> 
> In any event the most convenient way I found so far to do that kind of 
> conversion (inserting more characters into the output than were in the 
> input), isn't exactly convenient or efficient, but it's the most 
> optimized I have come up with so far. you make a small gosub that loops 
> through every character in a variable, and use it before every export 
> assignment that might want that kind of translation.
> 
> Also, you don't need f=, or r=\n, they are already part of the exort 
> word format. You would need those with export ascii, if you wanted more 
> control over when & how quotes were used, or not.
> 
> ------top of table------
> If: ' *** export contacts to csv for Palm Desktop / Outlook / Other PIM
> Then: gosub exp
> If: ' Name
> Then: n = 1 ; gosub cln ; csv(1) = n
> If: ' Company
> Then: n = 12 ; gosub cln ; csv(4) = n
> If: ' Work Phone
> Then: csv(5) = 3
> [...]
> If: ' Notes
> Then: n = 4{l{6{l{13{l{14{l{19{l{20{l{21{l{22{l{23{l{24{"" ; gosub cln ; 
> csv(19) = n
> [...]
> If:
> Then: end ' main
> cln    If: ' clean  '**************************************
> Then: p = "1" ; i = n{"" ; s = len(i{"")
> nxtchr If:
> Then: ic = mid(i,p,"1") ; oc = ic
> If: ic = chr("34")
> Then: oc = ic & ic
> If:
> Then: n = n & oc
> If: p lt s
> Then: p = p + "1" ; goto nxtchr
> If:
> Then return ' cln
> exp    If: ' export '**************************************
> Then: export word csv=(@pm)
> If:
> Then return ' exp
> @once  If: '***********************************************
> Then: l(1,*,g)=chr("10")
> If: ' write header record
> Then: gosub exp
> If:
> Then: csv(1) = "Last Name" ' or csv(1) = fieldname(-,1)
> If:
> Then: csv(4) = "Company" ' or csv(4) = fieldname(-,12)
> [...]
> If:
> Then: csv(19) = "Notes"
> [...]
> If:
> Then: end ' @once
> --------end of table--------
> 
> 
> OK so description, most of the table should be pretty easy to get, just 
> the cln gosub needs explaining. Basically, where you would normally say 
> simply:
> csv(1) = 1
> 
> If field 1 might have junk in it, you "clean" it first by copying 1 to 
> n, gosub "clean" which will read n and modify it in-place to remove bad 
> characters and do whatever other translations you need, in this 
> particular case it will just replace all " with "", then export N 
> instead of the original real field. So, in the main part of the table,
> csv(1) = 1
> becomes
> n = 1 ; gosub cln ; csv(1) = n
> 
> 
> Now the gosub itself:
> 
> cln    If: ' clean  '**************************************
> Then: p = "1" ; i = n{"" ; s = len(i{"") ; n = ""
> 
> First,  (re)set a few counters and temp fields that will be used in the 
> upcoming loop.
> p = position, which character of field I are we looking at, starts at 1.
> i = input, local temp copy of N because we will overwrite N
> s = size(length) of I so we know when to stop looping
> n = the input & output for the gosub. we received the data to work on 
> via N, and we will return the modified data via N. So start by 
> zeroing-out N after we have a copy of the original N in I.
> 
> nxtchr If:
> Then: ic = mid(i,p,"1") ; oc = ic
> 
> begining of character loop
> IC = input character = "1" character at position P within I.
> OC = output character(s) = will usually simply be the same as in, so 
> start by just setting oc to a copy of ic.
> 
> 
> If: ic = chr("34")
> Then: oc = ic & ic
> 
> If IC happens to be  a "
> Then set OC to be two of them instead of the usual one
> 
> 
> If:
> Then: n = n & oc
> 
> Append the current output character(s) to the end of n
> 
> 
> If: p lt s
> Then: p = p + "1" ; goto nxtchr
> 
> If the current position (within I) is less than the length of I
> Then increment the current position and process the next character.
> 
> 
> If:
> Then return ' cln
> 
> When P reaches the end of I, just return.
> N has already been built back up one character at a time and we are done.
> 
> -- 
> bkw
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> Subscribe/Unsubscribe/Subscription Changes
> http://mailman.celestial.com/mailman/listinfo/filepro-list
>