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版本中似乎不支持

All of the answers here work great if your data is "clean" (no data constraint violations, etc.) and you have access to putting the file on the server. Some of the answers provided here stop at the first error (PK violation, data-loss error, etc.) and give you one error at a time if using SSMS's built in Import Task. If you want to gather all errors at once (in case you want to tell the person that gave you the .csv file to clean up their data), I recommend the following as an answer. This answer also gives you complete flexibility as you are "writing" the SQL yourself.

注意:我将假设您运行的是Windows操作系统，并且能够访问Excel和SSMS。如果没有，我相信你可以调整这个答案来满足你的需求。

Using Excel, open your .csv file. In an empty column you will write a formula that will build individual INSERTstatements like =CONCATENATE("INSERT INTO dbo.MyTable (FirstName, LastName) VALUES ('", A1, "', '", B1,"')", CHAR(10), "GO") where A1 is a cell that has the first name data and A2 has the last name data for example. CHAR(10) adds a newline character to the final result and GO will allow us to run this INSERT and continue to the next even if there are any errors. Highlight the cell with your =CONCATENATION() formula Shift + End to highlight the same column in the rest of your rows In the ribbon > Home > Editing > Fill > Click Down This applies the formula all the way down the sheet so you don't have to copy-paste, drag, etc. down potentially thousands of rows by hand Ctrl + C to copy the formulated SQL INSERT statements Paste into SSMS You will notice Excel, probably unexpectedly, added double quotes around each of your INSERT and GO commands. This is a "feature" (?) of copying multi-line values out of Excel. You can simply find and replace "INSERT and GO" with INSERT and GO respectively to clean that up. Finally you are ready to run your import process After the process completes, check the Messages window for any errors. You can select all the content (Ctrl + A) and copy into Excel and use a column filter to remove any successful messages and you are left with any and all the errors.

这个过程肯定会比这里的其他答案花费更长的时间，但是如果您的数据是“脏的”并且充满SQL违规，您至少可以一次性收集所有错误并将它们发送给提供数据的人(如果您的场景是这样的话)。

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

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

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

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

FIELDTERMINATOR = ','

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

解决方案

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

首先，您需要导入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);
                }
            }
        }
    }
}

2)如果客户端从excel中创建了csv，那么有数据 逗号被括在"…(双引号)[如下所示 那么导入如何处理这个问题呢?

你应该使用FORMAT = 'CSV'， FIELDQUOTE = '"'选项:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)