I was looking for the answer and could not fine the one for my question. It seems that I'm doing everything as explained, but it does not work in my case...
I'm doing a bulk insert with "|" as a field separator and I have a text file with a content like that:
111 |234567|111111111111|22222222222|33333333333333|444444444444|D| | | |
222 |890111|222211111111|33332222222|34444333333333|555544444444|V| | | |
At the end of each line when I look at Notepad++ I have "CRLF"
I have a table named Bulk_Table_Sample with the same amount of columns as separated in the file
Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11
And I have a view based on that table:
CREATE VIEW [dbo].[vw_Bulk_Table_Sample]
AS
SELECT Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11
FROM Bulk_Table_Sample
This is my Bulk Statement:
declare @cmd varchar(1000)
declare @FullFilePath varchar(1000)
set @FullFilePath = '\\File_Path\Data_Sample.DAT'
SET @cmd = 'BULK INSERT dbo.vw_Bulk_Table_Sample FROM '''+ @FullFilePath +
''' WITH(BATCHSIZE=20000,TABLOCK,FIRSTROW=1,FIELDTERMINATOR=''"|"'',ROWTERMINATOR=''\r'')'
EXEC (@cmd)
When executing the statement, I'm getting the following error:
Msg 4832, Level 16, State 1, Line 183
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 183
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 183
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
What is wrong with what I'm doing?
