On 29-8-2018 03:27, 'Vaughan Wickham' vw@... [firebird-support]
wrote:

> I need to add a large number of IP addresses to an existing table in a
> database.
>
> After doing some research, I came to the conclusion using an external
> file as a table would do the job.
>
> So I’ve created a fixed length text file – 15 chars for the IP address:
> xxx.xxx.xxx.xxx
>
> Here is the query to create the table, it executes OK
>
> Create Table ip_addresses
>
> EXTERNAL File 'C:/ip_fixed_length.txt'
>
> (IP_Address CHAR(15));
>
> However when I query the results, it is not as I expect

External files are **not** a text format, but actually a binary format.
Your file content must exactly match its definition.

If you declare rows that are (only) CHAR(15), that means each row is 15
characters (and depending on the (default) character set that may be 1 -
4 bytes per character).

So, first of all, explicitly specify the character set to avoid
character set issues.

with your current definition, you would need to have a file content of

64.233.16.0<space><space><space>64.233.160.1<space><space><space>...

So no linebreaks.

If you do want linebreaks, then you must add a column for one or two
characters (depending on whether or not the file uses windows-style crlf
or unix-style lf, eg

Create Table ip_addresses
EXTERNAL File 'D:/temp/ip_fixed_length.txt'
(
IP_ADDRESS CHAR(15) CHARACTER SET ASCII,
LINE_BREAK CHAR(2) CHARACTER SET ASCII DEFAULT x'0D0A'
);

And then you must ensure that every line in your file is exactly 15
characters (space-padded if necessary) followed by cr/lf (total 17
characters per line) in character set ASCII.

In my example I also specified a default for the LINE_BREAK column, this
is not strictly necessary, but simplifies things if you want to insert
values. Inserting values into an external table is also a good way to
verify what format Firebird expects for your definition (eg by
inspecting in an editor that shows whitespace, eg Notepad++, or in a hex
editor).

Mark
--
Mark Rotteveel