Can I get all fields for all tables in a FoxPro DB into excel (one table per sheet) automatically?

[Alan]

Hi All,

Is there any way to get all tables (and all fields in each table) into
excel automatically without having to manually enter all table names
and field names into SELECT queries?

Ideally, one sheet per table with the sheets named with the table
names, but anything close would be good.

Thanks,

Alan.


--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address

 

[Guest]

It is possible but would require some pretty intense coding which I don't
have time to work out right now. But the key would be using ADO to connect
to the database and then you can use the OpenSchema method to read the names
of the tables. You could iterate through these in a loop, append a worksheet
to your workbook, and then use standard ADO recordset methods to retrieve the
data from each table (SELECT * FROM TABLENAME should be sufficient). You
could get column headers, if desired, from the Field.Name property (iterating
through the fields).

Sorry I can't do all the details, but for info on reading the database
schema see this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthopenschema.asp

If you need info on ADO methods in general:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconintroduction.asp

 

[Cindy Winegarden]

Hi Alan,

Do you know which version of FoxPro your tables were created with? Older Fox
tables (v2.6, for example) can be directly opened in Excel, although you
will not get the text in Memo fields. Some newer tables can be read with
ODBC but tables that have data features added in VFP7, 8, and 9 can only be
read via OLE DB. Both the latest Fox ODBC drivers and OLE DB data provider
are downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates.

By "all tables" I assume you mean all tables in a directory, or possibly all
tables belonging to a "database container" (DBC file - it's metadata about
the tables it "contains"). The DBC file itself is a table and can be opened
as such and read as any other table.

I'd take the same approach as K Dales - that of iterating through a list of
the tables you want to work with, inserting a sheet in your workbook,
reading them by whatever method works and entering it into the workbook.

If I were doing this in Visual FoxPro (VFP9 is the latest version) it would
be really simple.

 

[Alan]

It is possible but would require some pretty intense coding which I
don't have time to work out right now. But the key would be using
ADO to connect to the database and then you can use the OpenSchema
method to read the names of the tables. You could iterate through
these in a loop, append a worksheet to your workbook, and then use
standard ADO recordset methods to retrieve the data from each table
(SELECT * FROM TABLENAME should be sufficient). You could get
column headers, if desired, from the Field.Name property (iterating
through the fields).

Sorry I can't do all the details, but for info on reading the
database schema see this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthopenschema.asp

If you need info on ADO methods in general:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconintroduction.asp

Hi,

Thanks for that.

I will read those links and post back with my results so you know how
I went!

Thanks,

Alan.


--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address

 

[Alan]

Hi Alan,

Do you know which version of FoxPro your tables were created with?
Older Fox tables (v2.6, for example) can be directly opened in
Excel, although you will not get the text in Memo fields. Some newer
tables can be read with ODBC but tables that have data features
added in VFP7, 8, and 9 can only be read via OLE DB. Both the latest
Fox ODBC drivers and OLE DB data provider are downloadable from
http://msdn.microsoft.com/vfoxpro/downloads/updates.


By "all tables" I assume you mean all tables in a directory, or
possibly all tables belonging to a "database container" (DBC file -
it's metadata about the tables it "contains"). The DBC file itself
is a table and can be opened as such and read as any other table.

Hi Cindy,

Thank you for helping with this.

I am not sure what version was used to create the files, but I can
access the tables manually as follows:

There is an ODBC connection of type 'Visual Foxpro Database (DBC file)
so you are spot on there.

As you mention it does appear that this is just a link to the actual
tables which are in separate DBF files (I think).

I'd take the same approach as K Dales - that of iterating through a
list of the tables you want to work with, inserting a sheet in your
workbook, reading them by whatever method works and entering it into
the workbook.

If I were doing this in Visual FoxPro (VFP9 is the latest version)
it would be really simple.

I looks like I am!

Does that mean there is an easy to achieve what I need to do or do you
mean I would need to have VFP9 installed (which I don't)?

Thanks,

Alan.


--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address

 

[Alan]

It is possible but would require some pretty intense coding which I
don't have time to work out right now. But the key would be using
ADO to connect to the database and then you can use the OpenSchema
method to read the names of the tables. You could iterate through
these in a loop, append a worksheet to your workbook, and then use
standard ADO recordset methods to retrieve the data from each table
(SELECT * FROM TABLENAME should be sufficient). You could get
column headers, if desired, from the Field.Name property (iterating
through the fields).

Hi,

In reading the help files, I found this piece of information:

Remote Data Service Usage:
The OpenSchema method is not available on a client-side Connection
object.

Does that mean that, within excel, I cannot use the openscheme method
on a connection object (it can only be used within, say, Access VBA)?

Within the excel object browser, the openschema method is, by defaul,
a 'hidden' method so I am guessing that it won't work?

Thanks,

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address

 

[Cindy Winegarden]

Hi Alan,

I am not sure what version was used to create the files, but I can
access the tables manually as follows:

There is an ODBC connection of type 'Visual Foxpro Database (DBC file)
so you are spot on there.

As you mention it does appear that this is just a link to the actual
tables which are in separate DBF files (I think).

The actual tables come in 3 parts: the DBF file is the table itself, the FPT
file (optional) contains the Memo field data, and the CDX (optional)
contains the indexes.

I looks like I am!

Does that mean there is an easy to achieve what I need to do or do you
mean I would need to have VFP9 installed (which I don't)?

From a program written in VFP and run in the VFP IDE or as an executable the
code would look like this:

*-- Get an array of the DBFs in the directory
*-- Column 1 has file names
ADir(ArrayOfFiles, "*.dbf")

*-- Create the Excel Workbook
oExcel = CreateObject("Excel.Application")
oExcel.Visible = .T. && For testing
oExcel.Workbooks.Add()

*-- Iterate through column 1 of the array
*-- More than one way to do this but here's one
For nCount = 1 To Alen(ArrayOfFiles)
*-- If this element is in column 1
If ASubscript(ArrayOfFiles, nCount, 2) = 1
With oExcel
.Worksheets.Add()
cFileName = Alltrim(ArrayOfFiles(nCount))
Use (cFileName) Shared && Use opens a table
_Vfp.DataToClip(,,3) && Creates tab-delimited text
.ActiveSheet.Name = cFileName
.ActiveSheet.Cells(1, 1).Activate
.ActiveCell.PasteSpecial()
EndWith
EndIf
EndFor

oExcel.SaveAs("MyWorkbook")
oExcel.Quit()
oExcel = .NULL.
Clear All && Release all variables and close all tables