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

BCP Issue w/Right Truncation

Hello All,

I am attempting a bulk load of fixed position flat file data via bcp
and I have noticed that I get a Right Truncation error when trying to
load a row where the last column value is NULL.

For example:

Flat file row:
0000016M

FMT file:
7.0
3
1 SQLCHAR 0 7 "" 1 RECORD_KEY
2 SQLCHAR 0 1 "" 2 SEX
3 SQLCHAR 0 1 "\r\n" 3 HEIGHT

In this row, the height info is null and I get a right truncation
error. The row below, with height info goes in fine:

Flat file row:
0000016M510

Let me know what I am doing wrong!

Thanks in advancehharry (paulquigley@.nyc.com) writes:
> I am attempting a bulk load of fixed position flat file data via bcp
> and I have noticed that I get a Right Truncation error when trying to
> load a row where the last column value is NULL.
> For example:
> Flat file row:
> 0000016M
> FMT file:
> 7.0
> 3
> 1 SQLCHAR 0 7 "" 1 RECORD_KEY
> 2 SQLCHAR 0 1 "" 2 SEX
> 3 SQLCHAR 0 1 "\r\n" 3 HEIGHT
> In this row, the height info is null and I get a right truncation
> error. The row below, with height info goes in fine:
> Flat file row:
> 0000016M510

It would have help if you had posted a CREATE TABLE statement for your
table, and also provided a sample file where the is a row that fails.

But maybe you should try this file instead:

7.0
3
1 SQLCHAR 0 7 "" 1 RECORD_KEY
2 SQLCHAR 0 1 "" 2 SEX
3 SQLCHAR 0 3 "" 3 HEIGHT
4 SQLCHAR 0 0 "\n" 0

(I believe that \r\n or \n are the same here.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp