Help needed in Importing *.dat file

Question

question

sidi boy asked Mar 25, '10

Help needed in Importing *.dat file

Hi,

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....

import-data bcp bulk-insert

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-26T00:56:42Z

Jay Bonk answered Mar 26, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question