none
Format problems converting Excel to DBF

    Question

  • Hi all,
    I have a poorly formated Excel document that I'm trying to export to DBF (more accurately, to import/append from Excel into DBF). My suite numbers and zipcodes are being formatted incorrectly or in some cases are left out completely.

    About the Excel document:
    - The Suite # column contains some numbers and some letters and is formatted as "General".
    - The Zipcode colum contains 5 digits and is formatted as "Numeric" (which is already a problem since a zip code could start with a "0", but that's not my particular problem right now).

    I receive a new version of this file each month, and I can't trust the sender to change the formatting each time. I'm attempting to automate the Excel-to-DBF export, so manually changing the format in Excel and then using Excel's export to DBF function is not an option, although that works beautifully.

    My remaining two options that I'm aware of are:
    1) IMPORT FROM file.xls TYPE XLS
    In this case, some of the suite numbers get formatted as dates, while other values are completely left out of the table. A zip code such as 90006 is somehow evaluated as "7" or something similar, though some other zips like 95420 do evaluate properly.

    2) APPEND FROM file.xls TYPE XLS  (using a pre-formatted "template" dbf with Character-formatted Suite and Zip fields)
    In this case, the suite numbers are also evaluated as dates, even though my template dbf has a Character-formated suite field. And the zipcodes are evaluated as dates as well.

    Is there a way to automate this successfully? Many thanks to anyone who can help. Please let me know if this has already been asked, though I can't find any similar questions.

    Thanks,
    Jennifer
    Wednesday, July 9, 2008 11:05 PM

Answers

All replies