如上所述，您需要添加FORMAT和FIELDQUOTE选项来批量将. csv数据插入SQL Server。对于你的case, SQL语句是这样的:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '""',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK
)

虽然SSMS中的BULK INSERT非常适合一次性导入作业，但根据您的用例，您可能需要SSMS内部或使用第三方的其他选项。下面是一个详细的指南，描述了将CSV文件导入SQL Server的各种选项，包括自动化(我的意思是计划)过程和为CSV位置指定FTP或文件存储的方法。

解决数据中逗号问题的最好、最快和最简单的方法是在将Windows的列表分隔符设置为逗号以外的内容(如管道)后，使用Excel保存一个逗号分隔的文件。这将为您生成一个管道(或其他)分离文件，然后您可以导入该文件。这里描述了这一点。

首先，您需要导入CSV文件到数据表

然后可以使用SQLBulkCopy插入大容量行

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkInsertExample
{
    class Program
    {
      static void Main(string[] args)
        {
            DataTable prodSalesData = new DataTable("ProductSalesData");

            // Create Column 1: SaleDate
            DataColumn dateColumn = new DataColumn();
            dateColumn.DataType = Type.GetType("System.DateTime");
            dateColumn.ColumnName = "SaleDate";

            // Create Column 2: ProductName
            DataColumn productNameColumn = new DataColumn();
            productNameColumn.ColumnName = "ProductName";

            // Create Column 3: TotalSales
            DataColumn totalSalesColumn = new DataColumn();
            totalSalesColumn.DataType = Type.GetType("System.Int32");
            totalSalesColumn.ColumnName = "TotalSales";

            // Add the columns to the ProductSalesData DataTable
            prodSalesData.Columns.Add(dateColumn);
            prodSalesData.Columns.Add(productNameColumn);
            prodSalesData.Columns.Add(totalSalesColumn);

            // Let's populate the datatable with our stats.
            // You can add as many rows as you want here!

            // Create a new row
            DataRow dailyProductSalesRow = prodSalesData.NewRow();
            dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
            dailyProductSalesRow["ProductName"] = "Nike";
            dailyProductSalesRow["TotalSales"] = 10;

            // Add the row to the ProductSalesData DataTable
            prodSalesData.Rows.Add(dailyProductSalesRow);

            // Copy the DataTable to SQL Server using SqlBulkCopy
            using (SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=dbProduct;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = prodSalesData.TableName;

                    foreach (var column in prodSalesData.Columns)
                        s.ColumnMappings.Add(column.ToString(), column.ToString());

                    s.WriteToServer(prodSalesData);
                }
            }
        }
    }
}

Import the file into Excel by first opening excel, then going to DATA, import from TXT File, choose the csv extension which will preserve 0 prefixed values, and save that column as TEXT because excel will drop the leading 0 otherwise (DO NOT double click to open with Excel if you have numeric data in a field starting with a 0 [zero]). Then just save out as a Tab Delimited Text file. When you are importing into excel you get an option to save as GENERAL, TEXT, etc.. choose TEXT so that quotes in the middle of a string in a field like YourCompany,LLC are preserved also...

BULK INSERT dbo.YourTableName
FROM 'C:\Users\Steve\Downloads\yourfiletoIMPORT.txt'
WITH (
FirstRow = 2, (if skipping a header row)
FIELDTERMINATOR = '\t',
ROWTERMINATOR   = '\n'
)

我希望我可以使用FORMAT和Fieldquote功能，但在我的SSMS版本中似乎不支持

我知道有公认的答案，但我仍然想分享我的场景，也许能帮助人们解决他们的问题 工具

ASP。网 Ef码优先法 地对地导弹 EXCEL

场景 我正在加载数据集，它是CSV格式的，稍后将在视图中显示 我试图使用散装装载，但我无法装载散装装载正在使用

FIELDTERMINATOR = ','

Excel细胞也在使用， 然而，我也不能直接使用平面文件源，因为我使用的是代码优先的方法，并且只在SSMS DB中制作模型，而不是在我后来必须使用属性的模型中。

解决方案

我使用平面文件源，并从CSV文件制作DB表(右键单击SSMS中的DB ->导入平面文件->选择CSV路径，并按照指示进行所有设置) 在Visual Studio中创建模型类(你必须保持所有的数据类型和名称与sql中加载的CSV文件相同) 在NuGet包控制台使用Add-Migration 更新数据库

从2013-11-05如何使用SQL Server Management Studio将CSV文件导入到数据库中:

First create a table in your database into which you will be importing the CSV file. After the table is created: Log into your database using SQL Server Management Studio Right click on your database and select Tasks -> Import Data... Click the Next > button For the Data Source, select Flat File Source. Then use the Browse button to select the CSV file. Spend some time configuring how you want the data to be imported before clicking on the Next > button. For the Destination, select the correct database provider (e.g. for SQL Server 2012, you can use SQL Server Native Client 11.0). Enter the Server name; Check Use SQL Server Authentication, enter the User name, Password, and Database before clicking on the Next > button. On the Select Source Tables and Views window, you can Edit Mappings before clicking on the Next > button. Check the Run immediately check box and click on the Next > button. Click on the Finish button to run the package.