You are here: > ESRI Forums > arcgis desktop discussion forums > Thread Replies

ArcGIS Desktop Discussion Forums

ArcGIS Desktop - Data Management (General) forum

Excel to dBase.   Bryan Houk May 15, 2007
Re: Excel to dBase.   Monica Cotter-Brown May 24, 2007
Re: Excel to dBase.   Valentina Boycheva May 29, 2007
Re: Excel to dBase.   Cara Henry Jun 14, 2007
Re: Excel to dBase.   William Huber Jun 14, 2007
Re: Excel to dBase.   melanie helser Mar 18, 2009
Re: Excel to dBase.   Ray Carnes Mar 18, 2009
Re: Excel to dBase.   Theresa Valentine Feb 26, 2010
Re: Excel to dBase.   Ray Carnes Feb 26, 2010
Re: Excel to dBase.   Theresa Valentine Feb 26, 2010
Re: Excel to dBase.   Theresa Valentine Feb 26, 2010
Re: Excel to dBase.   Valentina Boycheva Feb 26, 2010
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Excel to dBase. 
Author Bryan Houk 
Date May 15, 2007 
Message I don't get it. I created a table in Excel that had a list of streets and did a subtotal of how many times the name is listed in the table. That showed in column B. I saved that as a dBase file and tried to open it in ArcMap. It tells me that it can't be opened, which is what it tells me everytime I do that. So I saved it as a .txt file and went to ArcCatalog to convert it to a dBase file. It worked fine and it showed in the preview section.

I created another Excel sheet that had just the streets names listed, with no subtotals. I saved that as a .txt file and went to ArcCatalog. I tried to convert it to a dbase file and it says failure to copy rows or something like that. I opened up the file that I was able to convert and pasted the street list in place of what was already there. I saved it exactly the same as I had before and tried to convert it. It told me the same thing, failure to copy rows. And now it won't work either. It worked before, but now it won't. I don't understand why I'm having so much trouble converting a .txt file to dbase. Also, I don't understand why I can't save it in Excel as a dbase4 and get it to work correctly.

It's really making my day start off very bad. Please help. Thanks. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author Monica Cotter-Brown 
Date May 24, 2007 
Message Don't know why it's not working but try this instead. Import your xls file into Access. Then try bringing the Access table into ArcGIS. Much more reliable. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author Valentina Boycheva 
Date May 29, 2007 
Message When you calculate the subtotals, the new column contains a formula, that's why the dbf can't be opened in ArcMap. If you create a brand new worksheet and copy/paste the two columns as text (special copy), then expand the fields to show the whole text, and finally select all the cells to be saved, you should be able to save to a dbf that ArcMap can read. The main idea is that any time you open the dbf file in Excel to make a small change, you have to create a blank worksheet, copy the results as described, and save again.

Indeed, it's much better to handle dbf files in Access. You can make all kind of queries and summaries and export in dbf without any problem.

More info on your problem. A long time ago I happened upon some very useful guidelines at this website, but the page has been deleted.

http://www.lib.umich.edu/nsds/tutspat/excel.html

However, I saved the text. Here it is, it saved me many times and I apologize for the long post.

Preparing tables for use with ArcView and ArcGIS
A common task for GIS users is to import tables into the GIS application. This guide explains how to use Microsoft Excel to translate tabular data to the format required for use with ArcView and ArcGIS, the dBase format.

To save a file to dBase format (*.dbf) using Microsoft Excel:

1. With the file open in Excel, select the “Save as…” option from the main “File” menu (located in the upper left corner of an Excel window.

2. When the “Save as…” window appears, use the folder options to navigate to the space where you would like to save the file.

3. Choose a name for the file (by typing the name into the “File name” box near the bottom of the “Save as” window), keeping in mind that Arc products may have problems reading the file if the file name contains spaces or special characters such as @ # $ % etc. Arc products may also have problems with file names that begin with numbers.

4. The file type (found in the “Save as type:” dropdown menu located below the “File name” box in the “Save as” window) must be set to ‘DBF4 (dbase IV)’ or ‘DBF3 (dbase III).’ The DBF4 option is usually preferable.

5. Press the “Save” button to complete the process (located in the lower right corner of the “Save as” window).

The following formatting steps will help minimize the likelihood of processing errors during the conversion from Excel to DBF (Adapted from web page entitled "Chapter 15: Tables" from William Huber’s course "SYS EN 597D: Geographic Information Systems" at Pennsylvania State University. URL as of October 12, 2004: http://www.quantdec.com/SYSEN597/GTKAV/section4/chapter_15a.htm):

1. Reorder the data rows if necessary so that the first one has valid, non-blank values in all fields. If there is no such record, create a "fake" first record and fill it with valid non-blank values. You can delete it after the conversion has occurred.

2. Format each numeric and date column explicitly. Make sure the entire column is formatted, not just part of it. Specify the number of decimal places you want.

3. Widen all columns to contain all the data in them. One way is to increase the font to 14 points and auto-size all columns. This seems to work well. You can increase column widths beyond this minimum to accommodate future values that might be even longer in width. [Note: Another way is to 1) format the text as Courier font; 2) select all rows and columns, by clicking in the rectangular box above the row numbers and to the left of the column letters; 3) from the Format menu select Columns/Autofit.]

4. Type short meaningful field names in the single header row. Remember, the dBase format limits you to 10 characters.

5. Delete any blank rows placed in the spreadsheet merely for formatting purposes.

6. Check that all values in all numeric columns really are numbers. Fix any errors.

7. Remove any hidden rows or columns within the data (Excel will not export data in hidden rows or columns, anyway, so unhide any data you want to export).

8. Save the spreadsheet at this point in its native Excel format. This preserves everything you see, including all formatting, giving you a point you can return to if the next steps reveal errors.

9. Convert the spreadsheet to dBase format using Excel's 'Save as...' command. dBase IV and III formats are compatible with Arc products.

10. Close the spreadsheet immediately (Minimizing does not work: it has to be closed.). Now open the .dbf version you just created.

11. Check the results carefully. If they are unacceptable, close the .dbf version and reopen the Excel version. Fix the problem(s) and resume at step 8 [Note: if formatting as text or numeric is not successful from within Excel, the problem can be addressed in ArcView/ArcGIS by creating a new field (defining it as text or numeric as appropriate) and setting it equal to the original field's values].

12. Clean up: if the results are ok, delete your initial "fake" record if you used one and re-save the file in dBase format.

Adding rows or columns:

The steps you will need to take if you wish to go back and add rows or columns to your table are explained in Saving Excel files in dBase format, a page maintained by Barbara Parmenter at the University of Texas School of Architecture.
http://www.ar.utexas.edu/Courses/parmenter/gis/tips/excel.html

It says as follows:

If you have used Excel to create a .dbf file, and you later want to go back and add records or columns to that .dbf file in Excel, please read the following before you save or export the modified file:

To add to an existing .dbf file in Excel, you must redefine the named range. This is because, unbeknownst to you, when you first exported the Excel file to a .dbf file, Excel created a "named range" for that .dbf file. If you do not modify this named range, Excel will continue using this original range and thus not include any added rows or columns on subsequent exports to .dbf of the same file . If you don't believe me, feel free to find out the hard way by losing all your added data!

To modify the named range to after you have added data to an existing .dbf file and before you save or export the file:

On the Insert menu, point to Name, and then click Define.
In the Names in workbook box, click the name whose cell reference you want to change (this is "database")
In the Refers to box, change the reference (just change the last row and/or column number so that it covers all your records)
Choose File-Save As, and save to a new .dbf (this ensures that you don't accidentally destroy your original file without being sure this process has worked)

Additional info:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q68914
http://www.blogontheweb.com/dragonspeed/archive/2004/11/02/21628.aspx


 
  Valentina Boycheva
Senior GIS Programmer/Analyst
http://www.jonesedmunds.com
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author Cara Henry 
Date Jun 14, 2007 
Message I just purchased a new machine with MS Office 2007, it doesn't offer an option to save in dbase format. Is there anyother format i can save to when importing data into ArcGIS, or does any one know of a patch I can download to allow Excel2007 to support the dBase format? Thanks so much for the help in advance! 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author William Huber 
Date Jun 14, 2007 
Message Wow! MS seems out of touch with reality, but you're absolutely right: Excel no longer creates dBase files, period. Visit http://office.microsoft.com/en-us/products/FX101633871033.aspx and scroll to the middle section on "What file formats will no longer be supported in Excel 2007?"

It looks like you will have to save data in text format or, if you have ArcGIS 9.2 or later, you can read Excel files directly. Not too helpful when working with shapefiles, though...
 
  --Bill Huber
Quantitative Decisions (http://www.quantdec.com )
More GIS Q&A at http://gis.stackexchange.com/q/3083/664 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author melanie helser 
Date Mar 18, 2009 
Message I have ArcGIS 9.2 and it won't let me import my excel data that I uploaded from the census website. I've tried uploading the data as a .txt and I get this pop up message:
---------------------------
Warning
---------------------------
A selected item could not be added to the map
General function failure

---------------------------
OK
---------------------------

I have to have this data added to this map by Friday (2 days from today). Any assistance would be MORE than appreciated. My email is: helsermd@uab.edu and my phone # is 205.568.1892

Thank you,
Melanie Helser 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author Ray Carnes 
Date Mar 18, 2009 
Message Melanie,

This message usually occurs when a field name (column name) in the spreadsheet is invalid, or if a row of data isn't in the same format as the rest (such as a line of totals of numeric data).

See 'FAQ: What characters should not be used in ArcGIS for field names and table names' at http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=23087

If you are unable to determine the problem, email me the spreadsheet: rcarnes '@' esri.com

For others reading this thread:

1) ArcGIS can create DBF files from XLSX spreadsheets if they are first saved as XLS spreadsheets. See 'Problem: Microsoft Office 2007 Excel does not allow tables to be saved as .dbf format' at http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=34102

2) If you have an XLSX spreadsheet but not Excel 2007, you can use it with ArcGIS after installing a download from Microsoft. See http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Ray Carnes,
ESRI Technical Marketing. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author Theresa Valentine 
Date Feb 26, 2010 
Message I can't seem to get this spreadsheet to show my column UTM(Northing,meters) to show up in ArcMap correctly (shows up as null). I'm trying to addxy on this data, so it's important. I'm using sheet1..

thanks 
  Theresa Valentine
Spatial Information Manager
Corvallis Forest Science Lab
theresa.valentine@orst.edu 
  todd_coordinates3.xls (opens in new window)
 
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author Ray Carnes 
Date Feb 26, 2010 
Message Hi Theresa,

The comma is not supported in a column name.

Once I removed these, I was able to use AddXY to get the data in the spreadsheet into ArcMap.

Ray Carnes,
ESRI Technical Marketing. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author Theresa Valentine 
Date Feb 26, 2010 
Message I removed the commas, and still get null for one of the coordinates... 
  Theresa Valentine
Spatial Information Manager
Corvallis Forest Science Lab
theresa.valentine@orst.edu 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author Theresa Valentine 
Date Feb 26, 2010 
Message only way I could get it to work was to create a new column, copy the values, and get rid of the old column... seems pretty buggy to me! Not sure what was wrong with my column, but it wouldn't put the values in there...

It would do the addxy, but the values for Northing were 0... so they weren't in the correct place. 
  Theresa Valentine
Spatial Information Manager
Corvallis Forest Science Lab
theresa.valentine@orst.edu 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Excel to dBase. 
Author Valentina Boycheva 
Date Feb 26, 2010 
Message In Excel, the numbers that import correctly are in number format, those that are null are in text format. Converting all to number will fix the problem. 
  Valentina Boycheva
Senior GIS Programmer/Analyst
http://www.jonesedmunds.com