none
Exporting data from FoxPro to Excel RRS feed

  • Question

  • I work for the NS Provincial Government and currently use Visual FoxPro 9.

    Going forward, our Help Desk will no longer be supporting this program. We are also shifting to new software for our Correspondence Tracking System (CTS).

    My question is:  How do I transfer data from FoxPro to a useable (searchable) program like Excel or Word.

    When I open the program, there are no features referring to exporting.  The only thing that I can produce is a report of outstanding correspondence.  I think that I need to know how to 'get in through a back door'.

    Can anyone help me?

    Regards,

    Joanne Bolger

    Friday, May 10, 2019 10:56 AM

Answers

  • Hi,

    There is a command in Foxpro to copy data from VFP table to Excel file but it is having some limitations of MEMO field will not get transferred to excel file and limitation of records get exported to excel file (can export a maximum of 65,535 rows)

    Extract from VFP 9 Help

    COPY TO FileName [DATABASE DatabaseName [NAME LongTableName]] [FIELDS FieldList | FIELDS LIKE Skeleton | FIELDS EXCEPT Skeleton] [Scope] [FOR lExpression1] [WHILE lExpression2] [ [WITH] CDX ] | [ [WITH] PRODUCTION ] [NOOPTIMIZE] [ [TYPE] [ FOXPLUS | FOX2X | DIF | MOD | SDF | SYLK | WK1 | WKS | WR1 | WRK | CSV | XLS | XL5 | DELIMITED [ WITH Delimiter | WITH BLANK | WITH TAB | WITH CHARACTER Delimiter ] ] ] [AS nCodePage]

    It is better to go for customized package to export it to Excel.

    - If you want to Only view/Search the data you can use Foxpro itself or application like DBFVIEWER

    - You can also migrate VFP data to different database like MS-SQL, MYSQL, Postgres so that you can query data.


    Friday, May 10, 2019 12:27 PM
  • Hi,

    There is a command in Foxpro to copy data from VFP table to Excel file but it is having some limitations of MEMO field will not get transferred to excel file and limitation of records get exported to excel file (can export a maximum of 65,535 rows)

    The limitation will depend of the Excel version you have installed

    Each version have a maximum lines per sheet, and that will determine the maximum lines to be exported

    You can always export it as CSV with no maximum lines limited and open it from Excel

    Friday, May 10, 2019 3:14 PM
    Moderator
  • Since your actual need isn't to get Excel files but the data, a Foxpro Application will either store it's data in separate DBF (table) data files.

    Unless these tables are encrypted you have all data in these files and access to it just needs Foxpro or at least the VFP OLEDB Provider and programming skills. Not exports and surely not exports to formats really bad for data transfer.

    You're talking of corrrespondence tracking and it seems to me all you really need to transfer are 

    a) current correspondence to be able to continue it until some final stage

    b) old correspondence only for reference.

    I second Tamar, it's a data migration job to transfer this data to a target system and that'll require knowledge about the structuring of the data in the old and new system and translation of the data in terms of transformation. Even the same topic and domain of data like shop order/sales can be stored in gazillian of different ways and database of system A can have table structures storing aspects of the data System B has not foreseen, so you caould have a loss of details.

    The usual strategy is to cover a) by finishing such correspondence with the old system, then b) isn't even necessarily moving any data into the format of a new system but you just keep it in some searchable form. Even if t's a requirement to purge VFP and VFP runtimes from your systems as laws or regulations demand that, you'd be better off with at least a 1:1 transferring of the data as it's strucutred now into a MSSQL backend or whatever is allowed database server and have a query tool to get at whatever back reference you need.

    In very short summary you're likely having no idea what relational databases are and how data is stored in a relational way and normalized structure and that it doesn't simply translate to one major list, even if the old VFP system mainly displayed a list of correspondence to you.

    To illustrate that with eg mails of a mail client, which really is mainly just a list of the mail texts with all its attributes from sender to recipient, subject, date, etc. Several mails will belong to one "conversation" related by being replies to each other. That kind of links are not really covered by an Excel sheet. So think twice before you go such a route of export of data in inferior formats not capable to give you even such simple features as grouping mails belonging to the same conversation.

    Bye, Olaf.

    Tuesday, May 14, 2019 5:00 AM
  • Your screenshot dowsn't show, it's complicated to get the feature to post images here in this forum.

    But from what you describe this isn't FoxPro, it's likely just an application written in FoxPro and of course that doesn't give you all the options the development environment and the language has.

    The developer of that application might nocht even have changed the main window title bar caption from "Microsoft Visual Foxpro" to somthing ekse like the application title and therefore you think this is FoxPro.

    Let me show how the FoxPro environment looks like:

    And this is just the bare minimum with VFP system menu, standard toolbar, command window (to immediately execute commands) and no project file open and none of the several tool windows you find in the menu and toolbar or other toolbars not added, as they only are necessary while designing forms or classes or reports, etc. etc. VFP transforms it's menu and toolbars depending on what you do, but even the bare minium is more than you describe and has nothing sepcialised such as letters.

    So if your window only has the menu items you tell, that's not FoxPro.

    What you likely will have is a folder with data, DBF files for sure - and as your menu items allow reindex, pack and especially pack memo - also CDX (index) and FPT (memo files). These three will come in bundles and are your data files.

    It's not straight forward to get at this data without Foxpro itself, but maybe what you start is indeed VFP, but just by configuration starting a _startup program, which replaces the system menu with the application menu.

    Do you have an executable named VFPN.EXE where N might be any digit from 3 to 9? (It should be 9, when you say it is VFP9, but as you see the standard development environment of VFP doesn't tell the version number)

    If so, you might get far more out of this, because quite likely someone who doesn't even set the screen caption also doesn't know how to build an EXE and a setup and just installs VFP itself with his programs and forms for simplicity, as he manages to start that like he does during development. That'd be against the end user license of VFP, but not your fault.

    So tell us, the name of the EXE you start, you likely have a Windows start menu item or a shortcut. Instead of double clicking it, click on it with the right mouse key and find (depending on what) properties or an option to open the folder with the application. That'll tell us more about what you really have there,

    Bye, Olaf.

    PS: last not least: If you don't have VFP itself, you still can get at the data itself because things like the VFP OLEDB provider are free to get and can be used to query data. It doesn't work standalone, it at least needs a script written in some language, but even without any programming environment installed, you have powershell to run something, for example. It's too early to hand out some code before we know what you really have.

    Friday, May 24, 2019 1:18 PM
  • It seems to me that whoever is implementing your new system should have the responsibility for transferring your existing data to it. Have you asked them to do so?

    Tamar

    • Proposed as answer by Koen-P Wednesday, May 15, 2019 11:39 AM
    • Marked as answer by Tom BorgmannEditor Wednesday, July 31, 2019 6:11 AM
    Monday, May 13, 2019 8:12 PM
    Moderator

All replies

  • Hi,

    There is a command in Foxpro to copy data from VFP table to Excel file but it is having some limitations of MEMO field will not get transferred to excel file and limitation of records get exported to excel file (can export a maximum of 65,535 rows)

    Extract from VFP 9 Help

    COPY TO FileName [DATABASE DatabaseName [NAME LongTableName]] [FIELDS FieldList | FIELDS LIKE Skeleton | FIELDS EXCEPT Skeleton] [Scope] [FOR lExpression1] [WHILE lExpression2] [ [WITH] CDX ] | [ [WITH] PRODUCTION ] [NOOPTIMIZE] [ [TYPE] [ FOXPLUS | FOX2X | DIF | MOD | SDF | SYLK | WK1 | WKS | WR1 | WRK | CSV | XLS | XL5 | DELIMITED [ WITH Delimiter | WITH BLANK | WITH TAB | WITH CHARACTER Delimiter ] ] ] [AS nCodePage]

    It is better to go for customized package to export it to Excel.

    - If you want to Only view/Search the data you can use Foxpro itself or application like DBFVIEWER

    - You can also migrate VFP data to different database like MS-SQL, MYSQL, Postgres so that you can query data.


    Friday, May 10, 2019 12:27 PM
  • Hi,

    There is a command in Foxpro to copy data from VFP table to Excel file but it is having some limitations of MEMO field will not get transferred to excel file and limitation of records get exported to excel file (can export a maximum of 65,535 rows)

    The limitation will depend of the Excel version you have installed

    Each version have a maximum lines per sheet, and that will determine the maximum lines to be exported

    You can always export it as CSV with no maximum lines limited and open it from Excel

    Friday, May 10, 2019 3:14 PM
    Moderator
  • You can always export it as CSV with no maximum lines limited and open it from Excel

    But MEMO field will not get transferred to CSV file.
    Monday, May 13, 2019 7:21 AM
  • It seems to me that whoever is implementing your new system should have the responsibility for transferring your existing data to it. Have you asked them to do so?

    Tamar

    • Proposed as answer by Koen-P Wednesday, May 15, 2019 11:39 AM
    • Marked as answer by Tom BorgmannEditor Wednesday, July 31, 2019 6:11 AM
    Monday, May 13, 2019 8:12 PM
    Moderator
  • Since your actual need isn't to get Excel files but the data, a Foxpro Application will either store it's data in separate DBF (table) data files.

    Unless these tables are encrypted you have all data in these files and access to it just needs Foxpro or at least the VFP OLEDB Provider and programming skills. Not exports and surely not exports to formats really bad for data transfer.

    You're talking of corrrespondence tracking and it seems to me all you really need to transfer are 

    a) current correspondence to be able to continue it until some final stage

    b) old correspondence only for reference.

    I second Tamar, it's a data migration job to transfer this data to a target system and that'll require knowledge about the structuring of the data in the old and new system and translation of the data in terms of transformation. Even the same topic and domain of data like shop order/sales can be stored in gazillian of different ways and database of system A can have table structures storing aspects of the data System B has not foreseen, so you caould have a loss of details.

    The usual strategy is to cover a) by finishing such correspondence with the old system, then b) isn't even necessarily moving any data into the format of a new system but you just keep it in some searchable form. Even if t's a requirement to purge VFP and VFP runtimes from your systems as laws or regulations demand that, you'd be better off with at least a 1:1 transferring of the data as it's strucutred now into a MSSQL backend or whatever is allowed database server and have a query tool to get at whatever back reference you need.

    In very short summary you're likely having no idea what relational databases are and how data is stored in a relational way and normalized structure and that it doesn't simply translate to one major list, even if the old VFP system mainly displayed a list of correspondence to you.

    To illustrate that with eg mails of a mail client, which really is mainly just a list of the mail texts with all its attributes from sender to recipient, subject, date, etc. Several mails will belong to one "conversation" related by being replies to each other. That kind of links are not really covered by an Excel sheet. So think twice before you go such a route of export of data in inferior formats not capable to give you even such simple features as grouping mails belonging to the same conversation.

    Bye, Olaf.

    Tuesday, May 14, 2019 5:00 AM
  • HI,

    If you want to export to Excel, including the memo fields you are advised to make use of Vilhelm's procedure

    Vilhelm blog

    Koen

    Wednesday, May 15, 2019 2:33 PM
  • May 24 - thank you everyone for your replies.  Part of my issue is that I do not have any options to export the data.  This is a screen print of what I see when I open FoxPro:

    Under "File", the only options are "Open Letters and Addresses" and "Exit".  Under Maintenance, the only options are 'Reindex Files', Pack Data Files' and 'Pack Memo Files'  The rest of the options are greyed out.  I have tried to all of the options that are not greyed out, but the system crashes on me when I try to do any of them.  Under "Reports" my only options are "Outstanding Letters".

    What I think that I need is to have the steps on how to go into the back door of the program and find the data files so I can export them in some way (doesn't need to be to Excel, but we need to be able to search historical pieces of correspondence).  I have asked my IT department, but since they no longer support FoxPro, they can not offer any assistance.

    If it would be easier to call me to discuss, I can be reached at 1-902-424-4121.

    Joanne

    Friday, May 24, 2019 12:11 PM
  • Your screenshot dowsn't show, it's complicated to get the feature to post images here in this forum.

    But from what you describe this isn't FoxPro, it's likely just an application written in FoxPro and of course that doesn't give you all the options the development environment and the language has.

    The developer of that application might nocht even have changed the main window title bar caption from "Microsoft Visual Foxpro" to somthing ekse like the application title and therefore you think this is FoxPro.

    Let me show how the FoxPro environment looks like:

    And this is just the bare minimum with VFP system menu, standard toolbar, command window (to immediately execute commands) and no project file open and none of the several tool windows you find in the menu and toolbar or other toolbars not added, as they only are necessary while designing forms or classes or reports, etc. etc. VFP transforms it's menu and toolbars depending on what you do, but even the bare minium is more than you describe and has nothing sepcialised such as letters.

    So if your window only has the menu items you tell, that's not FoxPro.

    What you likely will have is a folder with data, DBF files for sure - and as your menu items allow reindex, pack and especially pack memo - also CDX (index) and FPT (memo files). These three will come in bundles and are your data files.

    It's not straight forward to get at this data without Foxpro itself, but maybe what you start is indeed VFP, but just by configuration starting a _startup program, which replaces the system menu with the application menu.

    Do you have an executable named VFPN.EXE where N might be any digit from 3 to 9? (It should be 9, when you say it is VFP9, but as you see the standard development environment of VFP doesn't tell the version number)

    If so, you might get far more out of this, because quite likely someone who doesn't even set the screen caption also doesn't know how to build an EXE and a setup and just installs VFP itself with his programs and forms for simplicity, as he manages to start that like he does during development. That'd be against the end user license of VFP, but not your fault.

    So tell us, the name of the EXE you start, you likely have a Windows start menu item or a shortcut. Instead of double clicking it, click on it with the right mouse key and find (depending on what) properties or an option to open the folder with the application. That'll tell us more about what you really have there,

    Bye, Olaf.

    PS: last not least: If you don't have VFP itself, you still can get at the data itself because things like the VFP OLEDB provider are free to get and can be used to query data. It doesn't work standalone, it at least needs a script written in some language, but even without any programming environment installed, you have powershell to run something, for example. It's too early to hand out some code before we know what you really have.

    Friday, May 24, 2019 1:18 PM