将 CSV 文件导入 SQL Server

Import CSV file into SQL Server

提问by Prabhat

I am looking for help to import a .csvfile into SQL Server using BULK INSERTand I have few basic questions.

我正在寻找使用帮助将.csv文件导入 SQL Server BULK INSERT，但我有几个基本问​​题。

Issues:

问题：

1. The CSV file data may have ,(comma) in between (Ex: description), so how can I make import handling these data?

2. If the client creates the CSV from Excel then the data that have comma are enclosed within ""(double quotes) [as the below example] so how do the import can handle this?

3. How do we track if some rows have bad data, which import skips? (does import skips rows that are not importable)

1. CSV 文件数据之间可能有,（逗号）（例如：描述），那么如何进行导入处理这些数据？

2. 如果客户端从 Excel 创建 CSV 则包含逗号的数据包含在""（双引号）内 [如下例] 那么导入如何处理？

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

Here is the sample CSV with header:

这是带有标题的示例 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.

And SQL statement to import:

和要导入的 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
)

采纳答案by mr_eclair

Based SQL Server CSV Import

基于 SQL Server CSV 导入

1) The CSV file data may have ,(comma) in between (Ex: description), so how can I make import handling these data?

1) CSV 文件数据之间可能有,（逗号）（例如：描述），那么如何进行导入处理这些数据？

Solution

解决方案

If you're using ,(comma) as a delimiter, then there is no way to differentiate between a comma as a field terminator and a comma in your data. I would use a different FIELDTERMINATORlike ||. Code would look like and this will handle comma and single slash perfectly.

如果您使用,(comma) 作为分隔符，则无法区分逗号作为字段终止符和数据中的逗号。我会使用不同的FIELDTERMINATORlike ||。代码看起来像这样，这将完美地处理逗号和单斜杠。

2) If the client create the csv from excel then the data that have comma are enclosed within " ... "(double quotes) [as the below example] so how do the import can handle this?

2）如果客户端从 excel 创建 csv 那么带有逗号的数据被括在" ... "（双引号）内[如下例]那么导入如何处理？

Solution

解决方案

If you're using BULK insert then there is no way to handle double quotes, data will be inserted with double quotes into rows. after inserting the data into table you could replace those double quotes with ''.

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

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

3) How do we track if some rows have bad data, which import skips? (does import skips rows that are not importable)?

3）我们如何跟踪某些行是否有错误数据，哪些导入会跳过？（导入是否会跳过不可导入的行）？

Solution

解决方案

To handle rows which aren't loaded into table because of invalid data or format, could be handle using ERRORFILE property, specify the error file name, it will write the rows having error to error file. code should look like.

要处理由于无效数据或格式而未加载到表中的行，可以使用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
    )

回答by Zd8n8k

You first need to create a table in your database in which you will be importing the CSV file. After the table is created, follow the steps below.

您首先需要在您的数据库中创建一个表，您将在其中导入 CSV 文件。创建表后，请按照以下步骤操作。

? Log into your database using SQL Server Management Studio

? 使用 SQL Server Management Studio 登录您的数据库

? Right click on your database and select Tasks -> Import Data...

? 右键单击您的数据库并选择Tasks -> Import Data...

? Click the Next >button

? 点击Next >按钮

? 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.

? 对于数据源，选择Flat File Source。然后使用浏览按钮选择 CSV 文件。在单击Next >按钮之前，花一些时间配置您希望如何导入数据。

? 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 the Use SQL Server Authenticationradio button. Enter the User name, Password, and Database before clicking on the Next >button.

? 对于目标，选择正确的数据库提供程序（例如，对于 SQL Server 2012，您可以使用 SQL Server Native Client 11.0）。输入服务器名称。检查Use SQL Server Authentication单选按钮。在单击Next >按钮之前输入用户名、密码和数据库。

? On the Select Source Tables and Views window, you can Edit Mappings before clicking on the Next >button.

? 在“选择源表和视图”窗口中，您可以在单击Next >按钮之前编辑映射。

? Check the Run immediatelycheck box and click on the Next >button.

? 选中Run immediately复选框并单击Next >按钮。

? Click on the Finishbutton to run the package.

? 单击Finish按钮以运行包。

The above was found on this website(I have used it and tested):

以上是在这个网站上找到的（我已经使用过并测试过）：

回答by Oleg

2) If the client create the csv from excel then the data that have comma are enclosed within " ... " (double quotes) [as the below example] so how do the import can handle this?

2）如果客户端从excel创建csv，那么带有逗号的数据包含在“...”（双引号）内[如下例]那么导入如何处理？

You should use FORMAT = 'CSV', FIELDQUOTE = '"' options:

您应该使用 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
)

回答by Sachin Kainth

The best, quickest and easiest way to resolve the comma in data issue is to use Excel to save a comma separated file after having set Windows' list separator setting to something other than a comma (such as a pipe). This will then generate a pipe (or whatever) separated file for you that you can then import. This is described here.

解决数据中逗号问题的最佳、最快和最简单的方法是在将 Windows 的列表分隔符设置为逗号以外的其他设置（例如管道）后，使用 Excel 保存逗号分隔的文件。然后，这将为您生成一个管道（或其他）分隔文件，然后您可以导入该文件。这在此处进行了描述。

回答by kombsh

Firs you need to import CSV file into Data Table

首先需要将 CSV 文件导入数据表

Then you can insert bulk rows using SQLBulkCopy

然后您可以使用 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);
                }
            }
        }
    }
}

回答by Zee

Here's how I would solve it:

这是我将如何解决它：

1. Just Save your CSV File as a XLS Sheet in excel(By Doing so, you wouldn't have to worry about delimitiers. Excel's spreadsheet format will be read as a table and imported directly into a SQL Table)

2. Import the File Using SSIS

3. Write a Custom Script in the import manager to omit/modify the data you're looking for.(Or run a master script to scrutinize the data you're looking to remove)

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

2. 使用 SSIS 导入文件

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

Good Luck.

祝你好运。

回答by jarvis24

Because they do not use the SQL import wizard, the steps would be as follows:

因为他们不使用 SQL 导入向导，所以步骤如下：

1. Right click on the database in the option tasks to importdata,

2. Once the wizardis open, we select the type of data to be implied. In this case it would be the

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

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

Flat file source

平面文件源

We select the CSV file, you can configure the data type of the tables in the CSV, but it is best to bring it from the CSV.

我们选择CSV文件，可以在CSV中配置表格的数据类型，但是最好从CSV中带过来。

1. Click Next and select in the last option that is

3. 单击下一步并在最后一个选项中选择

SQL client

SQL客户端

Depending on our type of authentication we select it, once this is done, a very important option comes.

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

1. We can define the id of the table in the CSV (it is recommended that the columns of the CSV should be called the same as the fields in the table). In the option Edit Mappings we can see the preview of each table with the column of the spreadsheet, if we want the wizard to insert the id by default we leave the option unchecked.

4. 我们可以在 CSV 中定义表的 id（建议 CSV 的列应与表中的字段名称相同）。在 Edit Mappings 选项中，我们可以看到每个表的预览以及电子表格的列，如果我们希望向导默认插入 id，我们不选中该选项。

Enable id insert

启用 ID 插入

(usually not starting from 1), instead if we have a column with the id in the CSV we select the enable id insert, the next step is to end the wizard, we can review the changes here.

（通常不是从 1 开始），相反，如果我们在 CSV 中有一个带有 id 的列，我们选择启用 id 插入，下一步是结束向导，我们可以在这里查看更改。

On the other hand, in the following window may come alerts, or warnings the ideal is to ignore this, only if they leave erroris necessary to pay attention.

另一方面，在接下来的窗口中可能会出现alerts，或者warnings，理想的情况是忽略这一点，只有当他们留下error时才需要注意。

This link has images.

这个链接有图片。

回答by Steve Yo

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...

将文件导入 Excel，首先打开 excel，然后转到 DATA，从 TXT 文件导入，选择将保留 0 前缀值的 csv 扩展名，并将该列保存为 TEXT 因为 excel 将删除前导 0 否则（不要双击如果字段中有以 0 [零] 开头的数字数据，则使用 E​​xcel 打开）。然后保存为制表符分隔的文本文件。当您导入到 excel 时，您可以选择另存为 GENERAL、TEXT 等。

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

I wish I could use the FORMAT and Fieldquote functionality but that does not appear to be supported in my version of SSMS

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

回答by Chameleon

I know that there are accepted answer but still, I want to share my scenario that maybe help someone to solve their problem TOOLS

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

• ASP.NET
• EF CODE-FIRST APPROACH
• SSMS
• EXCEL

• ASP.NET
• EF 代码优先方法
• 安全管理系统
• 卓越

SCENARIOi was loading the dataset which's in CSV format which was later to be shown on the View i tried to use the bulk load but I's unable to load as BULK LOADwas using

场景我正在加载 CSV 格式的数据集，该数据集稍后将显示在视图中 我尝试使用批量加载，但我无法像BULK LOAD使用时那样加载

FIELDTERMINATOR = ','

and Excel cell was also using ,however, I also couldn't use Flat file sourcedirectly because I was using Code-First Approachand doing that only made model in SSMS DB, not in the model from which I had to use the properties later.

并且 Excel 单元格也在使用，,但是，我也无法Flat file source直接使用，因为我使用Code-First Approach和执行的操作仅在 SSMS DB 中创建模型，而不是在稍后我必须从中使用属性的模型中。

SOLUTION

解决方案

1. I used flat-file source and made DB table from CSV file (Right click DB in SSMS -> Import Flat FIle -> select CSV path and do all the settings as directed)
2. Made Model Class in Visual Studio (You MUST KEEP all the datatypes and names same as that of CSV file loaded in sql)
3. use Add-Migrationin NuGet package console
4. Update DB

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

回答by Arsen Khachaturyan

I know this is not the exact solution to the question above, but for me, it was a nightmare when I was trying to Copydata from one database located at a separate server to my local.

我知道这不是上述问题的确切解决方案，但对我来说，当我尝试将数据从位于单独服务器上的一个数据库复制到本地时，这是一场噩梦。

I was trying to do that by first exportdata from the Server to CSV/txtand then importit to my local table.

我试图通过首先将数据从服务器导出到CSV/txt然后将其导入到我的本地表来做到这一点。

Both solutions: with writing down the query to import CSVor using the SSMS Import Datawizard was always producing errors (errors were very general, saying that there is parsing problem). And although I wasn't doing anything special, just exportto CSVand then trying to importCSVto the local DB, the errors were always there.

两种解决方案：写下要导入的查询CSV或使用 SSMS导入数据向导总是会产生错误（错误非常普遍，表示存在解析问题）。虽然我没有做任何特别的事情，只是导出到CSV然后尝试导入CSV到本地DB，但错误总是存在。

I was trying to look at the mapping section and the data preview, but there was always a big mess. And I know the main problem was comming from one of the tablecolumns, which was containing JSONand SQLparser was treating that wrongly.

我试图查看映射部分和数据预览，但总是有很大的混乱。而且我知道主要问题来自其中一table列，该列包含JSON并且SQL解析器错误地处理了它。

So eventually, I came up with a different solution and want to share it in case if someone else will have a similar problem.

所以最终，我想出了一个不同的解决方案，并希望分享它，以防其他人遇到类似的问题。

---

---

What I did is that I've used the Exporting Wizardon the external Server.

我所做的是在外部服务器上使用了导出向导。

Here are the steps to repeat the same process:
1) Right click on the database and select Tasks -> Export Data...

以下是重复相同过程的步骤：
1) 右键单击​​数据库并选择Tasks -> Export Data...

2) When Wizard will open, choose Next and in the place of "Data Source:" choose "SQL Server Native Client".

2) 当向导打开时，选择下一步并在“数据源：”的位置选择“SQL Server Native Client”。

In case of external Server you will most probably have to choose "Use SQL Server Authentication" for the "Authentication Mode:".

对于外部服务器，您很可能必须为“身份验证模式：”选择“使用 SQL Server 身份验证”。

3) After hitting Next, you have to select the Destionation.
For that, select again "SQL Server Native Client".
This time you can provide your local (or some other external DB) DB.

3) 点击Next 后，您必须选择Destination。
为此，再次选择“SQL Server Native Client”。
这一次你可以提供你的本地（或其他一些外部DB）DB。

4) After hitting the Next button, you have two options either to copy the entire table from one DBto another or write down the query to specify the exact data to be copied. In my case, I didn't need the entire table (it was too large), but just some part of it, so I've chosen "Write a query to specify the data to transfer".

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

I would suggest writing down and testing the query on a separate query editor before moving to Wizard.

我建议在移动到向导之前，在单独的查询编辑器上写下并测试查询。

5) And finally, you need to specify the destination table where the data will be selected.

5) 最后，您需要指定将选择数据的目标表。

I suggest to leave it as [dbo].[Query]or some custom Tablename in case if you will have errors exporting the data or if you are not sure about the data and want further analyze it before moving to the exact table you want.

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

And now go straight to the end of the Wizard by hitting Next/Finishbuttons.

现在点击下一步/完成按钮直接进入向导的结尾。