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.

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.

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.

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/

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.

BULK INSERT

Other way is on Excell you build formula in new cells with data you want to export. The formula consists to format in strings and lenght to your field according lenght your field in firebird. So you can copy all this cells from excell and past on txt 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 formated data in txt 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.

Roberto Novakosky

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.

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.