Bcp - Format Files

[RitchieBoy]

I am trying to create a Format File for a bcp export procedure.

The bcp command works without the format file, but when i try to use it using -f & the name and path of the file in the bcp string, it stops working.

I get the error - 'Unable to open BCP host data file', does anyone know of why this error occurs - or any information i can get on bcp format files.

I would be greatly appreciative !!!!!!!!!!!!!!!!!!!!!!!!

 

[arrowhouse]

Books on Line, BCP Utitlity, follow using format files link.

Create your format file using some thing like:

bcp <db.owner.table> format -S<server> -U<user> -P<password> -c -t<col terminator> -r<row terminator> -e<path\error file> -f<path\format_file>

Edit it as appropriate & then you can bcp again something like:

bcp <db.owner.table> in &quot;<path\input_file>&quot; -S<server> -U<user> -P<password> -e<path\bcp_in_error_file> -E -f<path\format_file> -m<number_max_errors>

Can you post your bcp string (just <> any info you dont want to show)

I have had this message when the file is already open or the path is incorrectly specified or the user running the command doesn't have permissions on the file.

 

[mmcmullen]

Can you use bcp to write to an excel file?

 

[ClaireHCM]

when you bcp out give extension of the file as .csv and make sure you give -t, and -r\n

Double click on the .csv file it will automatically open in excel

if you need to export to a file with extension .XLS then you have to open EXCEL application and IMPORT it.

Example for case I.

bcp pubs..authors out myexel.CSV -SMyServer -
UUserid -PPassword -t, -r\n -c

Example for case II.
bcp pubs..authors out myexel.XLS -SMyServer -
UUserid -PPassword -t, -r\n -c

 

[Kendel]

RitchieBoy, did you get your problem resolved?

 

[ABerkowicz]

I am having the same problem...Unable to open BCP host data-file. Can someone help?

My script is as follows...

declare @filename char(39)
declare @str varchar(100)
set @FileName = 'c:\foldera\test_' + CONVERT(char(6), getdate(), 12)+ '.txt' --server needs to have a map to this directory
set @str = 'bcp DATABASE.dbo.TABLENAME out &quot;' + @FileName + '&quot; -c -U &quot;USERNAME&quot; -P &quot;USERPASSWORD&quot;' --user needs to have sysAdmin rights
EXECUTE master..xp_cmdshell @str

 

[RitchieBoy]

No i didn't get it fixed, i have bcp working for both files coming in & out - but can't get the format file to work still !!. Still get the same error.

If anyone has it working, if they could post a full example, that would be fantastic