Subject Re: {Disarmed} [firebird-support] How to import xls or csv into firebird database table?
Author Kjell Rilbe
Den 2011-03-11 10:15 skrev Banaru såhär:
> --- In
> <>, Kjell Rilbe
> <kjell.rilbe@...> wrote:
> > Is it possible for you to export fixed width format instead of delimited
> > format? In that case you can import into FB using an external table.
> >
> Please, Kjell be more explicit with this fixed width format.

Assume table like this:
Id int (max 9 digits)
Name varchar(20)

E.g. instead of this:

You create this format (undescores to signify spaces to avoid problems
with stripped whitespace):

Then create an external table (look it up in the FB docs) with this DDL:

create table "ToBeImported" external file 'yourpathandfilename' (
"Id" char(9),
"Name" char(20),
"Dummy" char(2) -- Assuming <cr><lf> linebreaks as in Windows

Then execute sql like this:

insert into "YourRealDataTabel" (
select cast("Id" as int) "Id",
cast("Name" as varchar(20)) "Name"
from "ToBeImported"

I may have got the ext table DDL wrong but look it up and fix the
errors. You shoud get the general idea...

I've also seen someone post Delphi code that creates a file that lets
you declare the external table with nativs FB types like int and varchar.

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