Generate Insert SQL statements from a csv file

Question

I need to import a csv file into Firebird and I've spent a couple of hours trying out some tools and none fit my needs.

The main problem is that all the tools I've been trying like EMS Data Import and Firebird Data Wizard expect that my csv file contains all the information needed by my Table.

I need to write some custom SQL in the insert statement, for example, I have a cvs file with the city name, but as my database already has all the cities in another table (normalized), I need to write a subselect in the insert statement to lookup for the city and write its ID, also I have a stored procedure to cread GUIDS.

My Insert statement would be something like this:

INSERT INTO PERSON (ID, NAME, CITY_ID) VALUES((SELECT NEW_GUID FROM CREATE_GUID), :NAME, (SELECT CITY_ID FROM CITY WHERE NAME = :CITY_NAME)

I know that it is very easy to write an application to do this, but I don't like to reinvent the wheel, and I'm sure that there are some tools out there to do it.

Can you guys give me some advice?

Answer 1

Fabio,

I've done what Vaibhav has done many times, and it's a good "quick and dirty" way to get data into a database.

If you need to do this a few times, or on some type of schedule, then a more reliable way is to load the CSV data "as-is" into a work table (i.e customer_dataload) and then use standard SQL statements to populate the missing fields.

(I don't know Firebird syntax - but something like...)

UPDATE person
SET id = (SELECT newguid() FROM createguid)

UPDATE person
SET cityid = (SELECT cityid FROM cities WHERE person.cityname = cities.cityname)

etc.

Usually, it's much faster (and more reliable) to get the data INTO the database and then fix the data than to try to fix the data during the upload. You also get the benefit of transactions to allow you to ROLLBACK if it does not work!!

Answer 2

use the csv-file as an external table. Then you can use SQL to copy the data from the external table to your destination table - with all the possibilities of SQL. See http://www.firebirdsql.org/index.php?op=useful&id=netzka

Answer 3

You can use the free csvsql to do this.

• Install it using these instructions

• Now run a command like so to import your data into your database. More details at the links above, but it'd be something like:

  csvsql --db firebase:///d=mydb --insert mydata.csv

• The following works with sqlite, and is what I use to convert data into an easy to query format

  csvsql --db sqlite:///dump.db --insert mydata.csv

Answer 4

option 1: 1- have you tried IBExert? IBExpert \ Tools \ Import Data (Trial or Customer Version).

option 2: 2- upload your csv file to a temporary table with F_BLOBLOAD. 3- create a stored procedure, which used 3 functions (f_stringlength, f_strcopy, f_MID) you cross all your string, pulling your fields to build your INSERT INTO.

links: 2: http://freeadhocudf.org/documentation_english/dok_eng_file.html 3: http://freeadhocudf.org/documentation_english/dok_eng_string.html

Answer 5

A tool I recently tried that worked outstandingly well is FSQL.

You write an IMPORT command, paste it into FSQL and it imports the CSV file into the Firebird table.

Answer 6

You could import the CSV file into a table as is, then write an SQL query that does all the required transformations on the imported table and inserts the result into the target table.

So something like:

<(load the CSV file into temp_table - n, city_name)>

insert into target_table

select t.n, c.city_id as city

from temp_table t, cities c

where t.city_name = c.city_name

Nice tip about using Excel, but I also suggest getting comfortable with a scripting language like Python, because for some task it's easier to just write a quick python script to do the job than trying to find the function you need in Excel or a pre-made tool that does the job.