Generate SQL insert statements from a CSV file

Question

I need to import a CSV file into Firebird, and I spent a couple of hours trying some tools, and none of them suited my needs.

The main problem is that all the tools I tried, such as EMS Data Import and Firebird Data Wizard, expect my CSV file to contain all the information needed for my table.

I need to write some custom SQL in the insert statement, for example, I have a CSV file with the name of the city, but since my database already has all the cities in another table (normalized), I need to write a sub-selection in the insert statement to search for the city and records its ID, I also have a stored procedure for creating GUIDS.

My insertion expression 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) 

How can I approach this?

Answer 1

This is a bit rude - but for one of the jobs, I sometimes use Excel.

If you import a CSV file into Excel, you can create a formula that creates the INSERT statement using the string concatenation in the formula. So, if your CSV file contains 3 columns that appear in columns A, B and C in Excel, you can write a formula, for example ...

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

Then you can repeat the formula along all lines, copy and paste the answer into a text file to run in your database.

Like I said - this is rude - but it can be a pretty "quick and dirty" way to get a job!

Answer 2

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

Answer 3

Well, if it's a CSV, and it's a one-time process, open the file in Excel, and then write the formulas to fill in your data the way you want, and then write a simple Concat formula to build your SQL, and then copy this formula for each strings. You will get a large number of SQL statements that you can execute anywhere.

Answer 4

Fabio

I did what Vaibhav did many times, and this is a good “quick and dirty” way to get data in a database.

If you need to do this several times or according to some type of schedule, then a more reliable way is to load the CSV data “as is” into the worksheet (ie customer_dataload), and then use standard SQL statements to fill in the missing fields.

(I don't know the syntax of Firebird, but something like ...)

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

and etc.

Usually, it is much faster (and more reliable) to get data in the database and then fix the data than trying to fix the data at boot time. You also benefit from transactions to let you rollback if it doesn't work!

Answer 5

You can import the CSV file into the table as is, and then write an SQL query that performs all the necessary transformations in the imported table and inserts the result into the target table.

So something like:

<(upload CSV file to temp_table - n, city_name)>

Paste into target_table

select tn, c.city_id as the city

from temp_table t, cities c

where t.city_name = c.city_name

Good advice on using Excel, but I also suggest getting it convenient with a scripting language such as Python, because for some tasks it’s easier to just write a quick python script to do the job than trying to find the Excel function you need or a tool ready to do the job .

Answer 6

Very simple online utility: Convert from / to CSV

Answer 7

You can try fbcopy and fbexport .

Answer 8

I would do it with awk .

For example, if you had this information in a CSV file:

 Bob,New York Jane,San Francisco Steven,Boston Marie,Los Angeles 

The following command will give you what you want, run in the same directory as your CSV file (named name-city.csv in this example).

 $ awk -F, '{ print "INSERT INTO PERSON (ID, NAME, CITY_ID) VALUES ((SELECT NEW_GUID FROM CREATE_GUID), '\''"$1"'\'', (SELECT CITY_ID FROM CITY WHERE NAME = '\''"$2"'\''))" }' name-city.csv 

Type awk --help for more information.

Answer 9

Just finished this VBA script, which may be convenient for this purpose. All you need to do is modify the Insert statement to include the corresponding table and list of columns (obviously, in the same sequence as in 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 10

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

Answer 11

You can use the free csvsql for this .

• Install using these instructions

• Now run the command to import the data into your database. More details on the links above, but it will be something like:

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

• The following works with sqlite, and this is what I use to convert data to a simple query format

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

Answer 12

I am using a small change in the Balloon Excel technique .

I highly recommend downloading the free ASAP Utilities plugin for Excel. One of the many time-saving tools that they include is pasting to the current value and pasting after the parameters of the current value .

This should help you find a solution faster by helping you create insert statements.

Answer 13

A tool I recently tried that worked great is FSQL .

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

Answer 14

option 1: 1- have you tried IBExert? IBExpert \ Tools \ Import data (trial version or client version).

option 2: 2. Download the csv file to the temporary table using F_BLOBLOAD. 3- create a stored procedure in which 3 functions were used (f_stringlength, f_strcopy, f_MID) you cross your entire line by pulling the 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 15

This csv to sql converter can generate all your csv data into MySQL insert requests.