从 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 文件包含所有信息我的桌子需要.

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.

我需要在插入语句中编写一些自定义 SQL，例如，我有一个带有城市名称的 CSV 文件，但是由于我的数据库已经在另一个表中包含所有城市(规范化)，我需要编写一个子选择在查找城市并写入其 ID 的插入语句中，我还有一个存储过程来读取 GUIDS.

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.

我的插入语句是这样的:

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)

我该如何处理?

推荐答案

这有点粗糙 - 但对于临时工作，我有时会使用 Excel.

It's a bit crude - but for one off jobs, I sometimes use Excel.

如果您将 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 & ")"

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

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屋！