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

<p>The main problem is that all the tools I've been trying like <a href="http://www.sqlmanager.net/products/ibfb/dataimport" rel="noreferrer">EMS Data Import</a> and <a href="http://www.sqlmaestro.com/products/firebird/datawizard/help/06_01_05_csv/" rel="noreferrer">Firebird Data Wizard</a> expect that my CSV file contains all the information needed by my Table.</p>

<p>I need to write some custom SQL in the insert statement, for example, I have a CSV 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.</p>

<p>My insert statement would be something like this:</p>

<pre><code>INSERT INTO PERSON (ID, NAME, CITY_ID) VALUES((SELECT NEW_GUID FROM CREATE_GUID), :NAME, (SELECT CITY_ID FROM CITY WHERE NAME = :CITY_NAME)
</code></pre>

<p>How can I approach this?</p>

[{'answer_id': 8216, 'author': 'Vaibhav', 'author_id': 380, 'author_profile': 'https://Stackoverflow.com/users/380', 'pm_score': 3, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 8220, 'author': 'Chris Roberts', 'author_id': 475, 'author_profile': 'https://Stackoverflow.com/users/475', 'pm_score': 8, 'selected': True, 'text': '<p>It\'s a bit crude - but for one off jobs, I sometimes use Excel.</p>\n\n<p>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...</p>\n\n<pre><code>="INSERT INTO MyTable (Col1, Col2, Col3) VALUES (" &amp; A1 &amp; ", " &amp; B1 &amp; ", " &amp; C1 &amp; ")"\n</code></pre>\n\n<p>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.</p>\n\n<p>Like I say - it\'s crude - but it can be quite a \'quick and dirty\' way of getting a job done!</p>\n'}, {'answer_id': 8227, 'author': 'Nickolay', 'author_id': 1026, 'author_profile': 'https://Stackoverflow.com/users/1026', 'pm_score': 2, 'selected': False, 'text': "<p>You could import the CSV file into a database table <em>as is</em>, then run an SQL query that does all the required transformations on the imported table and inserts the result into the target table.</p>\n<p>Assuming the CSV file is imported into <code>temp_table</code> with columns <code>n</code>, <code>city_name</code>:</p>\n<pre><code> insert into target_table\n select t.n, c.city_id as city \n from temp_table t, cities c\n where t.city_name = c.city_name\n</code></pre>\n<p>Nice tip about using Excel, but I also suggest getting comfortable with a scripting language like Python, because for some tasks 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.</p>\n"}, {'answer_id': 8230, 'author': 'Guy', 'author_id': 993, 'author_profile': 'https://Stackoverflow.com/users/993', 'pm_score': 3, 'selected': False, 'text': '<p>Fabio,</p>\n\n<p>I\'ve done what Vaibhav has done many times, and it\'s a good "quick and dirty" way to get data into a database.</p>\n\n<p>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.</p>\n\n<p>(I don\'t know Firebird syntax - but something like...)</p>\n\n<pre><code>UPDATE person\nSET id = (SELECT newguid() FROM createguid)\n\nUPDATE person\nSET cityid = (SELECT cityid FROM cities WHERE person.cityname = cities.cityname)\n</code></pre>\n\n<p>etc.</p>\n\n<p>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!!</p>\n'}, {'answer_id': 8417, 'author': 'Terry G Lorber', 'author_id': 809, 'author_profile': 'https://Stackoverflow.com/users/809', 'pm_score': 3, 'selected': False, 'text': '<p>I\'d do this with <a href="http://www.grymoire.com/Unix/Awk.html" rel="noreferrer">awk</a>.</p>\n\n<p>For example, if you had this information in a CSV file:</p>\n\n<pre><code>Bob,New York\nJane,San Francisco\nSteven,Boston\nMarie,Los Angeles\n</code></pre>\n\n<p>The following command will give you what you want, run in the same directory as your CSV file (named <code>name-city.csv</code> in this example).</p>\n\n<pre><code>$ 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\n</code></pre>\n\n<p>Type <code>awk --help</code> for more information.</p>\n'}, {'answer_id': 2111127, 'author': 'Stan', 'author_id': 255991, 'author_profile': 'https://Stackoverflow.com/users/255991', 'pm_score': 0, 'selected': False, 'text': '<p>A tool I recently tried that worked outstandingly well is <a href="http://www.volny.cz/iprenosil/interbase/fsql.htm" rel="nofollow noreferrer">FSQL</a>.</p>\n\n<p>You write an IMPORT command, paste it into <code>FSQL</code> and it imports the CSV file into the Firebird table.</p>\n'}, {'answer_id': 2208584, 'author': 'James C', 'author_id': 267222, 'author_profile': 'https://Stackoverflow.com/users/267222', 'pm_score': 1, 'selected': False, 'text': '<p>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).</p>\n\n<pre><code>Function CreateInsertStatement()\n    \'Output file location and start of the insert statement\n    SQLScript = "C:\\Inserts.sql"\n    cStart = "Insert Into Holidays (HOLIDAY_ID, NAT_HOLDAY_DESC, NAT_HOLDAY_DTE) Values ("\n\n    \'Open file for output\n    Open SQLScript For Output As #1\n\n    Dim LoopThruRows As Boolean\n    Dim LoopThruCols As Boolean\n\n\n    nCommit = 1 \'Commit Count\n    nCommitCount = 100 \'The number of rows after which a commit is performed\n\n    LoopThruRows = True\n    nRow = 1 \'Current row\n\n    While LoopThruRows\n\n        nRow = nRow + 1 \'Start at second row - presuming there are headers\n        nCol = 1 \'Reset the columns\n        If Cells(nRow, nCol).Value = Empty Then\n            Print #1, "Commit;"\n            LoopThruRows = False\n        Else\n            If nCommit = nCommitCount Then\n                Print #1, "Commit;"\n                nCommit = 1\n            Else\n                nCommit = nCommit + 1\n            End If\n\n            cLine = cStart\n            LoopThruCols = True\n\n            While LoopThruCols\n                If Cells(nRow, nCol).Value = Empty Then\n                    cLine = cLine &amp; ");"                    \'Close the SQL statement\n                    Print #1, cLine                         \'Write the line\n                    LoopThruCols = False                    \'Exit the cols loop\n                Else\n                    If nCol &gt; 1 Then                        \'add a preceeding comma for all bar the first column\n                        cLine = cLine &amp; ", "\n                    End If\n                    If Right(Left(Cells(nRow, nCol).Value, 3), 1) = "/" Then \'Format for dates\n                        cLine = cLine &amp; "TO_DATE(\'" &amp; Cells(nRow, nCol).Value &amp; "\', \'dd/mm/yyyy\')"\n                    ElseIf IsNumeric(Left(Cells(nRow, nCol).Value, 1)) Then \'Format for numbers\n                        cLine = cLine &amp; Cells(nRow, nCol).Value\n                    Else \'Format for text, including apostrophes\n                        cLine = cLine &amp; "\'" &amp; Replace(Cells(nRow, nCol).Value, "\'", "\'\'") &amp; "\'"\n                    End If\n\n                    nCol = nCol + 1\n                End If\n            Wend\n        End If\n    Wend\n\n    Close #1\n\nEnd Function\n</code></pre>\n'}, {'answer_id': 5248265, 'author': 'peponloqui', 'author_id': 163602, 'author_profile': 'https://Stackoverflow.com/users/163602', 'pm_score': 0, 'selected': False, 'text': '<p>option 1:\n1- have you tried IBExert?  IBExpert \\ Tools \\ Import Data (Trial or Customer Version).</p>\n\n<p>option 2:\n2- upload your csv file to a temporary table with F_BLOBLOAD.\n3- create a stored procedure, which used 3 functions (f_stringlength, f_strcopy, f_MID)\nyou cross all your string, pulling your fields to build your INSERT INTO.</p>\n\n<p>links:\n2: <a href="http://freeadhocudf.org/documentation_english/dok_eng_file.html" rel="nofollow">http://freeadhocudf.org/documentation_english/dok_eng_file.html</a>\n3: <a href="http://freeadhocudf.org/documentation_english/dok_eng_string.html" rel="nofollow">http://freeadhocudf.org/documentation_english/dok_eng_string.html</a></p>\n'}, {'answer_id': 5249181, 'author': 'Christoph Theuring', 'author_id': 651982, 'author_profile': 'https://Stackoverflow.com/users/651982', 'pm_score': 1, 'selected': False, 'text': '<p>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.\nSee <a href="http://www.firebirdsql.org/index.php?op=useful&amp;id=netzka" rel="nofollow">http://www.firebirdsql.org/index.php?op=useful&amp;id=netzka</a> </p>\n'}, {'answer_id': 42628109, 'author': 'Brad Parks', 'author_id': 26510, 'author_profile': 'https://Stackoverflow.com/users/26510', 'pm_score': 2, 'selected': False, 'text': '<p>You can use the free <a href="http://csvkit.readthedocs.io/en/0.4.4/scripts/csvsql.html" rel="nofollow noreferrer">csvsql</a> to do this. </p>\n\n<ul>\n<li>Install it <a href="http://csvkit.readthedocs.io/en/0.9.1/index.html" rel="nofollow noreferrer">using these instructions</a></li>\n<li><p>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:</p>\n\n<p><code>csvsql --db firebase:///d=mydb --insert mydata.csv</code></p></li>\n<li><p>The following works with sqlite, and is what I use to convert data into an easy to query format</p>\n\n<p><code>csvsql --db sqlite:///dump.db --insert mydata.csv</code></p></li>\n</ul>\n'}, {'answer_id': 61274589, 'author': 'Judy1989', 'author_id': 1652397, 'author_profile': 'https://Stackoverflow.com/users/1652397', 'pm_score': 0, 'selected': False, 'text': '<p>you can use shell</p>\n\n<pre><code>sed "s/,/\',\'/g" file.csv &gt; tmp\nsed "s/$/\'),(/g" tmp &gt; tmp2\nsed "s/^./\'&amp;/g" tmp2 &gt; insert.sql\n</code></pre>\n\n<p>and then add </p>\n\n<pre><code>INSERT INTO PERSON (ID, NAME, CITY_ID) VALUES(\n...\n);\n</code></pre>\n'}, {'answer_id': 63496757, 'author': 'ESP32', 'author_id': 3177115, 'author_profile': 'https://Stackoverflow.com/users/3177115', 'pm_score': 2, 'selected': False, 'text': '<p>Two online tools which helped me in <strong>2020</strong>:</p>\n<p><a href="https://numidian.io/convert/csv/to/sql" rel="nofollow noreferrer">https://numidian.io/convert/csv/to/sql</a></p>\n<p><a href="https://www.convertcsv.com/csv-to-sql.htm" rel="nofollow noreferrer">https://www.convertcsv.com/csv-to-sql.htm</a></p>\n<p>The second one is based on JS and does not upload your data (at least not at the time I am writing this)</p>\n'}]
['https://Stackoverflow.com/questions/8213', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/727/']