WinSQL » Knowledge base

Document information

Document ID:	1453
Subject:	Transferring, migrating, importing data from DB2 to Netezza
Creation date:	8/12/10 8:47 AM
Last modified on:	8/12/10 8:48 AM

Transferring data from Microsoft Excel to Netezza

From time to time, businesses need to migrate data from one source to another. This is often a major challenge when such migration needs to be done between different types of databases. Moreover, some businesses require these data transfers periodically based on a schedule.

This article demonstrates how to migrate data between Microsoft Excel and Netezza databases, it talks about challenges faced when converting between different data types as well as transferring binary data.

Using WinSQL for data migration

WinSQL is a universal tool that can migrate data from any ODBC compliant data source to any ODBC compliant target.
Since WinSQL is ODBC compliant, you can connect to almost any database currently available in the market today. This includes heavy duty relational databases like Oracle, MS SQL Server, IBM DB2 and Sybase to single user file bases databases like MS Access, Paradox and DBase.

Data migration challenges

Here are some typical challenges that must be met when transferring data

• Data type conversion - Often databases use different set of data types, which differ in precision and names. It is important to match data types closely as possible to avoid data truncation
• Binary data transfer - Often text files are used to transfer data. This method is fine for alphanumeric data types but falls short if you have binary data types like images, audio, and video
• Logging - It is important to log errors that occur during data transfer. This way users can take appropriate action in case something goes wrong.

WinSQL supports two modes of data migration:

• Connected mode - when you have access to both Microsoft Excel and Netezza databases at the same time.
• Disconnected mode when the databases reside on the different network and cannot be connected simultaneously from one client machine

Connected mode

Connected mode is the fastest way to migrate data from one source to a target. In this mode WinSQL can connect to both Microsoft Excel and Netezza at the same time, run a SELECT queries against source database and issues INSERT statements against the target database.

The following image displays a screen shot connecting to both databases. The easiest way to initiate data transfer is to select the desired table from Microsoft Excel and drop in to Netezza window, or the other way around.



This invokes the Data Transfer wizard in WinSQL. Click here to watch a video tutorial showing how to using WinSQL in this mode to transfer data.

Disconnected mode

Disconnected mode is useful when you cannot connect to both Microsoft Excel and Netezza at the same time. This requires two steps:

1. Exporting data from Microsoft Excel, saving it to a temporary location
2. Importing back into Netezza from the temporary location.

Historically, users have been using text files to import from the source and then insert data back into target. Typically, these text files contain data in CSV format, Tab delimited, or fixed length.

Although this technique may work in certain cases, it has its own problems:

• Custom code must be written to import/export data
• Cannot export binary data
• Carriage returns, line feeds, tab and other non-printable characters may not get transferred correctly
• Transferring large text value for one field becomes a challenge

WinSQL overcomes these challenges by creating the temporary file in a binary format, which not only handles the above mentioned problems but is generic enough to be used by any database. In WinSQL's language, this is called a DataBag. DataBags can not only store data, but also the design of a table. This way you can easily recreate the table in the target database. Click here to watch a video tutorial on this subject.

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users?

Your Name

Email

Hide my email address

Captcha

Your Tip

Submit Tip