将CSV文件上传到SQL服务器 [英] Upload CSV file to SQL server

问题描述

使用C＃将大型 csv 数据文件上传到SQL服务器的最佳方法是什么？该文件包含大约30,000行和25列。

解决方案

第一次，你不需要编程的东西。您可以使用SQL管理工具将CSV文件直接上传到SQL数据库。但是，如果你真的需要通过编程来做，只需阅读下面。

我个人认为，这种方法是通过编程做最有效和最简单的方法。

第一步骤是读取CSV文件并将记录保存为 DataTable 。

第二步骤是存储检索 DataTable 到SQL数据库表作为批量条目

这是一个函数，返回CSV文件数据作为 DataTable 。

此函数将返回CSV读取文件到DataTable。

  private static DataTable GetDataTabletFromCSVFile（string csv_file_path）
 {
 DataTable csvData = new DataTable 
 try 
 {
 using（TextFieldParser csvReader = new TextFieldParser（csv_file_path））
 {
 csvReader.SetDelimiters（new string [] {，}）; 
 csvReader.HasFieldsEnclosedInQuotes = true; 
 string [] colFields = csvReader.ReadFields（）; 
 foreach（colFields中的字符串列）
 {
 DataColumn datecolumn = new DataColumn（column）; 
 datecolumn.AllowDBNull = true; 
 csvData.Columns.Add（datecolumn）; 
} 
 while（！csvReader.EndOfData）
 {
 string [] fieldData = csvReader.ReadFields（）; 
 //使空值为空
 for（int i = 0; i  {
 if（fieldData [i] == ）
 {
 fieldData [i] = null; 
} 
} 
 csvData.Rows.Add（fieldData）; 
} 
} 
} 
 catch（Exception ex）
 {
} 
 return csvData; 
} 
} 
  

SQLBulkCopy - 将数据表检索到Sql表中

  static void InsertDataIntoSQLServerUsingSQLBulkCopy（DataTable csvFileData）
 {
 using （SqlConnection dbConnection = new SqlConnection（Data Source = ProductHost;初始目录= yourDB;集成安全= SSPI;））
 {
 dbConnection.Open 
 using（SqlBulkCopy s = new SqlBulkCopy（dbConnection））
 {
 s.DestinationTableName =您的表名; 
 foreach（csvFileData.Columns中的var列）
 s.ColumnMappings.Add（column.ToString（），column.ToString（））; 
 s.WriteToServer（csvFileData）; 
} 
} 
} 
  

来源

What is the best way to upload a large csv data file into SQL server using C# ? The file contains about 30,000 rows and 25 columns.

解决方案

1st off, You don't need programming stuff. You can directly upload CSV files into SQL Database with SQL management tools. However, if you really need do it through programming, Just read below.

Personally, I think this approach is the most efficient and easiest way to do through programming.

In general, you can achieve it in two steps

1st step is to read the CSV file and hold the records as a DataTable.
2nd step is store the retrieved DataTable into SQL Database Table as a Bulk Entry

This is a function that returns CSV File Data as a DataTable. Call and Keep it in the memory and you can do whatever you want with it.

This function is going to return CSV Read file into DataTable.

private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
    {
        DataTable csvData = new DataTable();
        try
        {
          using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
             {
                csvReader.SetDelimiters(new string[] { "," });
                csvReader.HasFieldsEnclosedInQuotes = true;
                string[] colFields = csvReader.ReadFields();
                foreach (string column in colFields)
                {
                    DataColumn datecolumn = new DataColumn(column);
                    datecolumn.AllowDBNull = true;
                    csvData.Columns.Add(datecolumn);
                }
                while (!csvReader.EndOfData)
                {
                    string[] fieldData = csvReader.ReadFields();
                    //Making empty value as null
                    for (int i = 0; i < fieldData.Length; i++)
                    {
                        if (fieldData[i] == "")
                        {
                            fieldData[i] = null;
                        }
                    }
                    csvData.Rows.Add(fieldData);
                }
            }
        }
        catch (Exception ex)
        {
        }
        return csvData;
    }
  }

SQLBulkCopy - Use this function to insert the Retrieved DataTable into Sql Table

static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
{
    using(SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=yourDB;Integrated Security=SSPI;"))
    {
         dbConnection.Open();
         using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
         {
             s.DestinationTableName = "Your table name";
             foreach (var column in csvFileData.Columns)
             s.ColumnMappings.Add(column.ToString(), column.ToString());
             s.WriteToServer(csvFileData);
         }
     }
 }

Source

这篇关于将CSV文件上传到SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！