Importing and Exporting Data with dBASE
Q: How do I import data from a text file into dBASE?
A: Importing data into tables is a basic need for any database system.
The macro below opens the Sample.dbf database which is located in the same folder with the workbook, and then runs an SQL query to the recordset. The query filters all the data based on country a criterion (Canada). Then, some of the filtered values are passed to an array, and, finally, the array values are written back to the Excel sheet. Until now I use Import/Export utility (64bit) installed with SQL Server 2014. Mainly I import EXCEL 2013 files. Now I would like to import a dBase dbf-file. The docs describe an OLEDB setup, I cannot configure with that utility, because the setup mentioned in the docs is not available. Any help is appreciated. Let Source = OleDb.DataSource ('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C: dbfFolder;extended properties=dBASE IV', Query='select. from myFile.dbf') in Source. You'll need the Access Database Engine provider deployed. Shift+Right Click the.DBF file. Open With Choose Default Program. Navigate to: C: Program Files (x86) Microsoft Office Office15 EXCEL.EXE. Make sure to tick the 'always use the selected program to open this kind of file'. Now all.dbf files should open in Excel 2013.
Importing.DBF files into Excel 2007 I have an XYZ.DBF file that I've imported into Excel 2007 but it doesn't parse properly on the import. The entire record goes into Column A of the Excel 2007 worksheet, Sheet 1. Columns B and up don't have anything in them. I think a record key is in the first several characters of each record.
dBASE provides this functionality through the use of the APPEND FROM command. Although there are a number of ways in which to use this command, we are interested only in the basic functionality of importing data to a table at this time. Once you understand the basic functioning of this command, you can view the on-line help for further information on the APPEND FROM command.
In order for you to import your data there are a couple of steps which must be followed.
1) You must have an existing table into which you will import your data. That means that you must create a new table whose structure will satisfy the needs of your data.
2) You must open this table. To do this, type:
USE <tablename>
in the command window. For example you could type:
USE Mytest.dbf
This way dBASE knows which table the data will be imported into.
3) You will then use the APPEND FROM command to bring your data into the open table.
If your data is in a *.CSV format, then all you need to do is use the following command in the command window:
APPEND FROM <filename> TYPE DELIMITED
For example you could type:
APPEND FROM mydata.txt TYPE DELIMTED
This format is probably the most common. Each character field in the data is surrounded by double quotes ('). Numeric and Date fields are not and these fields are seperated by comma's. (,)
Each record is identified by the use of a carriage return/linefeed at the end of the line. In essence, a single line in this file format is the same as a single record in the table.
Convert Xls To Dbf
Files in this format usually have either a .TXT or .CSV extension in the filename.
If your data is in a *.SDF format, then all you need to do is use the following command in the command window:
APPEND FROM <filename> TYPE SDF
For example you could type:
APPEND FROM mydata.txt TYPE SDF
This format is the next most common for text data. In this case all the data is 'padded out' with spaces so that all the fields end up in columns.
Just as in the DELIMITED style of file format each record is identified by the use of a carriage return/linefeed at the end of the line.
NOTE: Currently there is a problem with the BDE version which ships with Visual dBASE 7.01 (BDE version 4.51) which doesn't allow importation of date information from a text file.
Q: How do I export data from a dBASE table to a text file?
A: Exporting data from dBASE to a text file is handled through the COPY TO command.
Like the APPEND FROM command, there are a number of ways to use this command. Here we are only interested in it's most basic use. Once you understand how to use this command, you can go to your on-line help for further details on what can be accomplished with the COPY TO command.
In order to export data you must first be using the table from which the data will be exported. As before, you will be employing the USE command in the command window.
USE <tablename>
For example:
USE Mytest.dbf
Once the table is in use, all you need to do is type the following command in the command window:
COPY TO <filename> TYPE DELIMITED
For example:
Excel To Dbf File
COPY TO Myexport.txt TYPE DELIMITED
This would result in a file being created in the current directory called Myexport.txt which would be in the DELIMITED or *.CSV format.
If we had wanted to export the data in the *.SDF format, we would have typed:
COPY TO Myexport.txt TYPE SDF
This would result in a file being created in the current directory called Myexport.txt which would be in the System Delimted or *.SDF format.
Excel To Dbf Converter
Those are the basics on how to import and export text data into a dBASE table. For further information consult the on-line help for the APPEND FROM and COPY TO commands.
Tim Converse,
QA/Tech Support Engineer
dBASE Inc.
8/31/99