Import text file to SQL Server using Bulk Insert

sql-server bulk-import
riza picture riza · Jan 23, 2013 · Viewed 41.6k times · Source

This is my sql

BULK INSERT dbo.Account FROM 'G:\Import\Account3.txt'
WITH
(
    FIELDTERMINATOR = '" | "'
)
GO

When I run the sql i got this error

Msg 4866, Level 16, State 1, Line 1
The bulk load failed.

The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Please help me. I already tried many ways but still get the same error.

Answer

Tom Lint picture Tom Lint · Apr 17, 2015

From your example SQL, it seems you are missing a ROWTERMINATOR statement, specifying how rows are to be differentiated from one another.

Your query would then become something like

BULK INSERT dbo.Account FROM 'G:\Import\Account3.txt'
WITH
(
FIELDTERMINATOR = '" | "',
ROWTERMINATOR = '\r\n'
)
GO

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More