将.txt中的数据加载到SQL Server数据库中

I am using SQL Server and I need to load some data from a .txt file into my database table which I already created and set its columns properly.

我正在使用SQL Server,我需要将.txt文件中的一些数据加载到我已创建的数据库表中并正确设置其列。

The .txt has this format all along and it is really big (170MB). The first line simply shows the columns, so it needs to be ignored.

.txt一直都有这种格式,它真的很大(170MB)。第一行只显示列,因此需要忽略。

This is how the .txt format looks like. I renamed the names for simplicity.

这就是.txt格式的样子。为简单起见,我将这些名称重命名。

Col1||Col2||Col3||Col4
101||200||hello||world
104||202||hi||world
...

Looking at the MSDN documentation, I suppose need something similar to this:

查看MSDN文档,我想需要类似于此的东西:

BULK INSERT db1.table1
FROM 'C:\file1.txt'
WITH
(
  FIELDTERMINATOR = '||'
)

Is this syntax correct ? How would I manage to ignore the first line ? I unfortunately do not have any way to confirm the syntax for right now.

这种语法是否正确?如何设法忽略第一行?遗憾的是,我现在没有办法确认语法。

1 个解决方案

#1

You can skip the row using FIRSTROW -parameter.

您可以使用FIRSTROW -parameter跳过该行。

FIRSTROW = first_row

FIRSTROW = first_row

Specifies the number of the first row to load. The default is the first row in the specified data file.

指定要加载的第一行的编号。默认值是指定数据文件中的第一行。

The FIRSTROW attribute is not intended to skip column headers. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not valid the data in the fields of skipped rows.

FIRSTROW属性不用于跳过列标题。在跳过行时,SQL Server数据库引擎仅查看字段终止符,并且不会对跳过的行的字段中的数据进行有效。

Also, just in case the file is not in a place the SQL Server can easily access, you can also load it using bcp: https://msdn.microsoft.com/en-us/library/ms162802.aspx

此外,如果文件不在SQL Server可以轻松访问的位置,您也可以使用bcp加载它:https://msdn.microsoft.com/en-us/library/ms162802.aspx

#1