SQL::ResultSetToCSV Method
Syntax
dim result as C = ToCSV([IncludeHeaderRow as L = .f. [, RowsToCopy as N = -1[, StartRow as N = -1 [, UpperCaseNames as L = .f. [, DateTimeFormat as C = "" [, DateFormat as C = "" [, TextToSubstituteForBinary as C = "<Binary Data>"]]]]]]]])
Arguments
- IncludeHeaderRowLogical
Default = .f.. If true, the first row in the result will be the column names.
- RowsToCopyNumeric
Default = -1 (copy all rows). The number of rows to copy.
- StartRowNumeric
Default = -1 (start with next row in the result set). The starting row.
- UpperCaseNamesLogical
Default = .f.. If true and the header row is included, the header row column names will be converted to uppercase.
- DateTimeFormatCharacter
Default = "". The date time format used to format date time fields in the CSV data. See Date and Time Format Elements for more information about date time formats.
- DateFormatCharacter
Default = "". The date format used to format date fields in the CSV data. See Date and Time Format Elements for more information about date time formats.
- TextToSubstituteForBinaryCharacter
Default = "<Binary Data>". A text string used in place of binary data in the CSV file.
Returns
- resultCharacter
Returns the result set in CSV (comma separated variable) format.
Description
Convert a ResultSet to Comma Separated Variable format.
This function is useful in displaying the result of a query against a database. For example, after opening a database, like Northwind, you can use the Execute() function to perform a query. You can then use the toCSV() function to delimitate all of the results from the query using a comma.
dim cn as sql::Connection
? cn.Open("::Name::AADemo-Northwind")
= .T.
?cn.Execute("select * from customers where country='Germany'")
= .T.
? cn.ResultSet.ToCSV()
= ALFKI,Alfreds Futterkiste,Maria Anders,Sales Representative,Obere Str. 57,Berlin,,12209,Germany,030-0074321,030-0076545
BLAUS,Blauer See Delikatessen,Hanna Moos,Sales Representative,Forsterstr. 57,Mannheim,,68306,Germany,0621-08460,0621-08924
DRACD,Drachenblut Delikatessen,Sven Ottlieb,Order Administrator,Walserweg 21,Aachen,,52066,Germany,0241-039123,0241-059428
FRANK,Frankenversand,Peter Franken,Marketing Manager,Berliner Platz 43,München,,80805,Germany,089-0877310,089-0877451
KOENE,Königlich Essen,Philip Cramer,Sales Associate,Maubelstr. 90,Brandenburg,,14776,Germany,0555-09876,
LEHMS,Lehmanns Marktstand,Renate Messner,Sales Representative,Magazinweg 7,Frankfurt a.M.,,60528,Germany,069-0245984,069-0245874
MORGK,Morgenstern Gesundkost,Alexander Feuer,Marketing Assistant,Heerstr. 22,Leipzig,,04179,Germany,0342-023176,
OTTIK,Ottilies Käseladen,Henriette Pfalzheim,Owner,Mehrheimerstr. 369,Köln,,50739,Germany,0221-0644327,0221-0765721
QUICK,QUICK-Stop,Horst Kloss,Accounting Manager,Taucherstraße 10,Cunewalde,,01307,Germany,0372-035188,
TOMSP,Toms Spezialitäten,Karin Josephs,Marketing Manager,Luisenstr. 48,Münster,,44087,Germany,0251-031259,0251-035695
WANDK,Die Wandernde Kuh,Rita Müller,Sales Representative,Adenauerallee 900,Stuttgart,,70563,Germany,0711-020361,0711-035428
?cn.Execute("select * from customers where country='USA'")
= .T.
? cn.ResultSet.ToCSV(.t.)
= CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax
GREAL,Great Lakes Food Market,Howard Snyder,Marketing Manager,2732 Baker Blvd.,Eugene,OR,97403,USA,(503) 555-7555,
HUNGC,Hungry Coyote Import Store,Yoshi Latimer,Sales Representative,City Center Plaza 516 Main St.,Elgin,OR,97827,USA,(503) 555-6874,(503) 555-2376
LAZYK,Lazy K Kountry Store,John Steel,Marketing Manager,12 Orchestra Terrace,Walla Walla,WA,99362,USA,(509) 555-7969,(509) 555-6221
LETSS,Let's Stop N Shop,Jaime Yorres,Owner,87 Polk St. Suite 5,San Francisco,CA,94117,USA,(415) 555-5938,
LONEP,Lonesome Pine Restaurant,Fran Wilson,Sales Manager,89 Chiaroscuro Rd.,Portland,OR,97219,USA,(503) 555-9573,(503) 555-9646
OLDWO,Old World Delicatessen,Rene Phillips,Sales Representative,2743 Bering St.,Anchorage,AK,99508,USA,(907) 555-7584,(907) 555-2880
RATTC,Rattlesnake Canyon Grocery,Paula Wilson,Assistant Sales Representative,2817 Milton Dr.,Albuquerque,NM,87110,USA,(505) 555-5939,(505) 555-3620
SAVEA,Save-a-lot Markets,Jose Pavarotti,Sales Representative,187 Suffolk Ln.,Boise,ID,83720,USA,(208) 555-8097,
SPLIR,Split Rail Beer & Ale,Art Braunschweiger,Sales Manager,P.O. Box 555,Lander,WY,82520,USA,(307) 555-4680,(307) 555-6525
THEBI,The Big Cheese,Liz Nixon,Marketing Manager,89 Jefferson Way Suite 2,Portland,OR,97201,USA,(503) 555-3612,
THECR,The Cracker Box,Liu Wong,Marketing Assistant,55 Grizzly Peak Rd.,Butte,MT,59801,USA,(406) 555-5834,(406) 555-8083
TRAIH,Trail's Head Gourmet Provisioners,Helvetius Nagy,Sales Associate,722 DaVinci Blvd.,Kirkland,WA,98034,USA,(206) 555-8257,(206) 555-2174
WHITC,White Clover Markets,Karl Jablonski,Owner,305 - 14th Ave. S. Suite 3B,Seattle,WA,98128,USA,(206) 555-4112,(206) 555-4115