At 02:56 AM 10/11/2005 +0000, you wrote:

>Hi,
>
>I am trying to load a fixed width file into an external table, then
>copy that table to a "real" table.
>
>Here is the schema for the external table:
>create table eOffices external file 'c:\temp\dataFiles\Offices.txt'
>(
> OF_OFFICEID VARCHAR(10),
> OF_MLS_CODE VARCHAR(12)
>)
>
>and the "real" table:
>create table Offices(
> ROF_OFFICEID VARCHAR(10),
> ROF_MLS_CODE VARCHAR(12)
>)
>
>The format of the txt file is like this:
>0123456789MLSCODEXXXXX
>1111111111MLSCODE2XXXX
>2222222222MLSCODE34XXX
>0000000001MLSCODE345XX
>0000000002MLSCODE3456X
>
>So the first ten columns are the officeid, then the next 12 columns
>are
>the mlscode terminated by a newline.
>
>This format repeats for many rows.
>
>I receive no errors when creating the external table, and again no
>errors when doing the:
>
>Insert into Offices select of_officeid, of_mlscode from eOffices;
>
>However the data in the Offices table after the insert seems skewed;
>meaning it appears that the columns are not lining up correctly.
>
>For example my result set looks like:
>
>ROF_OFFICEID ROF_MLS_CODE
>23456789ML ODEXXXXX 11
>111111MLSC E2XXXX 2222
>2222MLSCOD 4XXX 000000
>01MLSCODE3 XX 00000000
>
>Obviously, it is skewed by some constant amount, but I cannot figure
>out what I am doing wrong. Is my line delimiter incorrect? Is my
>file
>format incorrect? I have been scouring the web for examples all day
>and have come up completely empty.
>
>Any input is greatly appreciated.

Yup. You declared the fields in the external file as varchar, so 2
additional bytes are being "taken" from the stream at each turn.

Redefine those external columns as char(10) and char(12) respectively.

./heLen