CONTEST:
1.Task for the laboratory work.
2.Theory part.
Build-in FoxProw commands
OLE
Practical usage of data of data transfer.
Technical facilities & literature used.
Applications
1. Task for the laboratory work.
The aim of this work is to show our ability to work with different
Microsoft applications & to produce the data exchange between them, usingbuild-in commands, OLE instruments & other possible facilities.
In the practical examples we will show the data transfer process using thefollowing Microsoft Applications: FoxPro, Excel & Word.
Also it is necessary to present the theory about the data transfer for itis impossible to use all of them in the examples.
2. Theory part.
There are several different ways to transfer data from FoxPro to different
Microsoft Applications & vice versa.
1. Built-in FoxPro commands.
Operator can use several built-in commands of FoxPro such as Export,
Import, Copy and Append from.
Now we would like to present some helpful information about theseoperands.
The format of EXPORT is:
EXPORT TO
[FIELDS]
[]
[FOR]
[WHILE]
[NOOPTIMIZE]
[[TYPE] DIF | MOD | SYLK | WK1 | WKS | WR1 | WRK | XLS] ]
Remarks
Use EXPORT to use FoxPro data in other software packages.
If the table/.DBF you are exporting from is indexed, the new file createdis created in the indexed order.
Clauses
Include the name of the file to which FoxPro exports data. If you do notinclude an extension with the file name, the default extension for thespecified file type is assigned.
FIELDS
Include FIELDS to specify which fields are copied to the newfile. If the FIELDS clause is omitted, all fields are copied to the newfile. Memo and general fields are not copied to the new file even if theirnames are included in the field list.
The scope clauses are: ALL, NEXT, RECORD, and REST. Theseare explained in the Overview of the FoxPro Language chapter in the FoxPro
Language Reference. Commands which include operate only on thetable/.DBF in the active work area.
You can specify a scope of records to copy to the new file. Only therecords that fall within the range of records specified by the scope arecopied to the new file.
The default scope for EXPORT is ALL records.
FOR
If the FOR clause is included, only records that satisfy the logicalcondition are copied to the new file. Including the FOR clauselets you conditionally copy records, filtering out undesired records.
Rushmore optimizes an EXPORT ... FOR command if is anoptimizable expression. For best performance, use an optimizableexpression in the FOR clause. A discussion of Rushmore optimizableexpressions appears in the Optimizing Your Application chapter in the
FoxPro Developer's Guide.
WHILE
If the WHILE clause is included, records are copied to the new file for aslong as the logical expression evaluates to true (. T.).
NOOPTIMIZE
To cause Rushmore not to optimize EXPORT, include NOOPTIMIZE. For moreinformation, see SET OPTIMIZE and the Rushmore discussion in the Optimizing
Your Application chapter of the FoxPro Developer's Guide
.
TYPE
You must specify the type of file to be created. The TYPE key word isoptional, but you must specify one of the following file types.
DIF
When you specify DIF, each field from a FoxPro table/.DBF becomes a vector
(column) and each record becomes a tuple (row) in a DIF (Data Interchange
Format) file, used by VisiCalc. The new file name is assigned a. DIFextension if an extension isn't included in.
MOD
Use the MOD clause to export to a file in Microsoft Multiplan version 4.01
MOD format. The new file name is assigned an. MOD extension if you don'tinclude an extension in.
SYLK
A SYLK file is a Symbolic Link interchange format (used by Microsoft
Multiplan) in which each field from a FoxPro table/.DBF becomes a column inthe spreadsheet and each record becomes a row. By default, SYLK file nameshave no extension.
WK1
Include this option to create a Lotus 1-2-3 spreadsheet from a FoxProtable/.DBF. A. WK1 extension is assigned to the spreadsheet file name foruse with Lotus 1-2-3 revision 2.x. Each field from the table/.DBF becomesa column in the new spreadsheet, and each record in the table/.DBF becomesa spreadsheet row.
WKS
A Lotus 1-2-3 spreadsheet can be created from a FoxPro table/.DBF with thisoption. A. WKS extension is assigned to the spreadsheet file name for usewith Lotus 1-2-3 revision 1-A. Each field from the table/.DBF becomes acolumn in the new spreadsheet, and each record becomes a row in thespreadsheet.
WR1
Include this option to create a Lotus Symphony spreadsheet from a FoxProtable/.DBF. A. WR1 extension is assigned to the spreadsheet for use with
Symphony version 1.01. Each field from the table/.DBF becomes a column inthe new spreadsheet, and each record in the table/.DBF becomes a row in thespreadsheet.
WRK
A Lotus Symphony spreadsheet can be created from a FoxPro table/.DBF withthis option. A. WRK extension is assigned to the spreadsheet file name foruse with Symphony version 1.10. Each field from the table/.DBF becomes acolumn in the new spreadsheet, and each record in the table/.DBF becomes arow in the spreadsheet.
XLS
Include XLS to create a spreadsheet you can use in Microsoft Excel. Eachfield in the + selected table/.DBF becomes a column in the spreadsheet; eachtable/.DBF record becomes a row in the spreadsheet. An. XLS file nameextension is assigned to the newly created spreadsheet file unless youspecify a different extension.
The format of IMPORT is:
IMPORT FROM
[TYPE]
FW2 | MOD | PDOX | RPD | WK1 | WK3 | WKS | WR1 | WRK | XLS
As it shown in format (TYPE) there are many possible Microsoftapplications to import from to FoxPro but the meaning of these properties,description of which you can see in the previous chapter, that's why it'snot necessary to list all of them again. So we will mention those mostcommon used.
Remarks
Most software packages store their data in a file format that cannot beopened directly in FoxPro. IMPORT creates a new FoxPro table/.DBF fromdata stored in file formats that FoxPro cannot read.
A new table/.DBF is created with the same name as the file the data isimported from. A. DBF extension is assigned to the newly createdtable/.DBF.
Clauses
is the name of the file to import data from. If you don't includean extension with the file name, the default extension for the specifiedfile type is assumed.
TYPE
The key word TYPE is optional, but you must include one of the followingfile types described below.
PDOX
Include PDOX to import Paradox files. Database files in Paradox versions
3.5 and 4.0 by Borland can be imported by including the PDOX option.
XLS
Include XLS to import data from Microsoft Excel spreadsheets versions 2.0,
3.0 and 4.0. Columns from the spreadsheet become fields in the table/.DBF,and the spreadsheet rows become records in the table/.DBF. Spreadsheetfiles created in Microsoft Excel have an. XLS extension.
The format of COPY is:
COPY TO
[FIELDS]
[]
[FOR]
[WHILE]
[[WITH] CDX] | [[WITH] PRODUCTION]
[ NOOPTIMIZE]
[[TYPE] [FOXPLUS | DIF | MOD | SDF | SYLK | WK1 | WKS | WR1 | WRK |
XLS |
DELIMITED [WITH WITH BLANK | WITH TAB ]]]
Remarks
If an index order is set, records are copied in master index order.
Clauses
The name of the new file to which COPY TO copies data is specified with
. If you do not include an extension with the file name, the defaultextension for the specified file type is assigned. If you do not specify afile type, COPY TO creates a new table/.DBF and assigns the table/.DBF filename the default extension. DBF.
FIELDS
If you include FIELDS and a field list, you can specify which fields arecopied to the new file. If the FIELDS clause is omitted, all fields arecopied to the file. If the file you are creating is not a database, memofields aren't copied to the new file even if memo field names are includedin the field list.
The scope clauses are: ALL, NEXT, RECORD, and REST. Theseare explained in the Overview of the FoxPro Language chapter in the FoxPro
Language Reference. Commands which include operate only on thetable/.DBF in the active work area.
You can specify a scope of records copied to a file. Only the records thatfall within the range of records specified by the scope are copied.
The default scope for COPY TO is ALL records.
FOR
If the FOR clause is included, only the records for which thelogical condition evaluates to true (. T.) are copied to the file.
Include FOR to conditionally copy records, filtering out undesiredrecords.
Rushmore optimizes COPY TO with a FOR clause if is anoptimizable expression. For best performance, use an optimizableexpression in the FOR clause. A discussion of Rushmore optimizableexpressions appears in the Optimizing Your Application chapter in the
FoxPro Developer's Guide.
WHILE
If WHILE is included, records are copied as long as the logicalexpression evaluates to true (. T.).
[WITH] CDX | [WITH] PRODUCTION
If the table/.DBF you copy from has a structural index file, you can createa structural index file for the new table/.DBF. Including CDX or
PRODUCTION creates an identical structural index file for the newtable/.DBF. The tags and index expressions from the original structuralindex file are copied to the new structural index file. The CDX and
PRODUCTION clauses have the same effect.
Do not include CDX or PRODUCTION if you are copying to a file other than anew FoxPro table/.DBF.
NOOPTIMIZE
Include NOOPTIMIZE to cause Rushmore to not optimize COPY TO. For moreinformation, see SET OPTIMIZE or consult the discussion of Rushmoreoptimization in the Optimizing Your Application chapter in the FoxPro
Developer's Guide.
TYPE
If the file you are creating isn't a FoxPro table/.DBF, you must specifyits file type. Although you must specify a file type, you need not includethe key word TYPE. You can create a wide variety of different file typesincluding DELIMITED ASCII text files in which you can specify a fielddelimiter.
FOXPLUS
FoxPro memo files have a different structure than FoxBASE + memo files. Ifyour source FoxPro table/.DBF contains a memo field, include the FOXPLUSclause to create a table/.DBF that can be used in FoxBASE +. The FoxPromemo field cannot contain binary data because FoxBASE + does not supportbinary data in memo fields.
SDF
An SDF (System Data Format) file is an ASCII text file in which recordshave a fixed length and end with a carriage return and line feed. Fieldsaren't delimited. The SDF file name is assigned a. TXT file extension ifyou do not include an extension.
SYLK
A SYLK file is a Symbolic Link interchange format (used in Microsoft
MultiPlan) in which fields from the FoxPro table/.DBF become columns in thespreadsheet and records become rows. SYLK file names have no extension.
DELIMITED [WITH | WITH BLANK | WITH TAB]
A DELIMITED file is an ASCII text file in which each record ends with acarriage return and line feed. The default field separator is a comma.
Since character data may include commas, character fields are additionallydelimited with double quotation marks.
In the following example there are 2 character fields ( "Smith" and
"TELEPHONE") delimited with double quotation marks. There is one numericfield which is not delimited since numeric data does not contain commas.
The 3 fields are separated with commas.
"Smith", 9999999, "TELEPHONE"
The DELIMITED WITH option can be used to replace the doublequotation marks with one of your choice.
The comma is not truly a delimiter. However, the DELIMITED WITH BLANK or
DELIMITED WITH TAB clauses allow you to replace the comma field separatorwith either a space or a tab. The data should not contain embedded spacesor tabs.
You cannot combine the WITH clause with either the WITH BLANKor WITH TAB clauses.
Unless you specify otherwise, a. TXT extension is assigned to all newlycreated DELIMITED files.
The format of APPEND is:
APPEND FROM |?
[FIELDS]
[FOR]
[[TYPE] [DELIMITED [WITH TAB | WITH | WITH BLANK] |
DIF | FW2 | MOD | PDOX | RPD | SDF | SYLK | WK1 | WK3 | WKS |
WR1 | WRK | XLS]]
Remarks
The file you are appending from is assumed to be a FoxPro table with a. DBFextension. If the file you want to append from is a FoxPro table anddoesn't have a. DBF extension, you must specify its extension. If the fileis a not a FoxPro table, you must specify the type of file you append from.
Before you can append from a table created in dBASE IV that contains a memofield, you must first open the table in FoxPro with USE. You are promptedwith "Convert MEMO file to FoxPro Format?" Choose Yes.
If you append from a FoxPro table, the table you append from can be open inanother work area. You can also append from a table that isn't open but isavailable on disk and a shared table opened when SET EXCLUSIVE is OFF.
When the table you append from contains records marked for deletion, therecords are not marked for deletion after they are appended.
If you include the? clause instead of including a table name, the Opendialog appears so you can choose a table to append from.
Clauses
Specify the name of the file to append from with. If you don'tinclude a file name extension, the default extension. DBF is assumed.
FIELDS
APPEND FROM supports an optional. Data is only appended tothe fields specified in the field list.
FOR
The entire source file is appended to the table unless you include the FORclause. If the FOR clause is included, a new record is appended for eachrecord in the file source for which evaluates to a logical true
(. T.). Records are appended until the end of the file is reached.
TYPE
If the file you are appending from isn't a FoxPro table, you must specifythe file TYPE. Although you must specify the file type, you need notinclude the key word TYPE. You can append from a wide variety of differentfile types including DELIMITED ASCII text files in which you can specify afield delimiter.
If the file you are appending from doesn't have the usual default fileextension for that type of file, the source file name must include thefile's extension. For example, Microsoft Excel spreadsheets normally havean. XLS file name extension. If the spreadsheet you are appending from hasan extension other than the expected. XLS, be sure to specify theextension.
Caution When appending from a spreadsheet, the data in the spreadsheetmust be stored in a row major order rather than a column major order. Thisallows the appended spreadsheet data to match the table structure.
DELIMITED [WITH TAB | WITH | WITH BLANK]
A DELIMITED file is an ASCII text file in which each record ends with acarriage return and line feed. Field contents are by default assumed to beseparated from each other by commas, and character field values to beadditionally delimited by double quotation marks. For example:
"Smith", 9999999, "TELEPHONE"
The DELIMITED WITH TAB option can be used to specify files which containfields separated from each other by tabs rather than commas. The DELIMITED
WITH option can be used to indicate that character fields aredelimited by a character other than the quotation mark. The DELIMITED WITH
BLANK option can be used to specify files which contain fields separated byspaces instead of commas. The file extension is assumed to be. TXT for alldelimited files.
You can import dates from delimited files if the dates are in proper dateformat. The date format defaults to 'mm/dd/yy'. Including the centuryportion of a date is optional. FoxPro will import a date that includes thecentury. If the century isn't included in a date (for example '12/25/92 '),the Twentieth century is assumed. Date delimiters can be any non-numericcharacter except the delimiter that separates the fields in the delimitedfile.
Dates in other formats can be imported if their format matches a dateformat available in SET DATE. To import dates that are not in the defaultformat, issue SET DATE with the proper date format before using APPEND
FROM. To test if a date format can be successfully imported, use it with
CTOD (). If the date is acceptable to CTOD (), the date will importproperly.
FW2
FW2 files are created by Framework II. FW2 file names are assumed to havea. FW2 extension.
RPD
RPD files are created by RapidFile version 1.2. RPD file names are assumedto have an RPD extension.
SDF
An SDF (System Data Format) file is an ASCII text file in which recordshave a fixed length and end with a carriage return and a line feed. Fieldsare not delimited. The file name extension is assumed to be. TXT for SDFfiles.
WK3
Data from a Lotus 1-2-3 spreadsheet. Each column from the spreadsheetbecomes a field in the table; each spreadsheet row becomes a record in thetable. A. WK3 file name extension is assigned to a spreadsheet created in
Lotus 1-2-3 revision 3.x.
2.2. OLE.
Another method of data exchange between not only FoxPro but between all
Windows based applications is OLE (OBJECT LINKING AND EMBEDDING)
First you can use a FoxPro built in command Append General.
APPEND GENERAL FROM
[LINK]
[CLASS]
APPEND GENERAL is supported in FoxPro for Windows only.
If an OLE object already exists in the general field, it is replaced withthe OLE object from the file.
Clauses
Include to specify the name of the general field the OLEobject is placed in. You can specify a general field in a table open in anoncurrent work area by including the table alias with the field name.
Include to specify the file containing the OLE object. You mustinclude the entire file name, including its extension. If the file islocated in a directory other than the current default directory, includethe path with the file name.
LINK
If LINK is included, a link is created between the OLE object and the filethat contains the object. The OLE object appears in the general field butthe object's definition remains in the file. If LINK is omitted, the OLEobject is embedded in the general field.
CLASS
Include the CLASS clause to specify an OLE class for an OLE object otherthan the default class.
Tip To determine the class for an OLE object, run REGEDIT and double -click on the OLE object. The class name is listed under Identifier.
You can specify a class name when the file extension for the filecontaining the OLE object is different than the default extension and youwant to force the behavior for the class. If the default extension can beused by multiple OLE servers, include the class to specify a particularserver.
3. Practical usage of data transfer.
In applications you can find the examples of the above mentioned commandsusage and their results in different Microsoft Applications (FoxPro, Word &
Excel).
Technical facilities & literature used.
During creating this program the following equipment & software was used:
Pentium 100 MHz PC with 8 Mb RAM
Microsoft Windows'95
Microsoft Visual FoxPro 3.0
Microsoft Word 7.0
Microsoft Excel 7.0
Printer Hewlett Packard LaserJet5L
No special literature but build-in HELP of Visual FoxPro 3.0, Word 7.0,
Excel 7.0 & useful advices was used to create this laboratory work.
5. Applications.
Application 1 "Transfering data form FoxPro to Word & Excel"
Application2 "FoxPro database converted to Excel (. Xls) file"
Application 3 "FoxPro database converted to Word (. sdf) file"
Application 4 "FoxPro base converted in Word file -- in Excel "
Application 5" Excel file in Word "
