Convert Excel File To .dbf Format
Sebastian Thorndike
dBASE was one of the original database management systems and the dBASE file format (.dbf) has been around for a long time. Microsoft Access supports exporting to the following dBASE file formats: dBASE III, dBASE IV, dBASE 5, and dBASE 7.
Microsoft 365 If you are a Microsoft 365 subscriber, make sure you have the latest version of Office. If you are an IT professional managing the Microsoft 365 update process, check out the channel releases page to learn which updates are provided in each channel
The default file format is dBASE III. To change to dBASE IV or dBASE 5, select a file format from the Save as type box. Although dBASE 7 is not listed, dBASE 5 and DBASE 7 are essentially the same file format. In Access for Office 365 and Access 2019, file names of up to 64 characters are supported.
I'm trying to convert an excel file to a shapefile in arview 3.3. using suggestions by Bill Huber (on the ESRI forums site). I've completed the recommened 'recipe' of requirements on this link Bill referred:
but I am now stuck trying to convert .xls to .dbf because there is not an option in MS Excel 2007 to convert to .dbf (dbase III or IV) that I can see - its all variations of .xls then .csv, .txt and other types.
Perhaps .csv is the file type I need, but the 'recipe' says dbase III or IV, neither of which are listed as options in excel. Does anyone know where to go from here? If I can just get it to dbf I should be good (assuming my file formatting worked).
How can I convert a .csv file into .dbf file using a python script? I found this piece of code online but I'm not certain how reliable it is. Are there any modules out there that have this functionality?
You won't find anything on the net that reads a CSV file and writes a DBF file such that you can just invoke it and supply 2 file-paths. For each DBF field you need to specify the type, size, and (if relevant) number of decimal places.
None that are well-polished, to my knowledge. I have had to work with xBase files many times over the years, and I keep finding myself writing code to do it when I have to do it. I have, somewhere in one of my backups, a pretty functional, pure-Python library to do it, but I don't know precisely where that is.
Fortunately, the xBase file format isn't all that complex. You can find the specification on the Internet, of course. At a glance the module that you linked to looks fine, but of course make copies of any data that you are working with before using it.
A solid, read/write, fully functional xBase library with all the bells and whistles is something that has been on my TODO list for a while... I might even get to it in what is left this year, if I'm lucky... (probably not, though, sadly).
I have created a python script here. It should be customizable for any csv layout. You do need to know your DBF data structure before this will be possible. This script requires two csv files, one for your DBF header setup and one for your body data. good luck.
My office has just switched to MS Office 2013 and neither MS Excel or MS Access support dbf (dbase IV) format. I am SOL unless I get an add on. I even used to be able to convert xls files in MS Access (even with Field Does Not Fit in Record error message) but can't even do that now.
Given your work environment, you might have to approach those within your IT department to see if some of the constraints can be lifted given that many of the aforementioned recommendations provide viable solutions in many situations.
I have a series of datafiles of all place names within several different countries saved as .dbfs that I'd like to display in ArcMap. Each row contains an ID as well as a latitude and longitude value; however, whenever I attempt the conversion ArcMap doesn't allow me to select the corresponding lat/long columns within the 'Display X,Y Data' action window. I've attached one of the relevant .dbfs and a screenshot of the issue to assist anyone who may understand this issue better than I.
I've skimmed through several online help files on this topic, but don't fully understand where the issue lies and/or what steps I can take to rectify this issue. Any advice is appreciated, thanks in advance.
Very helpful, thanks so much! One minor question though about that editing process: Do I need to convert the .dbf file into an Excel format first to change the data type? I would've hoped there would've been an easy method to make that change within ArcMap, but it doesn't appear to be very obvious at first glance. I apologize if that's a basic question; I'm rather new to this program!
You are right, Mike. Edit the data type in Excel. There is an Excel to table tool . . .try this method also. This will give you the added performance in landing data that will project on the fly. You will need to Define Projection (use your correct UTM zone).
I converted the dbf to an Excel file and changed the data type from "text" to "number" for the relevant LAT and LONG columns. However, when I attempt to perform the 'Display X Y Data' operation, I'm now only given the option of using the ID field (see picture). This remains true whether I add the data as an Excel doc or as a dbf. I have no clue what might be causing this issue, but would definitely like to rectify it. Thanks so much!
ramani
(Subramanian.G)
RE: Converting .dbf files to excel or access skoko (Programmer)23 Sep 03 17:12Ramani, he doesn't have foxpro.
Cajuntank, you can import foxpro file into excel. RE: Converting .dbf files to excel or access Kurjo (Programmer)23 Sep 03 17:12If you dont have Foxpro you can still convert dbf tables to Excel. Excel can open dbf tables if they are created in 2.6 format (up to 65K rows). After opening it you can save it as regular Excel sheets. If the dbf are Visual foxpro format you need foxpro and follow the tip Ramani gave.
Kurjo
RE: Converting .dbf files to excel or access cajuntank (IS/IT--Management)(OP)23 Sep 03 17:15Please elaborate a little more on some of your comments.... again I am very programmer illiterate so when you say
"Use myTable" I have not a foggy idea of this... Is this done from a command prompt or from the directory that houses the foxpro files??? What is a foxpro table?
Please be kind. RE: Converting .dbf files to excel or access Stella740pl (Programmer)23 Sep 03 17:30cajuntank,
If you don't have VFP in your posession, you might be able to open .dbf table from Excel and save it there, or import it to Access, although it all depends on versions of the software. You, most likely, don't need to do anything with the index file, since after you import it, you can order/sort/index it there using the tools of the target application. I can't recall from the top of my head right now, which of them used to have .ndx for index extension (Clipper? FoxBase? dBase?), but I don't think it was Visual FoxPro. For this reason, I believe that your tables are in a format that is readable by most currently used versions of Excel.
Stella RE: Converting .dbf files to excel or access Stella740pl (Programmer)23 Sep 03 17:50cajuntank,
FoxPro table is that file that have .dbf extension.
Index is the file with .ndx extension.
You can try to go to Excel, File->Open, set FileTypes to dBase(.dbf), and try to open the tables that you have. Chances are, you will be able to do it. Or you can go to Access, and try to Import it from there, it works with .dbf, too.
How big are your .dbf files?
Stella. RE: Converting .dbf files to excel or access jimoo (Programmer)23 Sep 03 17:56With the new OLEDB driver available from www.microsoft.com/downloads you can open the table as an ADO recordset if you're familiar with that technique. Then using the VBScrip from Excel you can handle the data accordingly.
I have not used the new OLEDB provider yet, so if other have feel free to elaborate whether this would work or not. Jim Osieczonek
Delta Business Group, LLC
www.deltabg.com RE: Converting .dbf files to excel or access ramani (Programmer)23 Sep 03 18:00Yes I failed to note that VFP is absent in his/her tools. Sorry.
ramani
(Subramanian.G)
googletag.cmd.push(function() googletag.display('div-gpt-ad-1406030581151-2'); ); Red Flag This PostPlease let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.
CancelRed Flag SubmittedThank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.
I will try the txt.
At the present I am coping the table converting to date and then exporting and this is working.
I have been told that we have to have all our "date" fields in "date/time" format for the interface with Intramaps, so the master layers have to stay in "date/time" format.Thanks Pam
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
-l?hl=en
When you create a Microsoft Office Access table that contains long integer numbers and then you export that table as type dBase IV (.dbf), the numbers are converted to double data type and will show decimal places.
When you create a number field in a table in Access, you use theFieldSize property to control the amount of space that is allocated for a particular field. For number fields, you can choose one of the following numeric types from a list:
By default, the setting for number fields is Long Integer. The Long Integer value stores whole numbers that range from about -2 billion to +2 billion. Or, you can choose the Double value to store numbers with decimals.
Modify the design of the database file after it has been added to the dBase catalog. For example, set the Dec column to 0 (zero). For more information about designing database files in dBase, see the dBase documentation.
c80f0f1006