Report on File Import/Export using SQL

Importing and Exporting Data from Foreign Sources

The INSERT, UPDATE, and DELETE statements are extremely useful from within a database program. They are used with the SELECT statement to provide the foundation for all other database operations you will perform. However, SQL as a language does not have a way to import or export of data from foreign data sources. For instance, your office may have been using a dBASE application for several years now that has outgrown itself. Now your manager wants to convert this application to a client/server application using the Oracle RDBMS. Unfortunately for you, these dBASE files contain thousands of records that must be converted to an Oracle database. Obviously, the INSERT, UPDATE, and DELETE commands will help you after your Oracle database has been populated, but you would rather quit than retype 300,000 records. Fortunately, Oracle and other manufacturers provide tools that will assist you in this task.

Nearly all database systems allow you to import and export data using ASCII text file formats. Although the SQL language does not include this feature, SQL will not do you (or your boss) much good when you have an empty database. Let's examine the import/export tools available in the following products: Microsoft Access, Microsoft and Sybase SQL Server, and Personal Oracle7.

Microsoft Access

Microsoft Access is a PC-only database product that contains many of the features of a relational database management system. Access also includes powerful reporting tools, a macro language similar to Visual Basic, and the capability to import and export data from various database and text file formats. This section examines this last feature, particularly the capability to export to delimited text files. Delimited means that each field is separated, or delimited, by some special character. This character is often a comma, a quotation mark, or a space.

Access allows you to import and export various database formats, including dBASE, FoxPro, and SQL Database. The SQL Database option is actually an ODBC data source connection.  For this discussion, you want to select the Export option and then choose the Text (Fixed Width) option.

After opening an Access database (with the File | Open), select Export. A Destination dialog box (for Exporting) is displayed. Select the Text (Fixed Width) option. This option allows you to output your Access tables to text files in which each data type is a fixed width. For example, a character data field of length 30 will be output to the file as a field 30 characters long. If the field's data takes up less space than 30 characters, it will be padded with spaces. Eventually, you will be asked to set up the export file format.

Microsoft and Sybase SQL Server

Microsoft and Sybase have jointly developed a powerful database system that is very popular in client/server application development. The name of this system is SQL Server. Microsoft has agreed to develop versions of the RDBMS for some platforms, and Sybase has developed its version for all the other platforms (usually the larger ones). Although the arrangement has changed somewhat in recent years, we mention this agreement here to help you avoid confusion when you begin examining the various database systems available on the market today.

SQL Server provides file import/export capabilities with the bcp tool. bcp is short for "bulk copy." The basic concept behind bcp is the same as that behind Microsoft Access. Unfortunately, the bcp tool requires you to issue commands from the operating system command prompt, instead of through dialog boxes or windows.

Bcp imports and exports fixed-width text files. It is possible to export a file using the Microsoft Access method described earlier and then import that same file directly into an SQL Server table using bcp. bcp uses format files (usually with an .FMT extension) to store the import specification. This specification tells bcp the column names, field widths, and field delimiters. You can run bcp from within an SQL database build script to completely import data after the database has been built.

Personal Oracle7

Personal Oracle7 allows you to import and export data from ASCII text files containing delimited or fixed-length records. The tool you use is SQL*Loader. This graphical tool uses a control file (with the .CTL extension). This file is similar to SQL Server's format (FMT) file. The information contained in this file tells SQL*Loader what it needs to know to load the data from the file.

Summary

Modern database systems supply various tools for data manipulation. Some of these tools enable developers to import or export data from foreign sources. This feature is particularly useful when a database is upsized or downsized to a different system. Microsoft Access, Microsoft and Sybase SQL Server, and Personal Oracle7 include many options that support the migration of data between systems.