none
Convert MS Excel file to VFP ver9 table (dbf, cdx, fpt) RRS feed

  • Question

  • I am fairly new to the VFP arena but am a fast learner. Please forgive me if this is a stupid question, but is there a way to convert an Excel worksheet to a VFP dbf?  Short story is that our software is run on VFP and our support tech wrote an application to upload data in the background for us. Unfortunatly it has caused more problems than it was worth and we can't seem to get the bad info cleared out.  My thought is that I have enough data prior to the upload that I can reconstruct the tables record to record.  If I did this in Excel, could I then convert that to VFP dbf?

    Thursday, March 20, 2008 6:05 PM

Answers

  • Converting an excel sheet into a VFP table is fairly easy using Ace OLEDB (I suggest not to use Jet, it looks like it's buggy). You could also directly link to VFP data using VFPOLEDB (provided your software designers didn't shut the door).

     

    However, if I'm reading you right, you are thinking to manage data in Excel??? Excel has never been good for data management, think about it twice.

     

    Say you managed the data and converted to VFP tables, then what? Your software might stop working completely. Do you know what type of fields, constraints etc are defined for your software's database?

     

    Upload data, upload from which source? Doing that directly from within VFP with no Excel in between generally ends in a better experience.

     

    Whatever route you take be sure to backup files.

     

    My .02 cents

    Friday, March 21, 2008 11:00 AM

All replies

  • Look at Excel's SAVE AS options. It offers different formats. Save as Type DBF3 (old version), but the DBF it produces is easily read from VFP.

    Thursday, March 20, 2008 6:13 PM
  • I am running Excel 2003 and don't see that as a SAVE AS option.  I do see plain old DBF 2, 3 and 4, but that os the only dbf reference. The other problem I have with saving my files in older versions of Excel is that they can only accomodate 16k records and my files exceed 20k.

    Thursday, March 20, 2008 6:20 PM
  •  KristiBaer wrote:

    I am fairly new to the VFP arena but am a fast learner.

     

    Do yourself a big favor and get a copy of Fundamentals: Building Visual Studio Applications on a Visual FoxPro 6.0 Foundation at http://www.hentzenwerke.com/catalog/fund6.htm. Even though it is a few versions old, the concepts are still the same and this is the best beginners reference I have ever seen.

     

     

    Thursday, March 20, 2008 6:30 PM
  • Don't know the limits on Excel 2003. Files SaveAs-Type-DBF3 is what I was talking about.

     

    If there's a limit (I thought it would be Excel's 66,000 rows or so), then another option is to save as CSV (comma-separated value, a.k.a. comma-delimited file). This is also easy to import into a VFP DBF (see VFP's APPEND FROM)

     

    Thursday, March 20, 2008 6:34 PM
  • I'll play with it and see what i come up with. I have already created one spreadsheet that is ready for conversion, so I'll use that and see what my software thinks of it.

     

    Thanks again,

    Kristi

    Thursday, March 20, 2008 7:36 PM
  • Converting an excel sheet into a VFP table is fairly easy using Ace OLEDB (I suggest not to use Jet, it looks like it's buggy). You could also directly link to VFP data using VFPOLEDB (provided your software designers didn't shut the door).

     

    However, if I'm reading you right, you are thinking to manage data in Excel??? Excel has never been good for data management, think about it twice.

     

    Say you managed the data and converted to VFP tables, then what? Your software might stop working completely. Do you know what type of fields, constraints etc are defined for your software's database?

     

    Upload data, upload from which source? Doing that directly from within VFP with no Excel in between generally ends in a better experience.

     

    Whatever route you take be sure to backup files.

     

    My .02 cents

    Friday, March 21, 2008 11:00 AM