csv逗号分隔值文件_如何将逗号分隔值（CSV）文件拆分为SQL Server列

最新推荐文章于 2022-12-20 20:44:07 发布

culuo4781

最新推荐文章于 2022-12-20 20:44:07 发布

阅读量2k

点赞数

文章标签：数据库 python java linux 大数据

原文链接：https://www.sqlshack.com/how-to-split-a-comma-separated-value-csv-file-into-sql-server-columns/

版权

csv逗号分隔值文件

Receiving a comma delimited file is not new technology nor is it difficult to deal with in SQL Server. As a matter of fact, it is extremely easy. There are many cases as to why you would want to do this. For example, you have an external data source that needs to be imported into your database/table. There a couple ways to do this, however the quickest and easiest way is to use the native “import” feature within SQL Server Management Studio and you can even save it to an SSIS Package at the end of the process. The end result of using this method is that the external CSV file is loaded into a SQL Server table where columns are created and rows are populated.

接收逗号分隔的文件不是新技术，也不是很难在SQL Server中处理的文件。事实上，这非常容易。关于为什么要执行此操作的案例很多。例如，您有一个外部数据源，需要将其导入数据库/表中。有两种方法可以执行此操作，但是最快，最简单的方法是使用SQL Server Management Studio中的本机“导入”功能，甚至可以在过程结束时将其保存到SSIS包中。使用此方法的最终结果是将外部CSV文件加载到SQL Server表中，在该表中创建列并填充行。

If you are only doing a single or one off import of data import then I recommend the native Import feature within SQL Server Management Studio. If you will be doing this process more than once, then I will show you how to save this process into an SSIS package at the end so stay tuned. From SQL Server 2005-2016 the feature has not changed. For this example below, I’ve used SQL Server Express 2014 however again, it is the same across the current version as well as the past several versions. To access it, from SQL Server Management Studio, expand your server branch to your databases. Right click on your database name and open the tasks menu then select Import Data. This will bring up the SQL Server Import and Export Wizard:

如果仅一次或一次导入数据导入，则建议在SQL Server Management Studio中使用本机导入功能。如果您将不止一次地执行此过程，那么我将向您展示如何将该过程最终保存到SSIS包中，请继续关注。从SQL Server 2005-2016开始，功能未更改。在下面的示例中，我再次使用了SQL Server Express 2014，但在当前版本以及过去的几个版本中，它都是相同的。要访问它，请从SQL Server Management Studio中将服务器分支扩展到数据库。右键单击数据库名称，然后打开任务菜单，然后选择“导入数据”。这将显示SQL Server导入和导出向导：

The wizard opens to the splash screen. Click Next to continue:

向导将打开到初始屏幕。单击下一步继续：

From the Data Source dropdown box, select Flat File Source then select your CSV file from the File Name Browse line that you have saved on your file system. Because we are using the flat file source, we can use a csv or txt file extension. In our example, we will use CSV.

在“数据源”下拉框中，选择“平面文件源”，然后从保存在文件系统上的“文件名浏览”行中选择CSV文件。由于我们使用的是平面文件源，因此可以使用csv或txt文件扩展名。在我们的示例中，我们将使用CSV。

数据源 (Data Source)

Once your file is selected, complete the rest of the form:

选择文件后，填写表格的其余部分：

Complete the Format section. If your text file contains a quote (“) around each column/row of data then make sure you add that as the Text qualifier. Header Row Delimiter defaults to {CR}{LF} which is carriage row/line feed. If you have column names in the first row(s), you may opt to skip them by selecting the number of rows to select. If you prefer to keep your column names, just let this stay with the default of 0 and make sure you check “Column names in the first data row”.

完成格式部分。如果您的文本文件的每一列/每行数据都包含引号（“），请确保将其添加为“文本”限定符。标题行分隔符默认为{CR} {LF}，即回车行/换行符。如果第一行中有列名，则可以选择要选择的行数来跳过它们。如果您希望保留列名，只需将其保留为默认值0，并确保选中“第一个数据行中的列名”。

Next, select Columns from the left hand side of the Source form.

接下来，从“源”表单的左侧选择“列”。

This will give you a preview of the data to be imported. Note here you will see the column headers you opted to keep.

这将为您提供要导入数据的预览。请注意，您将在此处看到选择保留的列标题。

Next select the “Advanced” option from the left menu. This option is important because will you be able to select your column types and column widths for each column. It is a good idea to verify that each column type and column length is appropriate for each column.

接下来，从左侧菜单中选择“高级”选项。此选项很重要，因为您将能够为每列选择列类型和列宽。验证每个列类型和列长是否适合每个列是一个好主意。

After you have updated each column with the appropriate column type and column width, click on the Preview option on the left menu. This will show you a preview of your data. At this time, you will also be able to go back and update column types and widths if necessary.

用适当的列类型和列宽更新每个列后，单击左侧菜单上的“预览”选项。这将向您显示数据的预览。此时，如果需要，您还可以返回并更新列的类型和宽度。

Click Next on the screen above. This will take you to the destination selection screen.

在上面的屏幕上单击“下一步”。这将带您进入目的地选择屏幕。

数据库目标 (Database Destination)

From here you will need to select your destination table for the data. For this example, we are going to create a table or destination within the wizard.

在这里，您将需要选择数据的目标表。对于此示例，我们将在向导中创建表或目标。

Change the destination to Microsoft OLE DB Provider for SQL Server. This will allow you to connect to your SQL Server.

将目标更改为SQL Server的Microsoft OLE DB提供程序。这将允许您连接到SQL Server。

Select your Server name and Database name from the drop down boxes or type them in, then select next

从下拉框中选择服务器名称和数据库名称，或在其中键入，然后选择下一步

You will be taken to the select source and destination selection. SQL Server will automatically populate your data source that you entered at the beginning of this lesson as well as default a destination to a table named the same as your data source. If you already have a table created in your database, you may change the destination to that or just change the table name defined by SQL Server.

您将被带到选择源和目的地选择。 SQL Server将自动填充您在本课开始时输入的数据源，并默认为与数据源相同的表的目的地。如果已经在数据库中创建了表，则可以将目标更改为该目标，或者仅更改SQL Server定义的表名。

Here you will see the source CSV file we have been working with as well as the destination table in the database. I have selected to use the default name given by SQL Server however, feel free to change the name or select a different table. Click on the Edit Mappings button, if you would like to make any changes to the destination table or edit the create table script. Here you may also enable identity insert.

在这里，您将看到我们一直在使用的源CSV文件以及数据库中的目标表。我选择使用SQL Server提供的默认名称，但是，随时可以更改名称或选择其他表。如果要对目标表进行任何更改或编辑创建表脚本，请单击“编辑映射”按钮。您还可以在此处启用身份插入。

If no changes are necessary, then select OK on this screen and then select Preview on the next.

如果不需要更改，请在此屏幕上选择“确定”，然后在下一个屏幕上选择“预览”。

Preview will give you a preview of how your data will look in the table

预览将为您预览数据在表中的外观

Once data is verified, close this screen and select Next.

验证数据后，关闭此屏幕并选择“下一步”。

Now this screen is probably the most important screen in this series because it enables you to create an SSIS package after. I almost always use this option because you will not have to redo any of the steps above for subsequent runs. Also, the SSIS package can be added to a SQL agent job and scheduled. Since you save the SSIS package, you will also be able to modify the SSIS package too.

现在，此屏幕可能是该系列中最重要的屏幕，因为它使您能够在此之后创建SSIS包。我几乎总是使用此选项，因为您不必为以后的运行重做上述任何步骤。另外，可以将SSIS包添加到SQL代理作业中并进行计划。由于保存了SSIS包，因此您也将能够修改SSIS包。

You have a choice to save your SSIS package directly into SQL Server or on your File System. Depending on how you are structured, neither is better than the other in my opinion, it just depends on how you want to organize it. Click next to continue. If you decide to save to the file system, you will be presented with this screen to save the file.

您可以选择将SSIS包直接保存到SQL Server或文件系统中。根据您的结构，在我看来，哪一个都不比另一个更好，这仅取决于您要如何组织它。单击下一步继续。如果决定保存到文件系统，将显示此屏幕以保存文件。

Add your file name and directory location and click next. You will be presented with a confirmation screen.

添加您的文件名和目录位置，然后单击下一步。您将看到一个确认屏幕。

Then click Finish.

然后单击完成。

If the import is successful, you should get a confirmation.

如果导入成功，您应该得到确认。

You have successfully imported a CSV file into a SQL Server table. There are no scripts involved in this process, just the native Import feature within SQL Server Management Studio. This is basic SQL Server 101 but a very important and useful tool.

您已成功将CSV文件导入到SQL Server表中。此过程中没有脚本，只有SQL Server Management Studio中的本机导入功能。这是基本SQL Server 101，但是是非常重要和有用的工具。