The previous example loaded a CSV data file. In this example we'll look at very simple fixed-field data file, and how to use SQL*Loader to transfer that data into an Oracle table.
With a fixed-field data file, there are no comma separators or enclosing double quote marks. The fields are always found in the same position on each line. The white space between values is composed of spaces -- actual ASCII text characters that look empty. For example, in the data show below, the names are always found in the position 1 to 10 (or we could also include the next 2 spaces for possible larger names and say that the first field extends from 1 to 12).
The Fixed-Field data file (file name = "Data.txt")
When using this type of date file, we'll need to carefully determine the starting and ending position for each column of data. Shown below is our data with a raster of positions from "01" to "46".
The Fixed-Field Data File
The Fixed-Field data file (file name = "Data.txt")
NAME BALANCE START_DT
Jones, Joe 14 Jan-12-2012 09:25:37 AM
Loyd, Lizy 187.26 Aug-03-2004 03:13:00 PM
Smith, Sam 298.5 Mar-27-1997 11:58:04 AM
Doyle, Deb 5.95 Nov-30-2010 08:42:21 PM
When using this type of date file, we'll need to carefully determine the starting and ending position for each column of data. Shown below is our data with a raster of positions from "01" to "46".