Task for the laboratory work
CONTEST:
1.Task for the laboratory work.
2.Theory part.
2.1. Build-in FoxProw commands
2.2. OLE
3. Practical usage of data of data transfer.
4. Technical facilities & literature used.
5. 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, using build-in commands, OLE instruments & other
possible facilities.
In the practical examples we will show the
data transfer process using the following Microsoft Applications: FoxPro, Excel
& Word.
Also it is necessary to present the theory
about the data transfer for it is 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.
2.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 these operands.
The format of EXPORT is:
EXPORT TO <file>
[FIELDS <field list>]
[<scope>]
[FOR <expL1>]
[WHILE <expL2>]
[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 created is created in the indexed order.
Clauses
<file>
Include the name of the file to which
FoxPro exports data. If you do not include an extension with the file name,
the default extension for the specified file type is assigned.
FIELDS <field list>
Include FIELDS <field list> to
specify which fields are copied to the new file. If the FIELDS clause is
omitted, all fields are copied to the new file. Memo and general fields are
not copied to the new file even if their names are included in the field list.
<scope>
The scope clauses are: ALL, NEXT
<expN>, RECORD <expN>, and REST. These are explained in the
Overview of the FoxPro Language chapter in the FoxPro Language Reference.
Commands which include <scope> operate only on the table/.DBF in the
active work area.
You can specify a scope of records to copy
to the new file. Only the records that fall within the range of records
specified by the scope are copied to the new file.
The default scope for EXPORT is ALL
records.
FOR <expL1>
If the FOR clause is included, only records
that satisfy the logical condition <expL1> are copied to the new file.
Including the FOR clause lets you conditionally copy records, filtering out
undesired records.
Rushmore optimizes an EXPORT ... FOR
<expL1> command if <expL1> is an optimizable expression. For best
performance, use an optimizable expression in the FOR clause. A discussion of
Rushmore optimizable expressions appears in the Optimizing Your Application
chapter in the FoxPro Developer's Guide.
WHILE <expL2>
If the WHILE clause is included, records
are copied to the new file for as long as the logical expression <expL2>
evaluates to true (.T.).
NOOPTIMIZE
To cause Rushmore not to optimize EXPORT,
include NOOPTIMIZE. For more information, 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 is optional, 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 .DIF extension if an extension isn't included in
<file>.
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't include an extension in <file>.
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 in the spreadsheet and each record becomes a row.
By default, SYLK file names have no extension.
WK1
Include this option to create a Lotus 1-2-3
spreadsheet from a FoxPro table/.DBF. A .WK1 extension is assigned to the
spreadsheet file name for use with Lotus 1-2-3 revision 2.x. Each field from
the table/.DBF becomes a column in the new spreadsheet, and each record in the
table/.DBF becomes a spreadsheet row.
WKS
A Lotus 1-2-3 spreadsheet can be created
from a FoxPro table/.DBF with this option. A .WKS extension is assigned to the
spreadsheet file name for use with Lotus 1-2-3 revision 1-A. Each field from
the table/.DBF becomes a column in the new spreadsheet, and each record becomes
a row in the spreadsheet.
WR1
Include this option to create a Lotus
Symphony spreadsheet from a FoxPro table/.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 in the new spreadsheet, and each record in the
table/.DBF becomes a row in the spreadsheet.
WRK
A Lotus Symphony spreadsheet can be created
from a FoxPro table/.DBF with this option. A .WRK extension is assigned to the
spreadsheet file name for use with Symphony version 1.10. Each field from the
table/.DBF becomes a column in the new spreadsheet, and each record in the
table/.DBF becomes a row in the spreadsheet.
XLS
Include XLS to create a spreadsheet you can
use in Microsoft Excel. Each field in the +selected table/.DBF becomes a
column in the spreadsheet; each table/.DBF record becomes a row in the
spreadsheet. An .XLS file name extension is assigned to the newly created
spreadsheet file unless you specify a different extension.
The format of IMPORT is:
IMPORT FROM <file>
[TYPE]
FW2 | MOD | PDOX | RPD | WK1 | WK3 | WKS |
WR1 | WRK | XLS
As it shown in format (TYPE) there are many
possible Microsoft applications 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’s not necessary to list all of them again. So we will mention
those most common used.
Remarks
A new table/.DBF is created with the same
name as the file the data is imported from. A .DBF extension is assigned to
the newly created table/.DBF.
Clauses
<file>
<file> is the name of the file to
import data from. If you don't include an extension with the file name, the
default extension for the specified file type is assumed.
TYPE
The key word TYPE is optional, but you must
include one of the following file 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. Spreadsheet files created in Microsoft Excel have an .XLS
extension.
The format of COPY is:
COPY TO <file>
[FIELDS <field list>]
[<scope>]
[FOR <expL1>]
[WHILE <expL2>]
[[WITH] CDX] | [[WITH] PRODUCTION]
[NOOPTIMIZE]
[[TYPE] [FOXPLUS | DIF | MOD | SDF |
SYLK| WK1 | WKS | WR1 | WRK | XLS |
DELIMITED [WITH <delimiter> WITH
BLANK | WITH TAB]]]
Remarks
If an index order is set, records are
copied in master index order.
Clauses
<file>
The name of the new file to which COPY TO
copies data is specified with <file>. If you do not include an extension
with the file name, the default extension for the specified file type is
assigned. If you do not specify a file type, COPY TO creates a new table/.DBF
and assigns the table/.DBF file name the default extension .DBF.
FIELDS <field list>
If you include FIELDS and a field list, you
can specify which fields are copied to the new file. If the FIELDS clause is
omitted, all fields are copied to the file. If the file you are creating is
not a database, memo fields aren't copied to the new file even if memo field
names are included in the field list.
<scope>
The scope clauses are: ALL, NEXT
<expN>, RECORD <expN>, and REST. These are explained in the
Overview of the FoxPro Language chapter in the FoxPro Language Reference.
Commands which include <scope> operate only on the table/.DBF in the active
work area.
You can specify a scope of records copied
to a file. Only the records that fall within the range of records specified by
the scope are copied.
The default scope for COPY TO is ALL
records.
FOR <expL1>
If the FOR <expL1> clause is
included, only the records for which the logical condition <expL1>
evaluates to true (.T.) are copied to the file. Include FOR <expL1> to
conditionally copy records, filtering out undesired records.
Rushmore optimizes COPY TO with a FOR
<expL1> clause if <expL1> is an optimizable expression. For best
performance, use an optimizable expression in the FOR <expL1> clause. A
discussion of Rushmore optimizable expressions appears in the Optimizing Your
Application chapter in the FoxPro Developer's Guide.
WHILE <expL2>
If WHILE <expL2> is included, records
are copied as long as the logical expression <expL2> evaluates to true
(.T.).
[WITH] CDX | [WITH] PRODUCTION
If the table/.DBF you copy from has a
structural index file, you can create a structural index file for the new
table/.DBF. Including CDX or PRODUCTION creates an identical structural index
file for the new table/.DBF. The tags and index expressions from the original
structural index 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 a new FoxPro table/.DBF.
NOOPTIMIZE
Include NOOPTIMIZE to cause Rushmore to not
optimize COPY TO. For more information, see SET OPTIMIZE or consult the
discussion of Rushmore optimization 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 specify its file type. Although you must specify a file
type, you need not include the key word TYPE. You can create a wide variety of
different file types including DELIMITED ASCII text files in which you can
specify a field delimiter.
FOXPLUS
FoxPro memo files have a different
structure than FoxBASE+ memo files. If your source FoxPro table/.DBF contains
a memo field, include the FOXPLUS clause to create a table/.DBF that can be
used in FoxBASE+. The FoxPro memo field cannot contain binary data because
FoxBASE+ does not support binary data in memo fields.
SDF
An SDF (System Data Format) file is an
ASCII text file in which records have a fixed length and end with a carriage
return and line feed. Fields aren't delimited. The SDF file name is assigned
a .TXT file extension if you 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 the spreadsheet and records become rows. SYLK file names
have no extension.
DELIMITED [WITH <delimiter> | WITH
BLANK | WITH TAB]
A DELIMITED file is an ASCII text file in
which each record ends with a carriage return and line feed. The default field
separator is a comma. Since character data may include commas, character
fields are additionally delimited with double quotation marks.
In the following example there are 2
character fields ("Smith" and "TELEPHONE") delimited with
double quotation marks. There is one numeric field which is not delimited
since numeric data does not contain commas. The 3 fields are separated with
commas.
"Smith", 9999999,
"TELEPHONE"
The DELIMITED WITH <delimiter> option
can be used to replace the double quotation 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 separator with either a space or a tab. The data
should not contain embedded spaces or tabs.
You cannot combine the WITH
<delimiter> clause with either the WITH BLANK or WITH TAB clauses.
Unless you specify otherwise, a .TXT
extension is assigned to all newly created DELIMITED files.
The format of APPEND is:
APPEND FROM <file> | ?
[FIELDS <field list>]
[FOR <expL>]
[[TYPE] [DELIMITED [WITH TAB |
WITH <delimiter> | 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 .DBF extension. If the file you want to append
from is a FoxPro table and doesn't have a .DBF extension, you must specify its
extension. If the file is 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 memo field, you must first open the table in FoxPro
with USE. You are prompted with "Convert MEMO file to FoxPro
Format?" Choose Yes.
If you append from a FoxPro table, the
table you append from can be open in another work area. You can also append
from a table that isn't open but is available on disk and a shared table opened
when SET EXCLUSIVE is OFF. When the table you append from contains records
marked for deletion, the records are not marked for deletion after they are
appended.
If you include the ? clause instead of
including a table name, the Open dialog appears so you can choose a table to
append from.
Clauses
Specify the name of the file to append from
with <file>. If you don't include a file name extension, the default
extension .DBF is assumed.
FIELDS <field list>
APPEND FROM supports an optional <field
list>. Data is only appended to the fields specified in the field list.
FOR <expL>
The entire source file is appended to the
table unless you include the FOR clause. If the FOR clause is included, a new
record is appended for each record in the file source for which <expL>
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 specify the file TYPE. Although you must specify the
file type, you need not include the key word TYPE. You can append from a wide
variety of different file types including DELIMITED ASCII text files in which
you can specify a field delimiter.
If the file you are appending from doesn't
have the usual default file extension for that type of file, the source file
name must include the file's extension. For example, Microsoft Excel
spreadsheets normally have an .XLS file name extension. If the spreadsheet you
are appending from has an extension other than the expected .XLS, be sure to
specify the extension.
Caution When appending from a
spreadsheet, the data in the spreadsheet must be stored in a row major order
rather than a column major order. This allows the appended spreadsheet data to
match the table structure.
DELIMITED [WITH TAB | WITH
<delimiter> | WITH BLANK]
A DELIMITED file is an ASCII text file in
which each record ends with a carriage return and line feed. Field contents
are by default assumed to be separated from each other by commas, and character
field values to be additionally delimited by double quotation marks. For
example:
"Smith", 9999999,
"TELEPHONE"
The DELIMITED WITH TAB option can be used
to specify files which contain fields separated from each other by tabs rather
than commas. The DELIMITED WITH <delimiter> option can be used to
indicate that character fields are delimited by a character other than the
quotation mark. The DELIMITED WITH BLANK option can be used to specify files
which contain fields separated by spaces instead of commas. The file extension
is assumed to be .TXT for all delimited files.
You can import dates from delimited files
if the dates are in proper date format. The date format defaults to
'mm/dd/yy'. Including the century portion of a date is optional. FoxPro will
import a date that includes the century. 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-numeric character except the delimiter that separates
the fields in the delimited file.
Dates in other formats can be imported if
their format matches a date format available in SET DATE. To import dates that
are not in the default format, 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 import properly.
FW2
FW2 files are created by Framework II. FW2
file names are assumed to have a .FW2 extension.
RPD
RPD files are created by RapidFile version
1.2. RPD file names are assumed to have an RPD extension.
SDF
An SDF (System Data Format) file is an
ASCII text file in which records have a fixed length and end with a carriage
return and a line feed. Fields are not delimited. The file name extension is
assumed to be .TXT for SDF files.
WK3
Data from a Lotus 1-2-3 spreadsheet. Each
column from the spreadsheet becomes a field in the table; each spreadsheet row
becomes a record in the table. 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 <general field> FROM
<file>
[LINK]
[CLASS <ole class>]
APPEND GENERAL is supported in FoxPro for
Windows only.
If an OLE object already exists in the
general field, it is replaced with the OLE object from the file.
Clauses
<general field>
Include <general field> to specify
the name of the general field the OLE object is placed in. You can specify a
general field in a table open in a noncurrent work area by including the table
alias with the field name.
<file>
Include <file> to specify the file
containing the OLE object. You must include the entire file name, including
its extension. If the file is located in a directory other than the current
default directory, include the path with the file name.
LINK
If LINK is included, a link is created
between the OLE object and the file that contains the object. The OLE object
appears in the general field but the object's definition remains in the file.
If LINK is omitted, the OLE object is embedded in the general field.
CLASS <ole class>
Include the CLASS clause to specify an OLE
class for an OLE object other than 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 file containing the OLE object is different than the default
extension and you want to force the behavior for the class. If the default
extension can be used by multiple OLE servers, include the class to specify a
particular server.
3. Practical usage of data transfer.
In applications you can find the examples
of the above mentioned commands usage and their results in different Microsoft
Applications (FoxPro, Word & Excel).
4. 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”
