Subject Re: [firebird-support] Steps for Bulk insert / upload text file for INSERT from EXT TABLE
Author Kjell Rilbe
As for external table, the format is "raw". In other words, FB expects
the data to appear in FB's native form, "side by side". You would
probably want to use a format like this for your sets, assuming ANSI
charset (iso-8859-1 or win-1252 for example):


This external table would be declared as:

create table "MyExternalTable"
external file 'C:\MyDataFiles\MyExternalData.txt' (
"Data" char(3) character set WIN1252,
"Dummy" char(2) character set WIN1252

If you need UTF8 or any variable length charset, then your file should
pad to N * max_byte_per_char for that charset.

The Dummy column will contain the linebreaks.
You could also save the file as:


Then you can/must skip the Dummy column.

For numeric data, the best option is to write the values in text format
in the external file and declare as varchar, then cast in SQL to the
desired numeric format. otheriwse you'll have to store the numerics in
binary format in the external file, which makes that file very hard to
read for humans.

You'll need to edit a firebird.conf file entry as follows:

ExternalFileAccess = Restrict C:\MyDataFiles

Restart the Firebird service for that change to take effect .(For
superserver, that is. Classic will have a new process per connection so
I suppose it will take effect for each new connection).

Hope this helps.


blizzardb2001 wrote:

> I am trying to insert a list of seats (e.g. 01A, 02A .. 06P) into
> TblSeats on the server.
> So to insert say, 96 seats (01A to 06P) means 96 inserts and it's taking
> a long time, especially in the field where internet access may be lousy.
> >From what I understand my options are:
> 1. Stored Procedure or
> 2. Upload a TXT file into a temporary table on the Firebird Server, and
> get it to do the insert that way.
> Problem with 1 is that the creation of the seat name is hard to
> translate into STORED PROCEDURE code (you can get an idea of the Delphi
> code for this below if you like).
> problem with 2 is I don't know how. Can you provide some proper
> instructions? I can generate the TEXT file without your help but I'll
> need to know the format.
> Below is the Delphi code in case you can do a stored procedure:
> ============
> //Explanation: The user gives 3 inputs: 1. The device ID, 2. The number
> of Rows (e.g. 15 as in A to P) 3. The number of columns (e.g. 6 as in 1
> to 6)
> k := edtRows.AsInteger;
> l := edtColumns.AsInteger;
> with QrySeats do
> begin
> for j := 1 to l do
> for i := 1 to k do
> begin
> insert;
> fieldbyname('DeviceID').asinteger := QryDevice.FieldByName('ID').AsInteger;
> if length(inttostr(i)) > 1 then
> fieldbyname('SeatNumber').AsString := inttostr(i) + Char(65 + j - 1)
> else
> fieldbyname('SeatNumber').AsString := '0' + inttostr(i) + Char(65 + j - 1);
> post;
> end;
> end;

Kjell Rilbe
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64