Importing a file from Excel 2010 to visual studio SQL Server

Question

So I have a huge huge file in Excel that I want to use for my User Interface in Visual Studio WPF. I have my rest of the database stored in SQL Server that came with Visual Studio. Is there any way I could Import this data from Excel into my Visual Studio Sql Server?

Thanks for your help.

Answer 1

How to: Run the SQL Server Import and Export Wizard

Answer 2

Personally I would use the Microsoft.Office.Interop.Excel Namespace and write some code like this save the file as a CSV, and then dump it into SQL using either the BULK COPY command or in Microsoft c#.net you can use the

SqlBulkCopy object to do this.

So you would read the CSV line by line and write it out to a DataTable. then use the following to write to SQL.

Example of the bulkCopy Object in VB.NET (Sorry i am using it in vb.net not c# for something)

 Public Function InsertDataToDatabase(ByVal _strDestinationTableName As String, ByRef _dtData As DataTable, ByRef _sqlConnection As SqlConnection) As Boolean
        Try

            RaiseEvent BulkCopyStartEvent(Me, _dtData.Rows.Count())

            OpenConnection()

            sBulkCopy = New SqlBulkCopy(DatabaseConnection)

            'Clear out all data in the TmpTable

            Dim sqlComm As New SqlCommand(String.Format("TRUNCATE TABLE {0}", _strDestinationTableName), DatabaseConnection)
            sqlComm.ExecuteNonQuery()

            With sBulkCopy

                sBulkCopy.DestinationTableName = _strDestinationTableName
                sBulkCopy.NotifyAfter = _dtData.Rows.Count / 100 ' Notify after every 1%
                sBulkCopy.WriteToServer(_dtData)
                sBulkCopy.Close()
            End With

            RaiseEvent BulkCopyCompleteEvent(Me, _dtData.Rows.Count(), arrExceptionStringList.Count())

            Return True

        Catch ex As BulkCopyUtilityErrorException
            RaiseEvent BulkCopyErrorEvent(Me, ex)
        Finally
            CloseConnection()
        End Try
    End Function