none
BCP Excel file to SQL Server 2005

    Question

  •  

    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

    Wednesday, May 02, 2007 9:12 PM

All replies

  • 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.)

    Wednesday, May 02, 2007 11:52 PM
    Moderator
  • 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

    Thursday, May 03, 2007 2:06 PM
  • 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.)

     

    Thursday, May 03, 2007 2:13 PM
    Moderator
  • 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

    Thursday, May 03, 2007 2:22 PM
  • 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.

    Thursday, May 03, 2007 2:35 PM
    Moderator
  • 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.

    Thursday, May 03, 2007 7:04 PM
  • 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
    Friday, May 04, 2007 2:22 AM
  • Good catch Steve, I overlooked that the OP was wanting to use bcp with a xml data file.
    Friday, May 04, 2007 2:46 AM
    Moderator