I have got an *.dat file which has been created from the Oracle Db. I need to Import it into SQL server 2008.
In the *.Dat file, Columns are pipe (|) delimited and semicolon (;) as end of row.
I am getting the error using this piece of code:
BULK INSERT Database_name.schema_name.Tablename FROM 'F:\******\***\******.dat' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = ';' );
Bulk load: An unexpected end of file was encountered in the data file.... Please Help ASAP.....
I have not used this code, but i am not sure is this correct or not?
EXEC xp_cmdshell 'bcp Database_name.schema_name.Tablename in 'F:\******\***\******.dat' -c -t ^| -r ^; -T'
Please suggest ASAP....
Answer by Jay Bonk ·
The Bulk Insert statement is correct, the problem I suspect is that there is a CR/LF at the end of your file.
I created this test table:
CREATE TABLE bcp_test ( col_1 CHAR(1) ,col_2 VARCHAR(20) ,col_3 VARCHAR(20) );
And then also a sample test file, that looked like this:
a|first line|ten;b|second line|twenty;c|third line|ninety three;
All on a single line, and ran this BCP:
BULK INSERT Playground.dbo.bcp_test FROM 'C:\WORK\testfile.dat' WITH ( FIELDTERMINATOR = '|' ,ROWTERMINATOR = ';' );
The data loaded without a problem. I replicated the data sting where I was able to load over 15K rows.
Next I went to the end of the file and hit enter, adding a CR/LF, and tried to load the data ending up with the following error:
*Msg 4832, Level 16, State 1, Line 1 Bulk load: An unexpected end of file was encountered in the data file. 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)".*
Check and make sure that the last character in the file is the semi column terminating the last record.
Running this from the command line, encountered the same error when the CR/LF was present and loaded successfully when not present
bcp Playground.dbo.bcp_test in "C:\WORK\testfile.dat" -c -t ^| -r ^; -T
And the same from xp_cmdshell
EXEC xp_cmdshell 'bcp Playground.dbo.bcp_test in "C:\WORK\testfile.dat" -c -t ^| -r ^; -T'
You do need to use double quotes (") around the file name