Thursday, July 05, 2012

Easy CSV import into Oracle

English: The logo of Oracle Corporation de:Bil...
English: The logo of Oracle Corporation de:Bild:Oracle-Logo.svg he:תמונה:Oracle Logo.jpg (Photo credit: Wikipedia)

The easiest way I've found to import text data sources into Oracle have been using the Microsoft SQL Server Import and Export Wizard that comes with Standard, Enterprise or Developer editions of MS SQL Server.

Assuming you've got the Oracle database drivers installed, just right click a database from within SQL Server Management Studio, choose Tasks -> Import Data.

As your datasource, choose your CSV or text file.

After you've configured the specifics of your data source, you'll need to choose a destination. Choose the OLE DB Provider for Oracle, then click Properties.

  • Instead of using your Server Name, enter the database instance name and the credentials. 
  • Click Text Connection to be sure it is correct, and click Allow saving password.
  • Click OK.
  • Click Next.


In the next window, choose the destination table you've created in Oracle. Edit your mappings and preview them to be sure the data is being added correctly, click next, and next again to Run Immediately.

You may get errors, but review them and your data to confirm your import was complete. My errors usually stem from not being able to retrieve column code page information which doesn't prevent any data loading.

Cheers!

Enhanced by Zemanta

No comments: