生成从csv文件插入SQL语句 [英] Generate Insert SQL statements from a csv file

问题描述

我需要将csv文件导入 Firebird ，我花了几个小时试用了一些工具，无法满足我的需要。

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.

主要问题是我一直在尝试的所有工具 EMS数据导入和 Firebird数据向导期望我的 csv file包含我的表所需的所有信息。

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.

我需要在insert语句中写一些自定义SQL，例如，我有一个cvs文件名称，但因为我的数据库已经有所有城市在另一个表（规范化），我需要写一个subselect在insert语句查找城市和写它的ID，我有一个存储过程cread GUIDS。

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, CITY_ID) VALUES((SELECT NEW_GUID FROM CREATE_GUID), :NAME, (SELECT CITY_ID 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.

你能给我一些建议吗？

推荐答案

如果将CSV文件导入到Excel中，则可以创建一个公式来创建一个INSERT语句，方法是使用字符串连接。所以 - 如果您的CSV文件有3列显示在Excel中的列A，B和C，您可以写一个公式如...

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&）

="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!

这篇关于生成从csv文件插入SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！