Moving data to a new directory

[shawnwmorris]

Hello again everyone,

This is a FoxPro beginner question. I have data that I am pulling from one directory via a select statement, I need to push this data into a table into a different directory. Is there a resource somewhere that would show me how to accomplish this? I can use the cursor and put the data into excel but it doesn't make sense to push the data into excel the try to import it.

 

[Mike Lewis]

Let me get this right. You have data (in DBF files?) in a given directory. You want to end up with the same data (also in DBF files?) in a different directory.

If that right, the simplest way would be just to copy the files (using VFP's COPY FILE command).

But you mentioned a SELECT statement. That suggests that you want to manipulate the data in some way - such as filtering the record or only selecting certain fields or changing the sort order. If so, simply add an INTO TABLE clause to the SELECT. This clause lets you specify a target filename, which can optionally include a directory path.

However, if the tables are part of a database, then it's a little more complicated. In that case, let us know and we will guide you further.

Whatever the situation, it's highly unlikely you would need to use Excel as intermediary.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[shawnwmorris]

The target tables are in a different directory.

So if I understand you correctly:
select field1, field2 from table where field 1 ='xxxxxx' and field 2 ='yyyy' INTO E:\reports\CA.dbf

 

[shawnwmorris]

This works as I need it to. Thanks for the input.

 

[Olaf Doschke]

OK, just a note on this: Creating new tables normally only occurs in theprocess to design and setup a database.

SQL also has solutions to insert data from source table(s) into a destination table already existing.

And I always find it simpler and more flexible to separate the path concerns with the query concerns, so open up dbfs in workareas, then just work on the alias names of the workareas. They will know where the files are.

CD sourcepath
USE tabSource.dbf
CD destpath
USE tabDest.dbf
INSERT INTO tabDest SELECT field1, field2 FROM tabSource WHERE field1 ='xxxxxx' and field2 ='yyyy'

It even becomes simpler without sepcifying paths at all, if the involved DBFs are part of a DBC. Then the DBC is the only file you need to address with full path, which can become configuration data. Then somewhere at startup you'll have:

CD (configpath1)
OPEN DATABASE sourceDB.dbc
CD (configpath2)
OPEN DATABASE destDB.dbc

Now, since databases are opened, you can CD anywhere else of interest, typically a VFP application will point to its own directory to be able to address any file relative to itself. Besides - but off topic here - you can find out all system folder paths, eg look at GETENV() for some of them, find out what SYS(16,0) gives you and PROGRAM() - caution: This may differ within IDE and EXE. within PRG and methods of classes. There's more with WinAPI and several system COM helper objects.

Now you can open DBFs by addressing them with databasename!Tablename, so the earlier code can be reduced to mere SQL:

INSERT INTO destDB!tabDest SELECT field1, field2 FROM sourceDB!tabSource WHERE field1 ='xxxxxx' and field2 ='yyyy'

Even when you change installation, as long as databases have their (unique) name, this query can stay as is, and such code is more ideal than any code with embedded aka "hardcoded" paths. Paths should just be stored in config data or xml or ini and no code should ever contain a concrete path, or you make your application depend on it and hard to move elsewhere.

Bye, Olaf.

 

[Mike Lewis]

So if I understand you correctly:
select field1, field2 from table where field 1 ='xxxxxx' and field 2 ='yyyy' INTO E:\reports\CA.dbf

Yes, that's correct - except that you need to include the keyword TABLE immediately after INTO.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Also notice th INTO clause allows specifying a DATABASE to add the new table to and a long table name. Off topic: Long table names are obsolete as file names nowadays also can be long and not 8.3 DOS names only, but it's also good to know you can give DBFs an DBC internal name differing from the file name, if you ever come across someone else having done so and don't find files.

Also notice the VFP help covers many details about SQL in detail topics, eg this one:

https://technet.microsoft.com/de-de/library/tdd4h271(v=vs.80).aspx

When it comes to playing with data and finaly want to persist a cursor as a dfb file, I also prefer to create a file from a workarea already existing with COPY TO, not by doing one more query into table. COPY TO also allows to creae a free dbf, or a dbc dbf and it allows to decide whether or not to also create cdx files, which INTO TABLE won't do, so using INTO TABLE the new dbf has no indexes. For two reasons:
1. The query can combine whatever data of many source tables and thus existing indexes of them won't necessarily fit in other scenarios than SELECT * FROM singletable.
2. The newly created resultset is a dbf now without any index to be able to copy it.

In the general case workareea might come from CREATE CURSOR, using a view, queries INTO CURSOR with READWRITE clause and you can apply INDEX to it, so finally a COPY TO can copy the workarea indexes to a CDX file, too, while INTO TABLE only copies over data, as the resultset can't have any index yet, even when indexes where used to optimize the query.

And though Mike already began with it, let me end with restating the simplest way still is to copy data by copying files. On top of that the wish to copy data always points out to me you want a data extract and store it somewhere. Why? The main database is there as persinstence of all data. Making copies with certain conditions is creating data, which ages in comparison to original data. The usual way to go about data is only storing it non redundant in 4th normal form in one place, only redundancy allowed are backups, which are there for obvious reasons. But any data extract of months or years data can always be extracted from the original data. The only thing to store is the queries/conditions to be able to repeat the extraction process. I know it is very common to copy out data for export. There is a whole terminology about that, ETL - extract, transform, load, which illustrates an export and import into the next tool of a tool chain, mostly in data warehouseing or data mart as the general target database for an array of BI and other analyzing tools. But in regard to one database and application your main dbc should contain all data and wanting a months data means querying it from there.

Bye, Olaf.