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 utility

I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

Thanks allYou can use the extended stored procedure xp_cmdshell to execute bcp from a
stored procedure. Please read in SQL Server Books Online the security
implications. Assuming you do not want to run it under an account that is
member of sysadmin, you may want to set up a proxy account via
sp_xp_cmdshell_proxy.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Mike (mckeyes@.gmail.com) writes:

Quote:

Originally Posted by

I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

As Plamen said, you can use xp_cmdshell, but xp_cmdshell is a security
risk and for this reason it is disabled by default. It may be better
to write a small application VBscript or whatever you fancy to run
the export.

--
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

Bcp header

I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppasswor
d
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be writte
n
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksSee this thread:
http://groups.google.ca/groups?selm...0%40tkmsftngp12
Anith|||Use a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:

> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassw
ord
> The command works beautifully except that it overwrites the header column
of
> the excel file as well. Is there anyway in bcp to have the results be writ
ten
> leaving the header column intact. Alternatively, I would like the results
to
> be written along with the column names.
> Thanks

BCP Excel file to SQL Server 2005

How do you BCP an Excel file to a table in SQL Server 2005. The XML Schema is simple and is only bringing in 5 columns. When I run the command 0 rows are copied. It doesnt error out but data is not transferred either.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Column1" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="2" NAME="Column2" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="3" NAME="Column3" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="4" NAME="Column4" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="5" NAME="Column5" xsi:type="SQLNCHAR"/>
</ROW>
</BCPFORMAT>

Thanks in advance,

Mike

Did you use the [ -x ] bcp parameter to get the format?

Perhaps this will help:

http://mssqltips.com/tip.asp?tip=1060

( For more assistance, you might post the complete bcp command line, and the table DDL.)

|||

Here is my bcp command line that I use:

bcp srs.[load].Example1 in \\spsqlfi050\downloads\Example1.xls -e \\spsqlfi050\downloads\bcperrorfiles\ErrorExample1.txt -x -S sdflshfi01 -T

The table that I am trying to upload the excel spreadsheet to is:

[Column1] [nchar] (3) NULL,

[Column2] [nchar] (6) NULL,

[Column3] [nchar] (70) NULL,

[Column4] [nvarchar] (25) NULL ,

[Column5] [nvarchar] (20) NULL

Mike

|||

This is not a bcp load command. This command with [ -x ] ONLY creates the format file.

Check the bcp documentation (Books Online, Topic: BCP Utility) for the correct syntax for using bcp to load data. (Expand the 'Arguments' section for details.)

|||

Sorry wrong one. Here is the BCP command I use:

bcp srs.[load].Example1 in \\spsqlfi050\downloads\Example1.xls -e \\spsqlfi050\downloads\bcperrorfiles\ErrorExample1.txt -f \\spsqlfi050\downloads\bcpformatfiles\Example1.xml -S sdflshfi01 -T

Mike

|||

Consider adding a [ -o ] outputfilename parameter to capture non-error messages so that you can find out what is happening.

Then between the error file and the output file, there 'should' be some good clues.

|||

I added an outputfilename parameter to my bcp command and is only providing me:

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

The load is not transferring any data. The spreadsheet has over 1,200 rows of data.

|||I could be wrong, but I don't think bcp is supposed to be able to load .xls files. Books Online says to prepare Excel data for bulk loading by converting it into a comma-separated value formatted file (.csv).

You can use the Jet provider through OPENROWSET or OPENDATASOURCE to select data from an Excel file, or you can create a linked server that points to the file. For example, this will select data from the range A1:N1535 of the worksheet named XYZ in C:\a.xls:

Code Snippet

SELECT * FROM
OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;IMEX=1;HDR=YES;Database=C:\a.xls',
'SELECT Format(SomeCol) AS FC,* FROM [XYZ$A1:N1535]'
)

In the query inside OPENQUERY, you can do much (most) of what Jet (Access) SQL allows, such as the Format() function shown here. There is also syntax for selecting from named ranges or entire worksheets of the file, instead of a coordinate range of cells.

You'll find more about some of the details here: http://groups.google.com/groups?q=ABE0A32D754D

Steve Kass
Drew University
http://www.stevekass.com
|||Good catch Steve, I overlooked that the OP was wanting to use bcp with a xml data file.

BCP Error

Hi,
I'm getting the following error when I use BCP to import a tab
seperated file exported from Excel.
I hope someone can help me figure it out.
I'm using the following command:
c:\>bcp myComm.dbo.cmrc_products in test2.txt -n -T -Sbiff\sqlexpress
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP
data-fil
e
I think it has something to do with the column widths and datatypes of
the import, but I don't know. I've been staring at a command line for
so long, I can't see straight.
Thanks for the help.
Tom
i figured this one out myself after many cups of coffee...
Basically my datafile was FUBAR and I wasn't using the -c switch...
bcp is VERY touchy and gives no breaks at all. Attention to detail is
the order of the day when using this little proggie.
|||i figured this one out myself after many cups of coffee...
Basically my datafile was FUBAR and I wasn't using the -c switch...
bcp is VERY touchy and gives no breaks at all. Attention to detail is
the order of the day when using this little proggie.

bcp column names

I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be written
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
Thanks
Use a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:

> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
> The command works beautifully except that it overwrites the header column of
> the excel file as well. Is there anyway in bcp to have the results be written
> leaving the header column intact. Alternatively, I would like the results to
> be written along with the column names.
> Thanks
>

bcp column names

I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be written
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksUse a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:
> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
> The command works beautifully except that it overwrites the header column of
> the excel file as well. Is there anyway in bcp to have the results be written
> leaving the header column intact. Alternatively, I would like the results to
> be written along with the column names.
> Thanks
>

bcp column names

I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppasswor
d
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be writte
n
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksUse a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:

> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassw
ord
> The command works beautifully except that it overwrites the header column
of
> the excel file as well. Is there anyway in bcp to have the results be writ
ten
> leaving the header column intact. Alternatively, I would like the results
to
> be written along with the column names.
> Thanks
>

BCP Formatting Output

Hi,

I wrote the below code and procedure that exports two tables contents into 2 separate Excel files. Is there a way to export contents of two tables via BCP utility into 1 Excel file but 2 different Worksheets of this file?

DECLARE @.FileName varchar(50),
@.FileName1 varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName = 'E:\GPPD_db_stats.XLS'
SET @.FileName1 = 'E:\GPPD_file_stats.XLS'
print @.FileName
SET @.bcpCommand = 'bcp "master.dbo.spdbdesc" OUT ' + @.FileName + ' -Samex-srv-gppdb -T -c'
print @.bcpCommand
EXEC master..xp_cmdshell @.bcpCommand

SET @.bcpCommand = 'bcp "master.dbo.spfiledesc" OUT ' + @.FileName1 + ' -Samex-srv-gppdb -T -c'
print @.bcpCommand
EXEC master..xp_cmdshell @.bcpCommand
exec master.dbo.xp_stopmail
set @.bcpCommand = ' ' + @.FileName + '; ' + @.FileName1 + ''
DECLARE @.body VARCHAR(1024)
SET @.body = 'Please find enclosed files with the database status reports as of '+
CONVERT(VARCHAR, GETDATE()) + '. Please DO NOT respond to this email or the ones coming in the future ' +
'with data files as this email address is not monitored for incoming emails. However, if you have any ' +
'questions/concerns please contact ...'


EXEC master..xp_sendmail
@.recipients='alla.levit@.amex.com',
@.message = @.body,
@.subject = 'Database Weekly Statistics Report',
@.attachments = @.bcpCommand

Thanks in advance!
-AllaGood luck. I've never been able to pull this off.