CODEWITHSUNDEEP
csvimportfirebirdimport-from-excel
codeguru
codeguru

Reputation: 1851

How to load Excel or CSV file into Firebird?

I'm using Firebird database and I need to load Excel file into a database table. I need a tool that does this well. I tried some I found on Google, but all of them have some bugs.

Since Excel data is not created by me, it would be good if it could scan the file and discover what kind of data is inside and suggest a table to be created in the database.

Also, it would be nice if I could compare the file against the data that is already in the database table, and I can pick which data to load and which not.

Tools that load CSV files are also fine, I can "Save as" CSV from Excel before loading.

Upvotes: 4

Views: 27703

Answers (8)

Mark Rotteveel
Mark Rotteveel

Reputation: 109138

I have written a tool, Firebird External Table Generator that allows you to convert a CSV file into Firebird's external table file format. It is free and open source.

In its basic usage, this tool will convert a CSV to CHAR columns, allowing you to import the data as-is. It also allows you to modify its configuration, like changing column lengths, but also different column types.

As a simple example (copied from the user manual I wrote):

Using the following persons.csv:

ID,Lastname,Firstname,Email
1,Doe,John,[email protected]
2,Doe,Jane,[email protected]
3,Deer,Jason,[email protected]
4,Deer,Jillian,[email protected]

To convert the file, use the commandline (NOTE: I'm using Windows Command Prompt line continuations, replace ^ with \ for Linux):

ext-table-gen --csv-file=C:\FirebirdData\csv\persons.csv ^
  --table-file=C:\FirebirdData\exttables\persons.dat ^
  --config-out=C:\FirebirdData\csv\persons.xml

Replace C:\FirebirdData\ with the actual paths (or use relative paths, though I don't recommend that for reasons explained in the user manual).

The file persons.dat contains the external table data. In this form it is a fixed width text format, but if you modify the configuration to use other data types, it is actually a fixed width binary format.

The persons.xml contains the configuration used to generate persons.dat, and the DDL necessary to generate an external table to actually read it:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<extTableGenConfig xmlns="https://www.lawinegevaar.nl/xsd/ext-table-gen-1.0.xsd" schemaVersion="2.0">
    <externalTable name="DEFAULT_EXTERNAL_TABLE_NAME" byteOrder="LITTLE_ENDIAN">
        <columns>
            <column name="ID">
                <char length="1" encoding="ISO8859_1"/>
            </column>
            <column name="Lastname">
                <char length="4" encoding="ISO8859_1"/>
            </column>
            <column name="Firstname">
                <char length="7" encoding="ISO8859_1"/>
            </column>
            <column name="Email">
                <char length="19" encoding="ISO8859_1"/>
            </column>
            <endColumn type="LF"/>
        </columns>
        <tableFile path="C:\FirebirdData\exttables\persons.dat" overwrite="false"/>
    </externalTable>
    <tableDerivation columnEncoding="ISO8859_1" endColumnType="LF"/>
    <csvFile path="C:\FirebirdData\csv\persons.csv" charset="UTF-8" headerRow="true">
        <rfc4180CsvParser/>
    </csvFile>
    <informational>
        <ddl>create table "DEFAULT_EXTERNAL_TABLE_NAME" external file 'C:\FirebirdData\exttables\persons.dat' (
  "ID" char(1) character set ISO8859_1,
  "Lastname" char(4) character set ISO8859_1,
  "Firstname" char(7) character set ISO8859_1,
  "Email" char(19) character set ISO8859_1,
  "LF" char(1) character set ASCII default _ASCII x'0a'
);
</ddl>
    </informational>
</extTableGenConfig>

You can execute the DDL to create the external table in your Firebird database, and then query it (you will need to modify the ExternalFileAccess setting before you can read the file, see section Firebird configuration for details).

The user manual also describes how to make the configuration file more reusable by changing column lengths, and how to use different data types. After modifying the configuration file, you'll need to re-run the tool to export the file again with the new format (and to regenerate the DDL).

By default, RFC 4180 CSV format is used, but you can change the configuration to support other CSV file formats.

Upvotes: 0

Roberto Novakosky
Roberto Novakosky

Reputation: 371

BULK INSERT

Other way is on Excel you build formula in new cells with data you want to export. The formula consists to format in strings and length to your field according length your field in Firebird. So you can copy all this cells from Excel and paste into a text editor, so is possible to use the strategy of BULK INSERT in Firebird.

See more details in http://www.firebirdfaq.org/faq209/

The problem is if you have blob or null data to import, so see if you have this kind of values and if this way is to you. If you have formatted data in a text file, BULK INSERT will be quick way.

Hint: You can too to disable trigger and index associated with your table to accelerate BULK INSERT, and after enable them.

Upvotes: 1

Kjell Rilbe
Kjell Rilbe

Reputation: 1509

As far as I can see all replies so far focus on tools that essentially read the Excel (or CSV) file and uses SQL inserts to insert the records into the Firebird database. While this works, I have always found this approach painstakingly slow.

That's why I created a tool that reads an Excel file and writes one file that has a (text) format suitable for Firebird external table (including support for UTF8 char columns) and one DDL file to create the external table in Firebird.

I then use regular SQL to select from the external table, cast as needed, and insert into whatever normal Firebird table I want. The performance with this approach is orders of magnitude faster than SQL inserts from a client app in my experience.

I would be willing to publish the tool. It's written in C#. Let me know if there's any interest.

Upvotes: 0

kwlee
kwlee

Reputation: 11

I load the excel file to lazarus spreadsheet and then export to firebird db. Everythong is fine and the only problem is fpspreadsheet will consider string field with numbers only as a number field. I can check the titles in the first row to see whether the excel file is valid or not.

Upvotes: 1

Ibrahim.H
Ibrahim.H

Reputation: 1195

There are some online tools which can help you to generate DDL/DML scripts from csv header/sample dump file, check out: http://www.convertcsv.com/csv-to-sql.htm You can then use sql-workbench's Data Pumper or WbImport Tool from command line. Orbada has GUI which support for importing csv file also. DBeaver Free edition also support importing csv out of the box.

Upvotes: 0

jlenfers
jlenfers

Reputation: 489

Have you tried FSQL?

It's a freeware very similar to Firebird's standard ISQL, but with some extra features, like import data from CSV files.

I've used it with DBF files and it worked fine.

Upvotes: 1

Dani
Dani

Reputation: 2660

There is also EMS Data import tool for Firebird and Interbase http://www.sqlmanager.net/en/products/ibfb/dataimport

Not free, though, but it accepts a big variety of formats, including CSV and Excel.

EDIT

Another similar payware tool is Firebird Data Wizard http://www.sqlmaestro.com/products/firebird/datawizard/

Upvotes: 0

Milan Babuškov
Milan Babuškov

Reputation: 61158

Well, if you can use CSV, the I guess XMLWizard is the right tool for you. It can load a CSV file and compare with database data. And you can select the changes you wish to make to the table.

Don't let the name fool you, it does work with XML, but it also works very well with CSV files. And it can also estimate the column datatypes and offer CREATE TABLE statement for your file.

Upvotes: 4

Related Questions