Why am I not successfuly importing all rows of an Excel file using the SQL Bulkcopy in C# with VS2010?

I tried using the following code in C# to take advantage of SQL Bulkcopy to copy
an Excel worksheet to a SQL Server 2005 table.

Do you know why it only copied the first 11 rows?

I have attached the Excel file that I am performing a bulk transfer on.

My results are as follows:

1  Null
2  Null
3  Null
4  Null
5  Null
6  Null
7  Book Credits (D)
8  Null
9  Null
10 Process
11 Date
12 Null
13 Null
14 Null
15 Null
16 Null
17 Null
18 Null
19 Null
20 Null
21 Null
22 Null
23 Null
24 Null
25 Null
26 Null
27 Null
28 Null
29 Null
30 Null
31 Null
32 Null

My C# code is as follows:

string sourceConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=U:\105 Internal  08-28-2013.xls;  
                         Extended Properties=Excel 8.0";
            DataTable sourceData = new DataTable();

            using (OleDbConnection conn = new OleDbConnection(sourceConnString))
                // Get the data from the source table as a SqlDataReader.  
                OleDbCommand command = new OleDbCommand(
                      @"SELECT [ABC #] FROM [Book Credits (D)$]", conn);
                OleDbDataAdapter adapter = new OleDbDataAdapter(command);

            string destConnString = @"Data Source=F,3;Initial Catalog=S;User ID =P;PWD=M;";

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
                bulkCopy.DestinationTableName = "dbo.tbl_SMR_Book_Credits_D";
                // Write from the source to the destination.  

My SQL Server table is as follows:

title: dbo.tbl_SMR_Book_Credits_D

fields            data type
-------           ------------------
f1                 nvarchar(255)
f2                 nvarchar(255)
f3                 nvarchar(255)
f4                 nvarchar(255)
f5                 nvarchar(255)
f6                 nvarchar(255)
f7                 nvarchar(255)
f8                 nvarchar(255)
f9                 nvarchar(255)
f10               nvarchar(255)
f11               nvarchar(255)
f12               nvarchar(255)
f13               nvarchar(255)
f14               nvarchar(255)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jacques Bourgeois (James Burger)PresidentCommented:
I am not sure that this is your problem, but the documentation for the SqlBulkCopy states : "SqlBulkCopy will fail when bulk loading a DataTable column of type SqlDateTime into a SQL Server column whose type is one of the date/time types added in SQL Server 2008"

Since your copy stops on the 11th row, where you have a date, it might make sense, by reading the documentation, that it could be the culprit someway.

The documentation also states: "for moving data from one table to another, whether on a single server or between servers". Excel is not a server. Have you used SqlBulkCopy with Excel with other data before?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.