CODEWITHSUNDEEP
sqlsql-server
dimitris93
dimitris93

Reputation: 4273

Load data from .txt into SQL Server database

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.

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.

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

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

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

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.

Upvotes: 0

Views: 1136

Answers (1)

James Z
James Z

Reputation: 12318

You can skip the row using FIRSTROW -parameter.

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.

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

Upvotes: 1

Related Questions