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.
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<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"/>
<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"/>
Thanks in advance,
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
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
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:
SELECT * FROM
'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