Subject Re: [firebird-support] Firebird 1.5 - External Table - Bulk Load - Problems
Author Helen Borrie
At 02:56 AM 10/11/2005 +0000, you wrote:
>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'
>and the "real" table:
>create table Offices(
>The format of the txt file is like this:
>So the first ten columns are the officeid, then the next 12 columns
>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:
>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
>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.