ProductsDatabaseSpyDatabase Import / Export

Altova DatabaseSpy® 2010 provides a robust set of features to insert new data into your database or extract data from it using a variety of formats to suit many purposes.

You can import data prepared in CSV or XML formats compatible with your database. You can export data as XML or XML Structure, CSV, HTML, or Excel files.

DatabaseSpy Supported Databases

DatabaseSpy® 2010 supports multiple database types and automatically adapts its data import and export functionality to the following databases:

• Microsoft® SQL Server® 2000, 2005, 2008
• IBM DB2® 8, 9
• IBM DB2 for iSeries® v5.4
• Oracle® 9i, 10g, 11g
• Sybase® 12
• MySQL® 4, 5
• PostgreSQL 8
• Microsoft Access™ 2003, 2007

 Click here to download a fully functional, free 30-day trial of Altova® DatabaseSpy® 2010!

Data Import Options

DatabaseSpy® 2010 lets you import data into your databases from conventional CSV files or from files containing XML data to create new tables or update existing tables in your database.

The Import option in the Tools menu or the Import button on the main DatabaseSpy® 2010 toolbar opens an Import dialog to set the import source, choose options, and see previews of your data import request.

The XML import option can help ease the transition between modern XML-based applications and legacy relational databases, with special XML-aware options to process data during import.

If the column names in the source file don’t exactly match your database you can reassign them. You can also prevent individual columns from being imported, and you can set key constraints.

If you need to convert data from a dissimilar format before importing it to your database, Altova MapForce® 2010 is the visual mapping tool with the power to map any combination of XML, database, text, or EDI files. Altova MapForce® 2010 can connect to all major databases and convert source data on the fly for direct insertion, but if connection to the target database is not possible or convenient, one of the many outputs supported is a CSV file that can be imported by DatabaseSpy® 2010. The combination of the data integration functionality of MapForce® 2010 with the database browsing, SQL editing, database design, and import/export features of DatabaseSpy® 2010 provides database administrators and other IT professionals with a robust toolset for database management needs.

DatabaseSpy® 2010 has two major import options: you can import the data directly, or you can generate the SQL statements necessary to perform the import and place them in the SQL Editor. If you generate SQL, the import request is not actually performed until you press the execute button in the SQL Editor window.

	Click to enlarge

Data Export Options

Once you have connected to a database, the export option is available from the Tools menu, or simply click the Export button in the toolbar to open the export data dialog.

Data Export Formats

DatabaseSpy® 2010 supports five formats for data export:

• XML – Each row in the table is exported with an identifying <Row> XML element and each column entry is identified by an XML element that matches the column name. This format also includes a commented header documenting the SQL SELECT statement used to retrieve the data.
• XML Structure – Each row in the table is exported with an identifying XML element that matches the exported table name. This lets you preserve table hierarchies and relationships when you export from more than one database table.
• CSV – Each row is exported as plain text, with multiple delimiter and newline options.
• HTML – Each row is exported with HTML coding to form an HTML table that can be opened in a Web browser or incorporated into a larger HTML page.
• Excel – Each row is exported directly to a Microsoft Excel spreadsheet file.

DatabaseSpy® 2010 allows you to fine tune the content of the output with special refinement options for each format.

Data Export Source

DatabaseSpy® 2010 gives you complete control over the source data selected for export. You can select any table or view from any connected database.

As you browse the hierarchy of your database in the export source window, you have the same Layouts, Filters, Favorites, and the Object Locator features available in the Online Database Browser to simplify navigation of the database structure.

You can also switch the source window from a database hierarchy to a SQL display. If you select one or more tables for output, then switch to SQL Source, DatabaseSpy® 2010 displays the SQL query it will use to retrieve data.

You can edit the query in the Source window if you like, or paste SQL statements copied from the DatabaseSpy® 2010 SQL Editor window. When you’re working in the SQL Editor you even have a dedicated Export button to let you instantly open an export dialog box pre-loaded with your current SQL Editor statements. This lets you construct a specialized SQL query, test it in the SQL editor, and conveniently export the resuilts in any of the five supported export formats.

Data Export Destination

The destination section of the export dialog lets you specify the path for output files, as you would expect. If your selected format is XML, XML Structure, CSV, or HTML, you can also export directly to Altova XMLSpy. If you select multiple tables or views for export, each is written to a separate file.

Altova XMLSpy® 2010 is the industry standard XML development environment for modeling, editing, transforming, and debugging XML technologies. The integration of the export data feature of DatabaseSpy® 2010 with Altova XMLSpy® 2010 makes it simpler than ever before for database and IT professionals to utilize the content of relational databases in platform-independent XML-based applications.

Data Export Preview

The Preview button in the Export dialog toolbar turns on the Preview section at the bottom of the Export dialog window. This feature lets you do much more than simply preview the data you have selected for output. You can click the column header row to rename columns in your output, delete individual columns, or adjust column output options.

When you are done selecting export options, simply click the export button to generate your output files or deliver your output directly to XMLSpy. DatabaseSpy® 2010 notifies you of progress as it processes your request.

Try out all the data import and data export features of DatabaseSpy® 2010 for yourself – download a free 30-day trial today!