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.