logo标签列表

将CSV文件导入SQL Server

sqlsql-servercsvbulkinsertbulk
240

我正在寻求帮助，使用BULK INSERT将.csv文件导入到SQL Server中，并有一些基本问题。

问题:

  1. CSV文件数据中可能会有,（逗号）（例如：描述），那么如何处理这些数据？

  2. 如果客户从Excel创建CSV，则具有逗号的数据将用""（双引号）括起来[如下例所示]，那么导入如何处理此类数据？

  3. 我们如何跟踪一些行是否有坏数据，导入会跳过哪些行？（导入会跳过不可导入的行吗）

这是带标题的样本CSV：

Name,Class,Subject,ExamDate,Mark,Description
Prabhat,4,Math,2/10/2013,25,Test data for prabhat.
Murari,5,Science,2/11/2013,24,"Test data for his's test, where we can test 2nd ROW, Test."
sanjay,4,Science,,25,Test Only.

导入的SQL语句：

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)
1
也许 SSMS：如何从Excel导入（复制/粘贴）数据 可以帮助你（如果你不想使用 BULK INSERT 或没有权限）。 - Denis
1
这个不是重点，但是你的样本CSV文件应该可以轻松加载到MS Access中。 - Walter Mitty
14个回答
203

基于SQL Server的CSV导入

1）CSV文件中的数据可能会在中间包含逗号（例如：描述），那么如何处理这些数据？

解决方案

如果您使用逗号作为分隔符，那么无法区分字段终止符和数据中的逗号。我建议使用不同的FIELDTERMINATOR，如||。代码将如下所示，并且可以完美处理逗号和单斜杠。

2）如果客户端从Excel创建CSV，则包含逗号的数据将用双引号" ... "括起来（如下例所示），那么如何处理导入？

解决方案

如果您使用BULK插入，则无法处理双引号，数据将带有双引号插入行中。在将数据插入表后，您可以将这些双引号替换为''。

update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')

3) 我们如何跟踪导入跳过的坏数据行？（导入是否会跳过无法导入的行）？

解决方案

为了处理由于无效数据或格式而未加载到表中的行，可以使用ERRORFILE属性进行处理，指定错误文件名，它将把具有错误的行写入错误文件。 代码应该像这样。

BULK INSERT SchoolsTemp
    FROM 'C:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    )
1
感谢您的帮助。 关于解决方案＃1：我们能否从Excel创建||分隔值文件？因为客户使用Excel创建了约20％的源文件。 - Prabhat
我不确定这完全准确。在SQL Bulk Insert中，您可以处理双引号。关于此主题有一个Stack Overflow，并且可以使用格式文件来教授Bulk Insert不同的分隔符。https://dev59.com/4IPba4cB1Zd3GeqPozLm http://www.advancesharp.com/blog/1083/bulk-insert-with-text-qualifier-in-sql-server - DtechNet
3
文件必须在服务器上。不在您的本地计算机上。 - Jess
为从基于UNIX的操作系统（如Mac、Linux等）生成的.CSV文件设置ROWTERMINATOR = '0x0A'。 - massaskillz
2
@Jess，指定的文件可以是UNC路径（例如\机器名\公共\），只要权限配置正确：https://dba.stackexchange.com/questions/44524/bulk-insert-through-network - massaskillz
显示剩余3条评论
51

从如何使用SQL Server Management Studio将CSV文件导入数据库，于2013-11-05发布:

首先在你的数据库中创建一个表，用于导入CSV文件。创建表后：

  • 使用SQL Server Management Studio登录数据库

  • 右键单击数据库，选择任务 -> 导入数据...

  • 点击下一步 >按钮

  • 对于数据源，选择平面文件源。然后使用浏览按钮选择CSV文件。在点击下一步 >按钮之前，花些时间配置希望如何导入数据。

  • 对于目标，选择正确的数据库提供程序（例如，对于SQL Server 2012，可以使用SQL Server Native Client 11.0）。输入服务器名称；勾选使用SQL Server身份验证，输入用户名、密码和数据库，然后点击下一步 >按钮。

  • 在选择源表和视图窗口上，可以在点击下一步 >按钮之前编辑映射。

  • 勾选立即运行复选框，然后点击下一步 >按钮。

  • 点击完成按钮以运行程序包。

53
如果您在复制/粘贴本答案的页面中加上出处，那就太好了。以下是需要翻译的内容：“It would be nice if you gave attribution to the page where you copy/pasted this answer from...” - SierraOscar
1
不需要预先创建表格，可以在导入过程中创建。 - Andrii Viazovskyi
3
我很喜欢你从网页上复制粘贴了一条非常有用的语句：“花些时间配置您想要导入数据的方式”。这正是我所需要的一切：我似乎完全无法进行配置！ - Auspex
1
哦，还有，“检查使用SQL Server身份验证单选按钮”是错误的，因为您可能希望使用Windows身份验证。这取决于您想使用哪种方式。 - Auspex
35

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
)
4
请注意，FORMAT格式说明符仅在SQL Server 2017及以上版本中可用。 - kristianp
15

解决数据中逗号问题最好、最快、最简单的方法是使用Excel，在将Windows的列表分隔符设置为逗号（如管道符）之外的其他字符后，保存一个逗号分隔的文件。这将为您生成一个用指定字符分隔的文件，然后您可以将其导入。具体步骤请参考这里。

7
由于他们没有使用SQL导入向导，因此步骤如下：

enter image description here

  1. 在选项导入任务中，右键单击数据库以导入数据，

  2. 一旦向导打开，我们选择要导入的数据类型。 在这种情况下，它将是

平面文件源

我们选择CSV文件，您可以配置CSV中表的数据类型，但最好从CSV中获取。

  1. 点击下一步，并选择最后一个选项

SQL客户端

根据我们的身份验证类型进行选择，完成后，会出现一个非常重要的选项。

  1. 我们可以定义CSV中表的ID（建议CSV中的列应与表中的字段同名）。 在编辑映射选项中，我们可以查看每个表格与电子表格列的预览，如果我们希望向导默认插入ID，则取消选中该选项。

启用ID插入

通常情况下，我们不从1开始编号。相反，如果在CSV中有一个带有ID的列，我们选择启用ID插入，下一步是结束向导，我们可以在此处查看更改。

另一方面，在以下窗口中可能会出现警报或警告，理想情况是忽略这些警告，仅当它们出现错误时才需要注意。

此链接包含图像。

5
首先，您需要将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);
                }
            }
        }
    }
}
一个可能更加用户友好的包装器，围绕着BulkCopy类 https://busybulkcopy.codeplex.com/ - busytools
4
这是我如何解决它的方法：

  1. 在Excel中将CSV文件另存为XLS表格(这样做，您就不必担心分隔符。Excel的电子表格格式将被读取为表格并直接导入SQL表)

  2. 使用SSIS导入文件

  3. 在导入管理器中编写自定义脚本以省略/修改您要查找的数据。(或运行主脚本来审查要删除的数据)

祝你好运。
3
下投票：使用SSIS导入XLS文件非常糟糕。SSIS会尝试猜测Excel数据的数据类型，但可能会猜错，而你却无能为力。使用CSV要好得多。 - NReilingh
我建议使用csv，但如果你已经阅读了OP的情况，他有一些特殊的场景，尤其是分隔符，这在xls表格中不是问题。通常这样的特殊情况不需要复杂的解决方案，而是需要一个能够保留数据的修复方法。在上传文件时，SSIS允许您选择源表和目标表之间的数据映射，这样可以减轻工作量。这就是为什么建议使用这种方法作为快速hack的原因。 - Zee
1
SSIS已经可以处理CSV文本分隔符。如果您已经在使用SSIS，那么为了将CSV另存为XLS而费力不讨好的做法似乎没有任何意义，反而可能会引起潜在的故障。 - NReilingh
1
此外，我经常有超过Excel处理能力的CSV文件。 - Auspex
3
所有的回答都很好，如果你的数据“干净”（没有数据约束违规等），并且你可以把文件放到服务器上，那么这些回答都适用。这里提供的一些答案会在第一个错误（PK冲突、数据丢失等）处停止，并在使用SSMS的内置导入任务时每次给出一个错误。如果你想一次收集所有的错误（以防你想告诉给你提供csv文件的人清理他们的数据），我建议你采用以下方法。这个答案还可以让你完全自由地“编写”SQL。

注意：我假设你正在运行Windows操作系统，并可以访问Excel和SSMS。如果不是，我相信你可以调整这个答案以满足你的需求。

  1. 使用Excel打开你的.csv文件。在一个空列中，你将编写一个公式来构建像=CONCATENATE("INSERT INTO dbo.MyTable (FirstName, LastName) VALUES ('", A1, "', '", B1,"')", CHAR(10), "GO")这样的单独 INSERT 语句，其中A1是具有名字数据的单元格，A2例如是具有姓氏数据的单元格。

    • CHAR(10)在最终结果中添加换行符，GO将允许我们运行这个INSERT，并继续到下一个即使有任何错误。

  2. 高亮显示具有=CONCATENATION()公式的单元格

  3. Shift + End以突出显示其它某一列的所有行

  4. 在功能区中> 主页 > 编辑 > 填充 > 单击向下

    • 这将应用公式到整张表格，使你不必手动复制、粘贴、拖动等来处理可能有数千行的数据。

  5. Ctrl + C来复制编制好的SQL INSERT 语句

  6. 粘贴到SSMS中

  7. 你会发现Excel可能出乎意料地在每个INSERT和GO命令周围添加了双引号。这是从Excel中复制多行值的“特性”（？）。你可以简单地查找并替换"INSERT和GO"分别为INSERT和GO以清理它们。

  8. 最后，你准备好运行你的导入过程了

  9. 完成导入过程后，检查消息窗口是否有任何错误。你可以选择所有内容（Ctrl + A）并将其复制到Excel中，使用列过滤器删除任何成功的消息，然后剩下任何和所有的错误。

这个过程肯定比其他答案需要更长时间，但是如果你的数据存在SQL违规现象，那么你至少可以一次性收集所有错误并将它们发送给提供数据的人（如果这是你的情况）。

2
我知道这不是上述问题的确切解决方案，但对我来说，当我尝试将位于另一个服务器上的数据库中的数据复制到本地时，这是一场噩梦。
我试图通过首先从服务器导出数据到 CSV/txt，然后将其导入到我的本地表中来实现这一点。
使用编写导入CSV的查询或使用SSMS导入数据向导的两种解决方案始终会产生错误（错误非常普遍，表示存在解析问题）。虽然我没有做任何特殊的事情，只是将CSV导出，然后尝试将CSV导入本地DB，但错误仍然存在。
我试图查看映射部分和数据预览，但总是一团糟。我知道主要问题来自其中一个表列，其中包含JSON，而SQL解析器对其处理不正确。
最终，我想出了另一种解决方案，并希望分享给其他可能遇到类似问题的人。
我所做的是在外部服务器上使用了“导出向导”。 以下是重复相同过程的步骤：
1）右键单击数据库，选择“任务->导出数据…”

2）当向导打开时，选择“下一步”，在“数据源:”处选择“SQL Server本机客户端”。

enter image description here

如果是外部服务器，您很可能需要选择“使用 SQL Server 身份验证”作为“身份验证模式”。 3）点击“下一步”后，您必须选择“目标”。 为此，请再次选择“SQL Server Native Client”。 这次，您可以提供本地（或其他外部 DB）的 DB。
(Note: The original text contains a spelling error "Destionation" which should be corrected to "Destination")

enter image description here

点击“下一步”按钮后，您有两个选项，要么将整个表从一个DB复制到另一个，要么编写查询以指定要复制的确切数据。在我的情况下，我不需要整个表（它太大了），而只需要其中的一部分，因此我选择了“编写查询以指定要传输的数据”。

enter image description here

我建议在转到向导之前，先在单独的查询编辑器上编写并测试查询。
最后，您需要指定选择数据的目标表。

enter image description here

我建议将其保留为[dbo].[Query]或自定义的Table名称，以防在导出数据时出现错误，或者如果您对数据不确定并希望在移动到确切的表之前进行进一步分析。

现在直接点击“下一步/完成”按钮，以完成向导的最后一步。
2

也许不完全是你所问的，但另一个选择是使用Notepad++的CSV Lint插件。

该插件可以预先验证csv数据，即检查坏数据，如缺少引号、不正确的小数分隔符、日期时间格式错误等。而且，它可以将csv文件转换为SQL插入脚本，而不是使用BULK INSERT。

enter image description here

SQL脚本将包含每个csv行的INSERT语句，以1000条记录为一批，并调整任何日期时间和十进制值。插件会自动检测csv中的数据类型，并且它将包括一个CREATE TABLE部分，其中包含每个列的正确数据类型。

enter image description here

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接