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.

"So I have a huge huge file in Excel that I want to use for my User Interface" - say what?? — Mitch Wheat, Feb 22 '11 at 00:40
If the file is really large, there may be problems: http://stackoverflow.com/questions/4990158/ace-oledb-drivers-unable-to-handle-huge-excel-files — Fionnuala, Feb 22 '11 at 00:49

score 0 · Accepted Answer · answered Feb 22 '11 at 00:41

0

How to: Run the SQL Server Import and Export Wizard

answered Feb 22 '11 at 00:41

Mitch Wheat

295,962
43
465
541

score -1 · Answer 2 · answered Feb 22 '11 at 00:57

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

Importing a file from Excel 2010 to visual studio SQL Server

2 Answers2