Importing sqlldr "loader" format data in Oracle SQL Developer

My previous blog detailed how to backup & restore tables. However it turns out if you need to restore/import a single table that's harder than it should be.

The problem lies mostly in the UI of Oracle's SQL Developer. By default it requires CSV/Excel/DAT file and opening a loader formatted file (ldr) is not an option.

Googling on "how to import loader format data" brought some hints though. First result was on directly using sqlldr which is a separate executable (part of Instant Client) you need to download from Oracle site.

Second result was on Jeff Smith's site and helped me realize that I can do the same with SQL Developer. In the end the backup I made already produced loader format files, so it was strange that I cannot import back these.

Jeff's post suggested that loader files are CSV-like and indeed inspecting them proved that. Then it hit me. Backup required separators and delimiters. So loader format is not much different that CSV.

So I renamed the file to csv and tried to follow Jeff's steps. No luck here. Obviously things changed a bit from 2012.

What worked for me though was:

1. Rename ldr to csv 
2. Delete the first space character in the file 
   
   As the ldr file consists of a single line that can be several GBs, most text editors cannot cope with that. You need to use command line like this
   
   
3. Select your table and start Import
   
4. Open our ldr file (masked as csv now) for import 
5. Stop reading preview when asked to do so
   
6. Select the following options
   
   Select Line Terminator as |{EOL}<space>
   Select Delimiter as |
   Deselect Header as loader files have no header info
   
   Selecting the terminator and delimiters will output several warnings that data cannot be previewed. Dismiss them and once you enter everting you should see your data in File Contents pane.
   
   Carefully inspect the data. If you see space in front of the data in the first column, please check you have the Line Terminator with space character (invisible on the screenshot) in the end.
   
   
7. Select import method
   
   I used Insert (without opening the resulting SQL in workspace), but you might check Jeff's advice on how to use SQL Loader
   
   
8. Select columns & definitions
   
   There should be no warnings in these two steps. If your first column (usually ID) says that the data is not appropriate for Non null columns check if you still have that first space character in your file and if so remove it.
   
   
9. Start the import