Importing Data Dump into Postgres

Importing Data Dump into Postgres

I needed to take four steps to import an Oracle binary dump into PostgreSQL

Open Oracle dump with something like notepad++ or grap and do a find/replace for all the binary characters (these can be highlighted in notepad++) and replace them with something like a pipe character (“|”)

If you know the special characters that are not compatible with UTF-8, then do find/replace on these too now … otherwise you can do this after running the copy command (step 5), since that will give an error for each non UTF-8-able line/character

Open the piped file in Excel and replace with tabs. Tabs are the standard delimiter in Postgres and by saving out of Excel, you get a uniform number of columns for each record, thereby avoiding “missing data for column” when executing the “copy” command

Create a table in Postgres with the number of columns seen in Excel, using generic data type such as text