Export to CSV - proper format

[Brian K. White]

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