On 29-8-2018 03:27, 'Vaughan Wickham' v...@zeb.com.au [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
