Importing Data to PostgreSQL Databases
Is there a way to import the data from Excel/Access/CSV or other file into PostgreSQL database? This question regularly appears in the PostgreSQL related forums, mailing lists and conferences. Answers to this question very often contain recommendations to use different raw scripts, ODBC data connections coupled with the application that created the source file or combination of various data transformation tools and a pgsql utility. Those recommendations could help to solve the problem of importing data to PostgreSQL database, but only in case if the original file has a simple structure, the volume of imported data is relative small and users have the opportunity to directly connect to the PostgreSQL server.
But what if the original file is a Word 2007 or HTML document, or TXT file that contains Unicode data? Or CSV file that has a large number of columns and the size of a few hundred megabytes? In this situation the above mentioned decisions do not give the expected effect - the import process is often executed with errors, source data are distorted and not transferred entirely while PostgreSQL import took quite a long time.
Simple PostgreSQL Import Solution
Here we will review the solution intended for solving the main tasks associated with the PostgreSQL data import process - EMS Data Import for PostgreSQL. This powerful tool allows you quickly import data from MS Excel 97-2007, MS Access, DBF, XML, TXT, CSV, MS Word 2007, ODF and HTML files to PostgreSQL tables. It provides a wide set of features, like the ability to define various import parameters for each source file, perform data import to one or several tables or views located in different databases and the possibility to select the necessary import mode. The utility has a special batch insert mode that allows import data to PostgreSQL databases at the maximum possible speed, it supports Unicode and the latest PostgreSQL server versions, has a flexible user-friendly wizard interface and many other useful features.
During the import process you have an ability to set logical correspondences between columns of the source file and table fields of the target PostgreSQL database according to the source data format. Moreover, for the most types of files the columns auto-recognition feature is available in EMS Data Import for PostgreSQL which allows setting this correspondence automatically, if the source file and the destination PostgreSQL table have the same order of columns or rows. The program allows you to set formats for each imported field separately, in case additional formatting is required. This is very useful feature, if you want, for example, to set the content of certain fields to constant values or perform automatic text replacement during data import. Also, another helpful feature of EMS Data Import for PostgreSQL is possibility to define SQL commands which will be executed before and after the import process.
Data Import for PostgreSQL provides full customization of the application interface style to your liking and has multilingual support. The program allows you to use SSH or HTTP tunneling to connect to PostgreSQL servers that are located behind firewalls and could not be accessed directly. For SSH tunneled connection the use of private and public cryptographic key pair is available if it is required for security reasons.
The tool allows you to store its configuration settings in external template files if you need to perform the data import regularly. In combination with a command-line utility, which is installed together with the wizard GUI version, this feature enables you to automate PostgreSQL import process by scheduling console utility launch and specifying a template file name as an execution parameter.
There are many database tools available from both open source developers and commercial providers for accomplishing PostgreSQL data import task. However, only some of them provide a complete set of necessary functions for managing all aspects of the PostgreSQL database import process. EMS Data Import for PostgreSQL - one of the few database utilities that allows you to resolve the most significant issues related to PostgreSQL data import.
However, database import is only a small portion of the tasks faced by PostgreSQL administrators in their every day work. EMS SQL Management Studio for PostgreSQL can help you to simplify PostgreSQL server development and administration, manage queries and administer security, compare and synchronize PostgreSQL database data and schemas and more.