|
DETAILS OF FOXTROT TIPS AND TECHNIQUES FOR USING EXCEL DATA:
OVERVIEW
There are various issues which arise when importing Excel spreadsheets into Foxtrot. There are also guidelines which need to be observed before attempting to import. Since Foxtrot uses the "deBase III" format for its databases, a spreadsheet must be imported into this format before Foxtrot can use it. There are two basic ways to do this: the first is to let Foxtrot import the selected sheet, the second is to use the "Save As" function in Excel.
TO IMPORT USING FOXTROT LE and FoxtrotOne
1. In Foxtrot LE click "Load Database” (or "Open Database") in the Database Center, or select from the Database menu. In FoxtrotOne Select Project from Menu options>then select add>data
2. Select the proper version of Excel in the "Files of Type" box.
3. Locate the desired spreadsheet file (with a name ending in ".xls"). Be sure you are looking in the correct directory.
4. You will see the "Import Spreadsheet" dialog box. On the left is a list of individual sheets contained in this file.
5. Click to highlight the desired sheet from the box on the left.
POSSIBLE ERROR MESSAGES
1. "This file is not in a format Foxtrot can import"
Repeat steps 1-5 above. Select a new name for the table to be created in Step 6. Be sure the "Trim Text Fields" option is selected. Click the "Import" button.
2. "Error 3349, Numeric Field Overflow"
This error is usually caused when the import function encounters a numeric column (field) containing numbers larger than dBase limits. (Around 32,000,000) The solution is to be sure that the entire column is configured as "text", rather than "numeric". However, using the Excel commands to format a cell or column as "text" is not always successful when it comes to importing to dbf. In this case, use the "Save As" function in Excel (see instructions below).
NOTE: A spreadsheet can contain features which are not translatable to a database (i.e. sub-totals, macros, etc).
Try to import again. Before clicking the "Import" button, first enter a new, different name in the "Create Table As:" box, then choose the "Trim Text Fields" option on the "Import" dialog box. Click the "Import" button. The sheet should import correctly now. If you have any problems importing Excel data, make this option the default behavior for Foxtrot. To do so open "Tools / Preferences" from the menu and select the option for "Trim Spreadsheet Text Fields" on the "General" page.
TO SAVE AS DBASE III FROM EXCEL
Your first row in your spreadsheet should contain column headers. These will be converted into field names in the new database. Because of this, they must conform to dBase field naming rules. These are:
1. All names must be 10 characters or less.
2. No duplicated field names allowed.
3. No field name can begin with a number, although numbers are allowed.
4. No punctuation allowed, except the underscore character (_).
5. No spaces allowed.
To create a database using Excel:
1. Highlight the top, left cell (A1).
2. Press Shift+Control+End to select the entire contents of the spreadsheet.
3. Select "Format/Column/Auto-Fit Selection" from the menu. (If this option is not chosen, Excel will only save the data which is visiible, even if the column is not wide enough.)
4. With all cells still highlighted, choose "File/Save As" from the menu.
5. In the "Save As Type" box, choose "DBF 3 (dBase III) (*.dbf)".
6. You may see a message stating that the selected file type will save only the current spreadsheet., click "OK".
7. Close the dbf file in Excel, or close Excel completely.
8. Answer "No" when asked to save the file in Excel format.
9. Open the newly created dbf file in Foxtrot (not the *xls file).
IF YOU HAVE PROBLEMS COPYING DATA INTO YOUR DATABASE
If you can’t use "Script Copy" to put data into the database you imported from Excel, it is probably because the length of the field you are copying is too small. If you are starting with an empty spreadsheet (or at least some of the fields are empty) and using Foxtrot to populate it, try this:
1. Open the spreadsheet in Excel.
2. Be sure that the first row contains dbf-compliant field names (see above).
3. If there is any column in the second row without data, populate it with "dummy" data, the length of the field you want to create. Use alphabetic characters rather than numbers.
4. Save the spreadsheet, close it in Excel, then import into Foxtrot, using the "Trim Text Fields" option. |