Bulk insert with format file

Question

Bulk insert with format file

Karthika Raman

SSCrazy

Points: 2520
More actions
March 8, 2006 at 2:51 pm

#113614

Hi,
I am trying to insert data into a temp table from an excel file using bulk Insert. Here is my code:

exec master..xp_cmdshell ' (BCP ##Test in "E:\WUTemp\Spreadsheetname.xls" -fE:\WUTemp\Prospect.fmt -T)'
Bulk Insert ##Test From 'E:\WUTemp\Spreadsheetname.xls' With (FormatFile = 'E:\WUTemp\Spreadsheetname.fmt')

I get the following error:

(3 row(s) affected)
Server: Msg 4860, Level 16, State 1, Line 2
Could not bulk insert. File 'E:\WUTemp\NewProspectJoJo.fmt' does not exist.

Any help would be greatly appreciated
KR

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Is 'NewProspectJoJo' your spreadsheet name? Does 'E:\WUTemp\NewProspectJoJo.fmt' exist? Does the SQL Server service account have access to E:\WUTemp? It sounds to me like the format file does not exist (just like the message is saying) or SQL Server cannot see it due to Windows permissions.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Karthika Raman SSCrazy Points: 2520 More actions · Answer 2

Yes, that is the spreadsheet name. The sql server account does have access to E:\WUTemp.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Have you created the format file NewProspectJoJo.fmt in E:\WUTemp?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Karthika Raman SSCrazy Points: 2520 More actions · Answer 4

I have. However I am looking at another way to solve the problem of copying data into the temp file, so hopefully I will not need to resolve this particular issue at this point.

Thanks for your input,

KR

YB SSC Enthusiast Points: 181 More actions · Answer 5

You could just use them SQL Bulk Insert via Query Analyzer:

BULK INSERT tableName

FROM '.....txt'

WITH

(

formatfile = '........fmt'

)

GO

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

You could also try running

exec master..xp_cmdshell 'dir E:\WUTemp\NewProspectJoJo.fmt'

..and see what it says..

SQL Server rarely lies - but if it says something doesn't exist, it just means that it can't find it where it's looking. It may exist, but the server may not look where we think it's looking

/Kenneth