none
SQLBulkCopy Formatting Issue RRS feed

  • Question

  • I am attempting to import an Excel spreadsheet (.xls file format) into SQL Server 2005 using the SQL BulkCopy class in an ASP.NET web page.  Most of the data is being imported correctly, but I am having a problem with one column.  The data in this column can be either numeric or alphanumeric, but I have both the column in Excel and the column in the database table formatted as text.  The problem is that either the numeric data is beng replaced with NULL values or the alphanumeric data is being replaced with NULL values.  How do I get both types to import properly?  The code I am using is below:

     

    Code Snippet

    Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("~/ExcelImport.xls") & ";Extended Properties=""Excel 8.0;HDR=YES;"""

    Dim sqlConnectionString As String = "(serverlogininfohere)"

    Using connection As New Data.OleDb.OleDbConnection(excelConnectionString)

    Dim command As New Data.OleDb.OleDbCommand("Select * FROM [Sheet1$]", connection)

    connection.Open()

    ' Create DbDataReader to Data Worksheet

    Using dr As Data.Common.DbDataReader = command.ExecuteReader()

    ' Bulk Copy to SQL Server

    Using bulkCopy As New Data.SqlClient.SqlBulkCopy(sqlConnectionString)

    bulkCopy.DestinationTableName = sqltable

    Try

    bulkCopy.WriteToServer(dr)

    Catch ex As Exception

    lblResult.Visible = True

    lblResult.Text = (ex.Message)

    Finally

    dr.Close()

    End Try

    End Using

    End Using

    connection.Close()

    End Using

     

     

    Thanks in advance!

     

    Thursday, October 2, 2008 9:06 PM

Answers

All replies