Bulk Insert Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Bulk Insert Error

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2010-01-26 : 10:08:46

I am getting the following error message when running the below Bulk Insert Statement:

Cannot bulk load. The file "C:\Test\Test_Format_File.txt" does not exist.

BULK INSERT atb_tbl FROM 'C:\Test\TestFile.txt'
WITH
(
FORMATFILE = 'C:\Test\Test_Format_File.txt',
FIELDTERMINATOR = '|'

)

The file does exist in the directory referenced, can someone help me figure out what I am doing wrong?

Brian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 10:43:33

is the file stored on C:\Test\TestFile.txt of your local machine? else you need to give UNC path to point to shared server path where it exists

bconner
Starting Member

48 Posts

Posted - 2010-01-26 : 11:15:08

Yes, it is stored on my C: Drive

I copied the code out of the old file and just copied into a new txt file. Now I am getting the below error:

Msg 4832, Level 16, State 1, Line 4
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 4
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 4
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Below is the code I wrote:

BULK INSERT atb_tbl FROM 'C:\Test\TestFile.txt'
WITH
(
FORMATFILE = 'C:\Test\Format.txt',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n\r'
)

Here is the code in the Format File:
9.0
3
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 80 "\"|\"" 1 Division Latin1_General_CS_AS
3 SQLCHAR 0 80 "\"|\"" 2 Group_ID Latin1_General_CS_AS
4 SQLCHAR 0 80 "\"\n" 3 Invoice_Number Latin1_General_CS_AS

Here is a sample of the text file I am trying to import:
"SJCQ"|"8"|"12285045"
"SJCQ"|"8"|"12285045"
"SJCQ"|"8"|"12285045"
"SJCQ"|"8"|"12285045"

Brian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 11:35:34

is delimiters in file data consistent? also make last line of format file as

4 SQLCHAR 0 80 "\"\r\n" 3 Invoice_Number Latin1_General_CS_AS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources