Load data from .txt into SQL Server database

Question

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.

Well, make a test file holding the first ten lines and give it a try, I'd say. — arkascha
– arkascha, Commented Mar 7, 2015 at 18:53
@arkascha In the last line of my question i mention that i can't, and also i need to know how to ignore the first line — dimitris93
– dimitris93, Commented Mar 7, 2015 at 18:53
No, you just wrote in your last line that you cannot confirm the syntax. Why can't you give it a try? If the syntax is wrong you get an error. You modify until you succeed. What is the problem here? About ignoring the first line: well strip it from the file. Why do people always have to make things complex? — arkascha
– arkascha, Commented Mar 7, 2015 at 18:55
@arkascha because I don't have a machine that allows me to "give it a try" — dimitris93
– dimitris93, Commented Mar 7, 2015 at 18:57
How that? You have the server you want to load the data into. Why isn't that fine? Even if the result is not as desired, you can always prune the table again. But maybe I just don't understand your hesitation :-) — arkascha
– arkascha, Commented Mar 7, 2015 at 18:58

James Z · Accepted Answer · 2015-03-07 18:55:21Z

1

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

answered Mar 7, 2015 at 18:55

James Z

12.3k10 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Load data from .txt into SQL Server database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related