Import Excel Spreadsheet Data to an EXISTING sql table?

Question

I have a table called tblAccounts whose contents will come from an excel spreadsheet.

I am using MS SQL Server 2008 (x64) on a Windows 8.1 (x64)

I tried using the SQL Server Import/Export Wizard but there is no option to choose an existing table but only an option to create a new one.

I tried using other methods such as OPENROWSETS

INSERT INTO tblAccount SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 12.0;Database=D:\exceloutp.xls','SELECT * FROM [Sheet1$]')

but gave me an error:

Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Some research told me that it occurred because of a 64-bit instance of SQL server.

The problem is that this Excel data transfer to a SQL table must be accomplished using the SQL Import/Export Wizard only.

How can I import an Excel spreadsheet to an existing SQL table without creating a new one?

Some links I visited but was not able to help me resolve my problem:

• How do I import an excel spreadsheet into SQL Server?
• Fix OLE DB error

Whimsicality

Why not import into a new temp table and then 'select into' the actual table you want? – Tellus

I really like to use your suggestion Mr. Brennan, but we are restricted from only using the SQL server import/export wizard. Is it possible through the said wizard? Thank you! – Whimsicality

I have done the same using bcp Utility msdn.microsoft.com/en-us/library/ms162802.aspx , but I have never try to import/export excel file. You can check it. – Zinnia

Answer 1

Saudate, I ran across this looking for a different problem. You most definitely can use the Sql Server Import wizard to import data into a new table. Of course, you do not wish to leave that table in the database, so my suggesting is that you import into a new table, then script the data in query manager to insert into the existing table. You can add a line to drop the temp table created by the import wizard as the last step upon successful completion of the script.

I believe your original issue is in fact related to Sql Server 64 bit and is due to your having a 32 bit Excel and these drivers don't play well together. I did run into a very similar issue when first using 64 bit excel.

Helen

Thanks! I was able to use the Import Wizard to transfer my Excel file to my table. I just did not see the option to select a table but indeed there was. I also used a comma delimited file and created a temp table as a work around! Thank you! – Whimsicality

Answer 2

You can copy-paste data from en excel-sheet to an SQL-table by doing so:

• Select the data in Excel and press Ctrl + C
• In SQL Server Management Studio right click the table and choose Edit Top 200 Rows
• Scroll to the bottom and select the entire empty row by clicking on the row header
• Paste the data by pressing Ctrl + V

Note: Often tables have a first column which is an ID-column with an auto generated/incremented ID. When you paste your data it will start inserting the leftmost selected column in Excel into the leftmost column in SSMS thus inserting data into the ID-column. To avoid that keep an empty column at the leftmost part of your selection in order to skip that column in SSMS. That will result in SSMS inserting the default data which is the auto generated ID.

Furthermore you can skip other columns by having empty columns at the same ordinal positions in the Excel sheet selection as those columns to be skipped. That will make SSMS insert the default value (or NULL where no default value is specified).

Travesty

This is not fast, but it works, it took 15 or 20 minutes to load 24,968 rows with 5 populated columns in a table with 8 columns. – Tiphanie

I believe the empty column in the selection should be the leftmost, to match the placement of the ID column in the leftmost position in SSMS. – Neediness

Just a note that if you have an error (e.g. if you don't leave an empty column in your copied data and try to enter it into a table with an ID column), you'll have to click "OK" on the error message the same number of times as there are rows in your copied data. There's no way to cancel. – Moritz

For very less data it is a good option but if data is more import is the faster way to do it. – Picrate

Answer 3

You can use import data with wizard and there you can choose destination table.

Run the wizard. In selecting source tables and views window you see two parts. Source and Destination.

Click on the field under Destination part to open the drop down and select you destination table and edit its mappings if needed.

EDIT

Merely typing the name of the table does not work. It appears that the name of the table must include the schema (dbo) and possibly brackets. Note the dropdown on the right hand side of the text field.

Pristine

If your import file matches the name of the existing table then the import wizard will automatically suggest to amend data to the existing table instead of creating a new table. – Gratiana

This is absolutely the answer and should be the ticked one. Just use the drop-down and pick the table you need, couldn't be simpler. – Inflate

Answer 4

If you would like a software tool to do this, you might like to check out this step-by-step guide:

"How to Validate and Import Excel spreadsheet to SQL Server database"

http://leansoftware.net/forum/en-us/help/excel-database-tasks/worked-examples/how-to-import-excel-spreadsheet-to-sql-server-data.aspx

Sprang

Answer 5

You can alternatively run a Powershell command

-- Change Source ($workbook), and under the connection string both the server and database name

# Load Excel COM object

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $false



# Open Excel file

$workbook = $excel.Workbooks.Open("C:\ABC\EmployeeRecords.xlsx")

$sheet = $workbook.Sheets.Item(1)



# Create SQL connection

$connectionString = "Server=MSSQL-ABCD;Database=AWPractDB;Integrated Security=True;"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = $connectionString

$sqlConnection.Open()



# Read data from Excel and insert into SQL

$rowIndex = 2

while ($sheet.Cells.Item($rowIndex, 1).Value() -ne $null) {

  $firstName = $sheet.Cells.Item($rowIndex, 1).Value()

  $lastName = $sheet.Cells.Item($rowIndex, 2).Value()

  $department = $sheet.Cells.Item($rowIndex, 3).Value()



  $query = "INSERT INTO EmployeeRecords (FirstName, LastName, Department) VALUES ('$firstName', '$lastName', '$department')"

  $command = $sqlConnection.CreateCommand()

  $command.CommandText = $query

  $command.ExecuteNonQuery()



  $rowIndex++

}



# Close the Excel and SQL connection

$workbook.Close($false)

$excel.Quit()

$sqlConnection.Close()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)



Write-Output "Data imported successfully"

Dehiscent