从CSV文件生成插入SQL语句

Question

我需要将一个csv文件导入到Firebird中，尝试了一些工具都不符合我的需求。主要问题是我尝试过的所有工具（如EMS Data Import 和 Firebird Data Wizard）都期望我的CSV文件包含表中所需的所有信息。

我需要在插入语句中编写一些自定义SQL，例如，我有一个包含城市名称的CSV文件，但由于我的数据库已经在另一张表中拥有了所有的城市（规范化），因此我需要在插入语句中写一个子查询来查找城市并写入它的ID，另外我还有一个存储过程来创建GUIDS。

我的插入语句应该像这样：

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

我该如何处理这个问题？

- Fabio Gomes

---

我有时会使用全球最简单的代码生成器（Javascript版）。它是在线的，但只是javascript - 您的数据不会被发送到任何地方。还有一个asp版本，具有更多功能。 - Blorgbeard

我在Balloon的Excel技巧上做了一些微小的变化。我强烈建议下载免费的ASAP Utilities插件，它是Excel的一个时间节省工具，其中包括当前值之前插入和当前值之后插入选项。这些选项应该能让你找到解决方案。 - berberich

非常简单的在线工具：CSV转换器，可进行CSV格式文件的转换。 - hdoghmen

很好，关于十进制类型有一些问题。 - Kiquenet

一个更好的转换工具：http://codebeautify.org/csv-to-sql-converter - ahgood

这个csv转sql转换器可以将您的所有csv数据生成为MySQL插入查询。 - ChethiyaKD

Answer 1

虽然有点简陋，但对于一次性的工作，我有时会使用Excel。

如果您将CSV文件导入Excel中，可以创建一个公式，通过在公式中使用字符串连接来创建INSERT语句。因此，如果您的CSV文件有3列出现在Excel中的A、B和C列中，您可以编写类似以下的公式...

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

然后，您可以将公式复制到所有行中，并将答案复制并粘贴到文本文件中，以便与数据库运行。

就像我所说的那样-虽然有些简陋，但这是完成工作的一种相当快速而直接的方法！

- Chris Roberts

---

你也可以使用一个好的编辑器（例如vim）打开文件，并在每一行上应用一个快速宏。 - Luc M

Answer 2

如果这是一个CSV文件，而且这只是一次性的过程，那么在Excel中打开文件，编写公式以任何你想要的方式填充数据，然后编写一个简单的Concat公式来构建你的SQL语句，然后复制该公式到每一行。你将得到大量的SQL语句，可以在任何地方执行。

- Vaibhav

Answer 3

Fabio,

我已经像Vaibhav那样多次完成了这项任务，这是一种快速而简便的将数据导入数据库的方法。

如果您需要多次执行此操作或按计划进行操作，则更可靠的方法是将CSV数据“原样”加载到工作表中（即customer_dataload），然后使用标准SQL语句填充缺失的字段。

（我不知道Firebird语法-但类似于...）

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

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

通常情况下，将数据导入数据库并在那里修复数据比在上传过程中尝试修复数据要快得多（也更可靠）。您还可以利用事务的好处，如果它不起作用，可以回滚！

等等。

- Guy

Answer 4

我会使用awk来完成这个任务。

比如说，如果你有一个CSV文件包含如下信息：

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

以下命令将会给你想要的结果，需在CSV文件相同目录下运行（在此示例中命名为name-city.csv）。

$ 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

输入awk --help获取更多信息。

- Terry G Lorber

---

这非常不给帮助。花几秒钟时间制作一个简单的示例，可以节省那些不知道如何使用awk的人数个小时的时间。 - Anthony

Answer 5

在2020年帮助我解决问题的两个在线工具：

https://numidian.io/convert/csv/to/sql

https://www.convertcsv.com/csv-to-sql.htm

第二个工具基于JS，不会上传你的数据（至少在我撰写本文时是这样）

- ESP32

Answer 6

你可以直接将CSV文件导入数据库表中，然后运行一个SQL查询，在导入的表上执行所有需要的转换，并将结果插入到目标表中。

假设CSV文件已经被导入到名为“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

关于使用Excel的技巧很不错，但我建议您也要熟悉像Python这样的脚本语言，因为对于某些任务来说，编写一个快速的Python脚本来完成工作比在Excel中查找所需函数或使用预制工具更容易。

- Nickolay

Answer 7

您可以使用免费的csvsql来完成此操作。

• 按照这些说明安装它

• 现在运行像这样的命令将数据导入您的数据库。更多细节请参见上面的链接，但是类似于以下内容：

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

• 以下适用于sqlite，是我用来将数据转换为易于查询格式的方法：

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

- Brad Parks

Answer 8

刚刚完成了这个 VBA 脚本，可能对此有用。所有需要做的就是更改插入语句以包括相关表格和列的列表（显然按照它们在 Excel 文件中出现的顺序）。

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

- James C

Answer 9

使用csv文件作为外部表。然后，您可以使用SQL将数据从外部表复制到目标表中 - 具有SQL的所有可能性。 请参见http://www.firebirdsql.org/index.php?op=useful&id=netzka

- Christoph Theuring

---

+1 要提及外部表，但需要确保数据以定长记录和字段的方式存储，使用 CHAR(n)，并授予相应权限，同时在 firebird.conf 中允许文件访问，设置 ExternalFileAccess = Full 和 DatabaseAccess = Full。 - Lumi

Answer 10

我最近尝试过的一个非常出色的工具是FSQL。

你可以编写一个IMPORT命令，将其粘贴到FSQL中，它就会将CSV文件导入到Firebird表中。

- Stan

---

链接似乎已失效。 - lorefnon