从CSV文件到数据库表（SQL Server）插入记录 [英] Inserting Records From Csv File To Database Table (SQL Server)

问题描述

我需要做的是创建一个SQL Server脚本，以将记录从CSV文件插入到表中。我知道这可以很容易地使用BULK插入。

  BULK 
 INSERT TempTable 
 FROM 'C：\Records.csv'
 WITH 
（
 FIELDTERMINATOR ='，'，
 ROWTERMINATOR ='\\\
'
）
 
 

不幸的是，CSV文件包含不一致的限定符（）。某些字段可能有也可能没有限定符当手动创建CSV文件时（如下例所示）：

  10001，LeBron Quitter，CompanyA 
 10002， Insane，Charlie，CompanyB 
 10003，Donald Punk，CompanyC，CA
  

如果我对上述CSV格式使用上述代码，将会有错误，因为：

1.限定符将包含在表中（例如：Insane）

2.由于逗号（，）是fieldterminator，第二条记录将被认为是4个字段。

所以我不得不想到别的东西，因为我不想预处理CSV文件。它来到这个解决方案 - 使用MICROSOFT.JET.OLEDB.4.0。

  INSERT INTO MyRealTable 
 SELECT * FROM OPENROWSET（'MICROSOFT.JET.OLEDB.4.0'，'Text; Database = C：\ Temp \;'，'SELECT * FROM [Test.csv]'）
  / pre> 
 
 
注意：在执行上述代码之前，请务必具有访问权限/请执行以下命令：
  EXEC 
 sp_configure'show advanced options'，1 
重新配置
 EXEC 
 sp_configure'Ad Hoc Distributed Queries'，1 
重新配置
 EXEC 
 sp_configure'OLE自动化过程'，1; 
重新配置
 EXEC 
 sp_configure'Agent XPs'，1; 
重新配置
  
问题：
 
 1.我的解决方案是否正确一个？
 
 2.有其他更好的解决方案吗？
 
 3.因为我使用MICROSOFT.JET.OLEDB.4解决方案，应该安装/先决条件？ 
 
 
 
我非常愿意接受任何建议，批评或任何东西，因为我只是想了解更多...非常感谢你... 
解决方案
尝试在SQL Server中单独执行此操作可能是一个很大的挑战 - 我最喜欢做这个独立的独立控制台app或某事。
 
 
 
基本上，我会使用优秀的 FileHelpers 2.0库来处理CSV导入 - 就像一个魅力，真的很容易使用。您可以将任何固定宽度或分隔的文件导入到DataTable中。
 
 
 
一旦你有了，你可以转而使用 SqlBulkCopy 从您的应用程序批量加载到SQL Server。
 
 
 
代码看起来像这样：
  //定义您的输入记录 - 有什么字段，行和
 //字段是如何分隔的 -  FileHelpers的灵活性和强大功能是惊人的！ 
 
 [DelimitedRecord（，）] 
 public class InputRecord 
 {
 public int ID; 
 [FieldQuoted（''，QuoteMode.OptionalForBoth）] 
 public string PersonName; 
 [ 
} 
 
 //在控制台应用程序中，获取一个文件，使用FileHelpers将它导入内存，然后将它转换为DataTable并使用SqlBulkCopy将其插入SQL Server 
 static void Main（string [] args）
 {
 //从命令行参数中导入的grab文件名
 string fileNameToImport = args [0] 
 
 //实例化FileHelpers引擎
 FileHelperEngine engine = new FileHelperEngine（typeof（InputRecord））; 
 
 //将文件中的数据读入DataTable 
 DataTable records = engine.ReadFileAsDT（fileNameToImport）; 
 
 //创建你的SqlBulkCopy对象
 SqlBulkCopy bc = new SqlBulkCopy（server =（local）; database = TEST; Integrated Security = SSPI; 
 
 bc.DestinationTableName =TempTable; 
 
 //将数据批量复制到SQL Server 
 bc.WriteToServer（records）; 
} 
  
 
What I need to do is to create a SQL Server Script to insert records from a CSV file to a table. I know that this can be done easily using "BULK Insert".
BULK
INSERT TempTable
FROM 'C:\Records.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)
GO
Unfortunately, the CSV file contains inconsistent qualifiers ("). Some fields may or may not have this qualifiers when the CSV file is created (manually) like the sample below:
10001,LeBron Quitter,CompanyA
10002,"Insane, Charlie",CompanyB
10003,Donald Punk,"CompanyC,CA"
If I use the above code for the said CSV format, there will be errors because:

 1. Qualifier will be included in the table (Ex: "Insane)

 2. Since comma (,) is the fieldterminator, the 2nd record will be considered as 4 fields.  

So I have to think of something else since I don't want to preprocess the CSV file. It came down to this solution - using MICROSOFT.JET.OLEDB.4.0.
INSERT INTO MyRealTable
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [Test.csv]')
Note: Before executing the above code be sure to have access right/permission & execute the following:
EXEC
sp_configure 'show advanced options', 1
Reconfigure
EXEC
sp_configure 'Ad Hoc Distributed Queries', 1
Reconfigure
EXEC
sp_configure 'OLE Automation Procedures', 1;
Reconfigure
EXEC
sp_configure 'Agent XPs', 1;
Reconfigure
Questions:

 1. Is my solution the right one?

 2. Are there any other better solution?

 3. Since I'm using MICROSOFT.JET.OLEDB.4 solution, what should be installed/prerequisite?  

I'm very open to any suggestion, criticism, or anything because I just want to learn more... Thanks you very much in advance...
解决方案
Trying to do this in SQL Server alone is probably quite a challenge - I would most like do this as a separate, stand-alone console app or something.

Basically, I'd use the excellent FileHelpers 2.0 library to handle the CSV import - works like a charm and is really easy to use. You can import any fixed-width or delimited file into a DataTable.

Once you have that, you can then turn around and use SqlBulkCopy from your app to bulk load these into SQL Server.

The code would look something like this:
// define your input record - what fields are there, how are the rows and
// fields delimited - the flexibility and power of FileHelpers is amazing!

[DelimitedRecord(",")]
public class InputRecord
{
    public int ID;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string PersonName;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string CompanyName;
}

// in your console app, grab a file, import it into memory using FileHelpers,
// then convert it into a DataTable and use SqlBulkCopy to insert it into SQL Server
static void Main(string[] args)
{
    // grab file name to import from command-line arguments
    string fileNameToImport = args[0];

    // instantiate FileHelpers engine
    FileHelperEngine engine = new FileHelperEngine(typeof(InputRecord));

    // read the data from the file into a DataTable
    DataTable records = engine.ReadFileAsDT(fileNameToImport);

    // create your SqlBulkCopy object
    SqlBulkCopy bc = new SqlBulkCopy("server=(local);database=TEST;Integrated Security=SSPI;");

    bc.DestinationTableName = "TempTable";

    // bulk copy the data into SQL Server
    bc.WriteToServer(records);
}


                        
这篇关于从CSV文件到数据库表（SQL Server）插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！