We help IT Professionals succeed at work.

Convert xls to FoxPro dbf?

caseylee
caseylee asked
on
2,425 Views
Last Modified: 2007-12-19
Hi,

I have the data file in both Excel and txt format. I need to convert it into FoxPro dbf format. In Exel XP (on Win2K), there are three options for dbf format: db II, db III and db IV.

For FoxPro, which one should I use? Or should I use something entirely different.

Thanks,
Casey
caseyl@eglobalcall.com
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
Here is some code that is taken from a form that does exactly what you need.  The problem with xls to DBF is that depending on the data fields that are in the XLS the will transform to a large numeric field.  This field will be the days that have passed since 12-31-1899 (the turn of the 19th century).  If you import the xls file and you see dates that appear to be of this nature just derive the true date by adding these days to the date equilvalent of this field

Store CTOD('12/31/1899') + Field to NewDate.  Other than that issue this code is pretty straight forward.

STORE (sys(5) + SYS(2003)) to Hold_Path  && Root
SET default to &Import_Path  && My import Directory
m.FileName  = GetFILE([xls,XLS],'Get XLS Input Data','Select',0,'Generate New Accounts')  && File Dialog
IF !EMPTY(M.FileName)  && Got an xls file
    Set default to &Import_Path  && set path to write
    Store JUSTSTEM(M.FileName) to TheName  && dbf name
    Store 'IMPORT FROM ' + M.FileName + ' TYPE XL8 AS 1252' to Command
    &Command
    Select &TheName
    USE
ENDIF
SET default to &Hold_path && Reset path

Any other questions you can e-mail me at phoenixnational@fcol.com

Thanks

Author

Commented:
Dear Slink9,

The Excell cannot be used to translate to .dbf because it does not transformed the time correctly. It always add the date of 1/1/1900 to the time, no matter what format the time column is.

-Casey
Could you provide a sample XL file.

Commented:
That is a DBF file's DATETIME format.  You can pull the time out of it by doing a TTOC() on the field.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.