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, CITYID) VALUES((SELECT NEWGUID FROM CREATEGUID), :NAME, (SELECT CITYID 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

Well, if it's a CSV, and it this is a one time process, open up the file in Excel, and then write formulas to populate your data in any way you desire, and then write a simple Concat formula to construct your SQL, and then copy that formula for every row. You will get a large number of SQL statements which you can execute anywhere you want.

Answer 2

It's a bit crude - but for one off jobs, I sometimes use Excel.

If you import the CSV file into Excel, you can create a formula which creates an INSERT statement by using string concatenation in the formula. So - if your CSV file has 3 columns that appear in columns A, B and C in Excel, you could write a formula like...

="INSERT INTO MyTable (Col1, Col2, Col3) VALUES (" & A1 & ", " & B1 & ", " & C1 & ")"

Then you can replicate the formula down all of your rows, and copy and paste the answer into a text file to run against your database.

Like I say - it's crude - but it can be quite a 'quick and dirty' way of getting a job done!

Answer 3

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 temptable - n, cityname)>

insert into target_table

select t.n, c.city_id as city

from temp_table t, cities c

where t.cityname = c.cityname

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.

Answer 4

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 5

I sometimes use The World's Simplest Code Generator (Javascript edition). It's online, but it's just javascript - your data doesn't go anywhere. There's also an asp version though, with more features.

Answer 6

awk

Answer 7

I use a slight variation on Balloon's Excel technique.

I highly recommend downloading the free ASAP Utilities plug-in for Excel. One of the many time saving tools they include are insert before current value and insert after current value options.

Those should let you reach a solution quicker by helping you build your insert statements.

Answer 8

You could try fbcopy and fbexport tools.

Answer 9

Try http://www.sqlscripter.com to generate insert scripts of your text/csv file.

Answer 10

I tool I tried recently that worked outstandingly well is FSQL (www.volny.cz/iprenosil/interbase/fsql.htm). You write an IMPORT command, paste it into FSQL and it imports the CSV file into the Firebird table.

Answer 11

Just finished this VBA script which might be handy for this purpose. All should need to do is change the Insert statement to include the table in question and the list of columns (obviously in the same sequence they appear on the Excel file).

Function CreateInsertStatement()

'Output file location and start of the insert statement
SQLScript = "C:\Inserts.sql"
cStart = "Insert Into Holidays (HOLIDAY_ID, NAT_HOLDAY_DESC, NAT_HOLDAY_DTE) Values ("

'Open file for output
Open SQLScript For Output As #1

Dim LoopThruRows As Boolean
Dim LoopThruCols As Boolean


nCommit = 1 'Commit Count
nCommitCount = 100 'The number of rows after which a commit is performed

LoopThruRows = True
nRow = 1 'Current row

While LoopThruRows

    nRow = nRow + 1 'Start at second row - presuming there are headers
    nCol = 1 'Reset the columns
    If Cells(nRow, nCol).Value = Empty Then
        Print #1, "Commit;"
        LoopThruRows = False
    Else
        If nCommit = nCommitCount Then
            Print #1, "Commit;"
            nCommit = 1
        Else
            nCommit = nCommit + 1
        End If

        cLine = cStart
        LoopThruCols = True

        While LoopThruCols
            If Cells(nRow, nCol).Value = Empty Then
                cLine = cLine & ");"                    'Close the SQL statement
                Print #1, cLine                         'Write the line
                LoopThruCols = False                    'Exit the cols loop
            Else
                If nCol > 1 Then                        'add a preceeding comma for all bar the first column
                    cLine = cLine & ", "
                End If
                If Right(Left(Cells(nRow, nCol).Value, 3), 1) = "/" Then 'Format for dates
                    cLine = cLine & "TO_DATE('" & Cells(nRow, nCol).Value & "', 'dd/mm/yyyy')"
                ElseIf IsNumeric(Left(Cells(nRow, nCol).Value, 1)) Then 'Format for numbers
                    cLine = cLine & Cells(nRow, nCol).Value
                Else 'Format for text, including apostrophes
                    cLine = cLine & "'" & Replace(Cells(nRow, nCol).Value, "'", "''") & "'"
                End If

                nCol = nCol + 1
            End If
        Wend
    End If
Wend

Close #1

End Function

Answer 12

Fabio,

Create an additional (temporary) table and use a tool (like Database Workbench) to import the CSV file.

Next, create a SQL statement or Stored Procedure that does the additional processing, you can fully manipulate the CSV data once in the database or update another table by using the CSV data and parts of your current data.

Answer 13

You can use Advanced ETL Processor to generate insert statements

Data from both tables and views can be scripted

Support for text files

Table names and column names with spaces are handled

All datatypes are handled

NULLs are gracefully handled

Timestamp columns are handled

Identity columns are handled

Very flexible and configurable

Computed columns are handled

You can filter the rows/lines for which you want to generate INSERTs

http://www.dbsoftlab.com/Advanced-ETL-Processor-News/Generate-Insert-Statements.html

Answer 14

Generate SQL Script from Excel

http://www.geekblogger.org/2010/10/giveaway-querycell-license-worth-45-for.html

Hey we have started contest to win Querycell excel add-on by which you can Generate SQL Script from Excel on a single click of mouse.