从 CSV 文件生成插入 SQL 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8213/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generate insert SQL statements from a CSV file
提问by Fabio Gomes
I need to import a csv file into Firebirdand I've spent a couple of hours trying out some tools and none fit my needs.
我需要将一个 csv 文件导入Firebird，我花了几个小时尝试了一些工具，但没有一个适合我的需要。
The main problem is that all the tools I've been trying like EMS Data Importand Firebird Data Wizardexpect that my CSV file contains all the information needed by my Table.
主要问题是我一直在尝试的所有工具（如EMS 数据导入和Firebird 数据向导）都希望我的 CSV 文件包含我的表所需的所有信息。
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.
我需要在插入语句中编写一些自定义 SQL，例如，我有一个带有城市名称的 CSV 文件，但是由于我的数据库已经在另一个表中包含了所有城市（标准化），我需要在插入中编写一个子选择语句来查找城市并写入它的 ID，我还有一个存储过程来创建 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)
How can I approach this?
我该如何解决这个问题？
回答by Chris Roberts
It's a bit crude - but for one off jobs, I sometimes use Excel.
这有点粗糙 - 但对于一次性工作，我有时会使用 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...
如果将 CSV 文件导入 Excel，则可以创建一个公式，该公式通过在公式中使用字符串连接来创建 INSERT 语句。所以 - 如果您的 CSV 文件有 3 列出现在 Excel 的 A、B 和 C 列中，您可以编写一个公式，例如...
="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!
就像我说的 - 这很粗糙 - 但它可以是一种“快速而肮脏”的完成工作的方式！
回答by Vaibhav
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.
好吧，如果它是一个 CSV，并且这是一个一次性过程，请在 Excel 中打开该文件，然后编写公式以您想要的任何方式填充数据，然后编写一个简单的 Concat 公式来构建您的 SQL，然后然后为每一行复制该公式。你会得到大量的 SQL 语句，你可以在任何你想要的地方执行它们。
回答by Guy
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.
我已经做了很多次 Vaibhav 做过的事情，这是将数据导入数据库的一种“快速而肮脏”的好方法。
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.
如果您需要多次执行此操作或按某种类型的计划执行此操作，则更可靠的方法是将 CSV 数据“按原样”加载到工作表（即 customer_dataload）中，然后使用标准 SQL 语句填充缺少字段。
(I don't know Firebird syntax - but something like...)
（我不知道 Firebird 语法 - 但类似......）
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!!
通常，将数据导入数据库然后修复数据比在上传期间尝试修复数据要快得多（也更可靠）。您还可以获得交易的好处，如果它不起作用，您可以回滚！！
回答by Nickolay
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.
您可以按原样将 CSV 文件导入表中，然后编写 SQL 查询，对导入的表执行所有必需的转换，并将结果插入目标表中。
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
<（将 CSV 文件加载到 temp_table - n, city_name）>
插入目标表
选择 tn, c.city_id 作为城市
来自 temp_table t，城市 c
其中 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.
关于使用 Excel 的好提示，但我也建议熟悉像 Python 这样的脚本语言，因为对于某些任务，编写一个快速的 Python 脚本来完成这项工作比尝试在 Excel 或预做这项工作的工具。
回答by Terry G Lorber
I'd do this with awk.
我会用awk做到这一点。
For example, if you had this information in a CSV file:
例如，如果您在 CSV 文件中有此信息：
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.csvin this example).
以下命令将为您提供所需的内容，在与 CSV 文件（name-city.csv在此示例中命名）相同的目录中运行。
$ awk -F, '{ print "INSERT INTO PERSON (ID, NAME, CITY_ID) VALUES ((SELECT NEW_GUID FROM CREATE_GUID), '\''""'\'', (SELECT CITY_ID FROM CITY WHERE NAME = '\''""'\''))" }' name-city.csv
Type awk --helpfor more information.
键入awk --help以获取更多信息。
回答by James C
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).
刚刚完成了这个 VBA 脚本，这对于这个目的可能很方便。所需要做的就是更改Insert 语句以包含有问题的表和列列表（显然与它们出现在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
回答by Christoph Theuring
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
使用 csv 文件作为外部表。然后您可以使用 SQL 将数据从外部表复制到目标表 - 具有 SQL 的所有可能性。见http://www.firebirdsql.org/index.php?op=useful&id=netzka
回答by Brad Parks
You can use the free csvsqlto do this.
您可以使用免费的csvsql来执行此操作。
- 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.csvThe 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
- 使用这些说明安装它
现在运行类似这样的命令将您的数据导入到您的数据库中。在上面的链接中有更多详细信息，但它会是这样的：
csvsql --db firebase:///d=mydb --insert mydata.csv以下适用于 sqlite，是我用来将数据转换为易于查询的格式的内容
csvsql --db sqlite:///dump.db --insert mydata.csv
回答by Judy1989
you can use shell
你可以使用外壳
sed "s/,/','/g" file.csv > tmp
sed "s/$/'),(/g" tmp > tmp2
sed "s/^./'&/g" tmp2 > insert.sql
and then add
然后添加
INSERT INTO PERSON (ID, NAME, CITY_ID) VALUES(
...
);
回答by peponloqui
option 1: 1- have you tried IBExert? IBExpert \ Tools \ Import Data (Trial or Customer Version).
选项 1：1- 您是否尝试过 IBExert？IBExpert \ Tools \ 导入数据（试用版或客户版）。
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.
选项 2：2- 使用 F_BLOBLOAD 将您的 csv 文件上传到临时表。3- 创建一个存储过程，它使用了 3 个函数（f_stringlength、f_strcopy、f_MID），你跨过所有的字符串，拉动你的字段来构建你的 INSERT INTO。
links: 2: http://freeadhocudf.org/documentation_english/dok_eng_file.html3: http://freeadhocudf.org/documentation_english/dok_eng_string.html
链接：2：http: //freeadhocudf.org/documentation_english/dok_eng_file.html3：http: //freeadhocudf.org/documentation_english/dok_eng_string.html
