Rules for Excel or CSV to dBase:
These apply for the creation of files in CSV format to import
to MICRODEM. If you have an existing CSV file that needs to
be edited, you can use Excel. You can also do small edits to the CSV file
in Notepad or Wordpad.
The first line
must have the field names; you will edit any of those longer than
10 characters, and all characters other than Letters (they will be converted to
all capitals), Numbers,
and "_" (underscore) will be removed. If your
file does not have field names, you can add them in any text
editor such as NOTEPAD. Use the same delimiter as the
rest of the file.
- Insure that you have columns "LAT" and
"LONG", both all caps. They can
be anywhere in the worksheet.
- Rules for field names.
- If you must scale a column, for instance to convert miles
to kilometers, or to insert an implied decimal point from
old records that tried to minimize file size:
- Create a new column in Excel with formulas to get the
- Copy that column, and use a paste special to get
the values instead of the formulas.
- Delete the original column with the bad units,
and the one with the formulas.
- Insure that latitude and longitude columns use decimal
degrees, have a negative sign for south latitude and west
longitude, and that longitude goes from -180 to
+180. If not, use math operations in Excel to correct them. The CSV import into MICRODEM will let you put a minus sign for west longitude
if that is the only problem. You can also edit the database in
MICRODEM to multiply the LONG field by -1, or to convert 0-360 longitudes.
- If you have ZIP codes and want to use them to join/link databases, you should
use the field name "ZIP" or "ZIPCODE". Otherwise they may import as
real numbers, which cannot be used for the join. MICRODEM now insures the
ZIP codes have 5 character lengths, by adding leading "0" if needed (this can
happen using Excel which which treat the Zip codes as integers and delete
leading "0" in New England).
- FIPS codes will have similar issues to ZIP codes.
- Rearrange the columns if you would like (drag and drop);
when you view the table later, the fields will be in the
same order as they are in the Excel spreadsheet.
- Avoid using commas and quotation marks in you
table. These will cause you problems (values will be in the
wrong field, and they cascade down the rows). If you use CSV to export and import into
MICRODEM (overall the least hassle), these will cause you
problems. Excel has global search and replace where you can
replace commas with spaces or dashes.
- If you have no data for a field (attribute), leave it
blank. If you have no data for any of the records for a field, you
must put in a holder value in one record that has the correct length (and
number of decimals for a floating point number) so that the dBase table will
be correctly initialized. You must have a value in the file that will
be the largest or longest value that you will ever want in that field if you
are going to edit the database or perhaps add additional records.
- If you want the values to be floating point, you need to insure that
they have a decimal point, and the correct number of values after the
decimal point. The easiest way to do this will be to edit in Notepad,
and put a placeholder value for the first line, which you can edit later.
- MICRODEM will convert integers larger than about 32000 into strings.
If you want them to remain values, put a decimal in the first value in
Notepad (Excel will strip it out), and they will be treated as floats.
Excel to CSV to dBase in
Import CSV into MICRODEM.
Problems with CSV import
Last revision 1/26/2013