Creating text string 1

[harrymossman]

I am creating a form that runs a query and combines fields into a text string, which is written as a spreadsheet. Commas are used to separate the data into columns.

Initially, I had a problem because some of the numeric data had commas in it. These commas created extra columns I didn't want. Someone explained that I could fix this with format("Wn",fieldToFormat).

However, I am also having trouble with text elements of the string formula that have commas within them. E.g., "San Francisco, CA" (There's no set pattern of where the commas might be though.)

+ "," + tcMas.projectSite + "," +

Can anyone suggest an easy way to fix this?

 

[NotAllThere]

Try enclosing the value in quotes :
+ ",\"" + tcMas.projectSite + "\"," +
Let's know.

 

[harrymossman]

Thanks. That's getting me much closer. Now I'm getting, e.g., "San Francisco, CA" (in quotes) together in one field. But maybe that's what my post suggested that I wanted, since I put the example in quotes. Ideally, though, I would like to not have the quotes.

 

[NotAllThere]

Are the quotes appearing in the spreadsheet cells? In a normal CSV (Comma Separated Value) file export, text values which contain the delimiter (in this case a comma) are usually in quotes so that each value can be separated by comma.

I thought that was what you were trying to achieve so sorry if I've given a wrong steer. Can you give more info on how you're writing the spreadsheet - or is it a text file for import to a spreadsheet app? Which app - Excel, etc?

It may be worthwhile to dummy up a real spreadsheet in the way you want it, and then SaveAs to CSV format. Load the CSV file into Wordpad an see exactly the format which will work out.

Maybe the following will work better since it will only put the quotes around the value if it contains commas:
+ iif(search(tcMas.projectSite,",&quot> 0,",\"",",&quot + tcMas.projectSite + iif(search(tcMas.projectSite,",&quot> 0,"\",",",&quot +

 

[harrymossman]

Oops! I spoke too soon about it working except for the commas in the cells. Paradox is still putting the comma-containing strings in separate cells. I have played around with the commas and slashes but no luck yet. Here's the code:

ts.create(":priv:SpreadSheet.wb3")
tcMas.open(":priv:makeSpreadSheet.DB")
tcDet.open(":priv:impactCounties.DB")
counter = 1
myString = ""

scan tcMas:

   scan tcDet:

      if tcDet.impact = tcMas.impact and counter=2
        then  myString = myString+ "; " +tcDet.counties
      endif

      if tcDet.Impact = tcMas.Impact and counter=1
        then  myString = tcMas.impact + "," + tcMas.year + "-" + tcMas.number + "," + format("W10.3",tcMas.DirectImpactAcres) + "," + format("W10.3",tcMas.IndirectImpactAcres) + ",\"" + tcMas.projectSite + "\"," + tcDet.counties
              counter = 2
      endif

   endscan

ts.writeLine(myString)

counter = 1

endscan

 

[NotAllThere]

Oh! Spreadsheet.wb3? Are you trying to directly create a Quatro Pro Workbook? The code looks like it's creating a CSV text file. You say "Paradox is still putting the comma-containing strings in separate cells." I don't understand. Paradox is writing the a text file (but maybe not in the correct format for importing into a spreadsheet). I'm confused. If you give a bit of dummy data (e.g. samples values for impact, counties, etc.) and describe what you want to see as output (e.g. how you want the spreadsheet to look), I'll have another look at it.

Question: Is it easier to create what you want as a temporary table - each field mimicing a column and each record mimicing a row - and then exportSpreadsheet?

 

[harrymossman]

I could just use exportSpreadsheet("makeSpreadSheet.DB", "spreadsheet.wb3", true) except that I want to combine counties together into cells. That's the main function of the code. The impactCounties.db data looks like:

Impact    Counties
____________________
534       Sacramento
534       Yolo
535       San Francisco

So the code scans through impactCounties.db and comes up with strings like:

Sacramento; Yolo

.

If I can get these strings in the proper database cells, then I can use exportSpreadsheet( ). However, I am quite a novice at this type of work and I don't quite know how to go about it. (I didn't create this code. Most of it was suggested to me.)

ProjectSite data varies a lot but it's all fairly short and simple, e.g.:

Sacramento, CA

Thanks for your help!

 

[NotAllThere]

I've made some assumptions on what you want for output, but try this :

var
tbl table
tcTrash tcursor
destTbl string
tv tableView
;you have already declared tcMas, tcDet somewhere
endVar

destTbl = "RIV:TRASH1.DB"
tbl = create destTbl
with "Impact" : "A200",
"YearNo" : "A20",
"DirAcre": "N",
"IndAcre": "N",
"Site" : "A200",
"Counties": "A200"
endCreate

if not tcTrash.open (destTbl) then
errorShow()
return
endif
tcTrash.edit()

tcMas.open("riv:makeSpreadSheet.DB&quot
tcDet.open("riv:impactCounties.DB&quot

scan tcMas:
tcTrash.insertAfterRecord()
counter = 1

scan tcDet for tcDet.impact = tcMas.impact:
if counter = 1 then
tcTrash.impact = tcMas.impact
tcTrash.YearNo = string(tcMas.year) + "-" + string(tcMas.number)
tcTrash.DirAcre = format("W10.3",tcMas.DirectImpactAcres)
tcTrash.IndAcre = format("W10.3",tcMas.IndirectImpactAcres)
tcTrash.Site = tcMas.projectSite
tcTrash.Counties = tcDet.counties
tcTrash.postRecord()
counter = 2
loop
endif

;more than one county impacted
if counter = 2 then
tcTrash.Counties = tcDet.counties + "; " + tcDet.counties
tcTrash.postRecord()
endif
endscan

endscan
tcTrash.endEdit()
tcTrash.close()

;next 2 lines for debug - have a look at the table and see if this is what u want
tv.open ("RIV:TRASH1.DB&quot
tv.wait()

if not exportSpreadSheet(destTbl, getAliasPath("RIV:&quot + "\\SHEET1.WB3&quot then
errorShow()
return
endif

If this gives the required result then it can be tidied up in terms of performance.

HTH
Padraig

 

[harrymossman]

Thanks. That worked great with just a little tweeking.

In the code that actually makes the spreadsheet (below), I would like to name the spreadsheet. I'm trying to use a string defined earlier in the method. I.e., MyString.WB3 in place of SHEET1.WB3.

I have played around with lots of approaches such as

string(MyString)

But nothing seems to work.

if not exportSpreadSheet(destTbl, getAliasPath(":PRIV:") + "\\SHEET1.WB3") then
    errorShow()
   return
endif

 

[NotAllThere]

Glad to hear you're getting there. That code I posted could be improved quite a lot, e.g. imactCounties.dB is being scanned once each record in makeSpreadSheet.DB. It only needs to be scanned once. However, if you've tweaked with the code, I won't post an update without seeing what you've done.

To set the name of the sheet with a variable use code like this:

Var
myFileName string
endVar

myFileName = "SOMEFILE.WB3"

;see the help on exportSpreadsheet - file extension defines the type of spreadsheet (excel, lotus, etc).


if not exportSpreadSheet(destTbl, getAliasPath("RIV:&quot + "\\" + myFileName) then
errorShow()
return
endif

You can also export the table as an ASCII file (see help on exportASCII) which is a text file, etc., if you wish (which is why I thought it better to create a table first - more flexability).

Let's know if u need tips on improving performance, etc.

 

[harrymossman]

Thanks. I went with

exportSpreadSheet(destTbl, getAliasPath(":PRIV:") + "\\" + MyString + ".WB3")

The form works fine now. I probably won't tinker much more. It's just to save me lots of work on a one-time task. Also, I wanted to learn more about how to write code, which I certainly did, thanks to you and others.

I'm posting all of the final code below. Someone might find it useful later.

[code]
method pushButton(var eventInfo Event)

var

	  tcMas  	tCursor
	  tcDet      	tCursor
	  counter    	number
	  q          	query
	  spp           string
	  tbl		table
     tcTrash 	 	tcursor
     myString		string
     destTbl 		string

endvar

;Read acronym inserted by user
spp=acronym_field.value


;Define Query

q=Query
ANSWER: :PRIV:makeSpreadSheet.DB

SORT: impact.db->"Impact"

impact.db | Impact                         | Year| Number |
          | Check _join1, _join3!, _join4! |Check| Check  |

impact.db | Notes                        |
          | Check as Notes about Project |

impactHabitat.DB | ImpKey | Impact | HabitatImpacted    |
                 | _join2 | _join1 | Check as Ecosystem |

impactHabitat.DB | DirectImpactAcres |
                 | Check				 |

impactHabitat.DB | IndirectImpactAcres  |
                 | Check 			|

impactSpp.db | ImpKey | Species |
             | _join2 | ~spp    |

impactCompensation.DB | Impact | Site             | Credits-Preservation |
                      | _join3 | Check as MitSite | Check as CrPres      |

impactCompensation.DB | Credits-Creation  | AcresPreserved  | AcresCreated      |
                      | Check as CrCreate | Check as AcPres | Check as AcCreate |

impactCompensation.DB | Acres Restored |
                      | Check as AcRes |

impactTakeAuthorized.DB | Impact | Species | Take   | Auth  | Actual |
                        | _join4 | ~spp    | Check  | Check  | Check  |

impactTakeAuthorized.DB | Notes              |
                        | Check as TakeNotes |

EndQuery


;Error processing
if not executeQBE(q) then

errorShow()
setMouseShape( mouseArrow )
   errorShow( "Can't run query", "Use [>>] for details." )
   q.writeQBE( ":PRIV:BADQUERY.QBE" )

else

;Create a temporary table

 destTbl = ":PRIV:TRASH1.DB"
 tbl = create destTbl
   with "Impact":		"I",
        "YearNo":  	"A10",
        "DirAcre": 	"N",
        "IndAcre": 	"N",
        "AcPres":  	"N",
        "AcCreate":	"N",
        "AcRes":   	"N",
        "CrPres":  	"N",
        "CrCreate":	"N",
        "Take":    	"A25",
        "Auth":    	"N",
        "Actual":  	"N",
        "MitSite": 	"A30",
        "Counties":	"A35",
        "TakeNotes": "A120"
endCreate

;Error processing
if not tcTrash.open (destTbl) then
   errorShow()
   return
endif

;Use tCursor to fill temp table
tcTrash.edit()
tcMas.open(":priv:makeSpreadSheet.DB")
tcDet.open(":WORK:impactCounties.DB")

scan tcMas:
   tcTrash.insertAfterRecord()
   counter = 1
   myString = ""

   scan tcDet:

      if tcDet.impact = tcMas.impact and counter=2
        then  myString = myString+ ", " +tcDet.counties
      endif

      if tcDet.Impact = tcMas.Impact and counter=1
        then  myString = tcDet.counties
        counter = 2
      endif

   endscan

        tcTrash.impact = tcMas.impact
        tcTrash.YearNo = string(tcMas.year) + "-" + string(int(tcMas.number))
        tcTrash.DirAcre = format("W10.3",tcMas.DirectImpactAcres)
        tcTrash.IndAcre = format("W10.3",tcMas.IndirectImpactAcres)
        tcTrash.AcPres = tcMas.AcPres
        tcTrash.AcCreate = tcMas.AcCreate
        tcTrash.AcRes = tcMas.AcRes
        tcTrash.CrPres = tcMas.CrPres
        tcTrash.CrCreate = tcMas.CrCreate
        tcTrash.Take = tcMas.Take
        tcTrash.Auth = tcMas.Auth
        tcTrash.Actual = tcMas.Actual
        tcTrash.MitSite = tcMas.MitSite
        tcTrash.Counties = string(myString)
        tcTrash.TakeNotes = tcMas.TakeNotes
        tcTrash.postRecord()

endscan

tcTrash.endEdit()
tcTrash.close()

;Create spreadsheet
if not exportSpreadSheet(destTbl, getAliasPath(":PRIV:") + "\\" + spp + ".WB3") then
    errorShow()
   return
endif

endIf

endMethod