bcp with format file

I am trying to copy the data in excel file into a table using the bcp
and this is the code that I have. However the bcp utility does not
seem to create a format file, which I thought it should do. I am
probably going about this all wrong so any help would be useful.

exec master..xp_cmdshell '(FOR %i IN ("E:\WUTemp\*") DO (bcp
#ProspectImportTest in "%i" -fE:\WUTemp\Prospect.fmt)'
bulk insert #ProspectImportTest from 'E:\WUTemp\*."' with (formatfile =
'E:\WUTemp\Prospect.fmt')

Thanks.

KRKR (kraman@.bastyr.edu) writes:
> I am trying to copy the data in excel file into a table using the bcp
> and this is the code that I have. However the bcp utility does not
> seem to create a format file, which I thought it should do. I am
> probably going about this all wrong so any help would be useful.
> exec master..xp_cmdshell '(FOR %i IN ("E:\WUTemp\*") DO (bcp
> #ProspectImportTest in "%i" -fE:\WUTemp\Prospect.fmt)'
> bulk insert #ProspectImportTest from 'E:\WUTemp\*."' with (formatfile =
> 'E:\WUTemp\Prospect.fmt')

To have BCP to create a format file, you should specify "format" for
the direction parameter, not "in".

Furthermore, BCP cannot access the temp table #ProspectImportTest, as it
as local to your connection. You could try a global temp table,
##ProspectImportTest.

And BULK INSERT does not, as far as I know, accept file specifications with
wildcards in them.

As for the format file , I would not expect that format file be very useful.
You said Excel file, but you did not specify what format of Excel. BULK
INSERT cannot read an xls file, as that is a binary file. It can read
a CSV file or a tab-delimited file, but you don't need a format file for
that, just specify FIELDTERMINATOR with the BULK INSERT command. (Unless you
have a CSV file with strings quoted. Then you need a format file.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Guess I have a lot to learn yet - and you have given me good points. I
am new to this kind of thing.

Anyway, by the time I saw your reply, I had tried to go in another
direction where I was trying to do the same thing but I am working with
a csv file this time.

Thanks