1

Is there a way to import data from an excel sheet into a database on SQL Server without using the bulk method? Keeping in mind that I'm creating a web application where the user uploads an excel file to the page and it proceeds to import it into an existing database.

The bulk method works using OleDbDataReader, except it copies the excel sheet and pastes it onto the database without considering the column names compatibility. Am I missing something?

JNYRanger
  • 6,829
  • 12
  • 53
  • 81
Tamara Nagui Khalil
  • 13
  • 3
  • 1
    SSIS - is one way to go. – Lukasz Szozda Aug 01 '17 at 13:13
  • --Also keeping in mind I'm using C# and asp.net – Tamara Nagui Khalil Aug 01 '17 at 13:15
  • 2
    Are you looking to do this programmatic-ly, or a one-time load in SSMS? Because SSMS is a GUI for Sql Server, not a database itself. – JNYRanger Aug 01 '17 at 13:15
  • @JNYRanger one-time load.. a web application that uploads an excel file and imports the data into an already existing database and that's it – Tamara Nagui Khalil Aug 01 '17 at 13:17
  • If it's a one-time load the consumer of the database & programming language/frameworks used are irrelevant. Just use the SQL Import/Export wizard in SSMS as @Fran posted. – JNYRanger Aug 01 '17 at 13:19
  • 1
    All I had to do was use google: https://code.msdn.microsoft.com/office/Import-Excel-Spreadsheet-2b7ca7cf – Keith Aug 01 '17 at 13:21
  • okay I think I meant to say programmatically, the application will be used to import data into the database whenever i need to.. @JNYRanger – Tamara Nagui Khalil Aug 01 '17 at 13:22
  • 1
    That's a bit too broad of a question here on SO, Tamara. if you have a specific question regarding how you decided to go about this there's plenty of people who can help you here. There are many, many, many ways to do it. Personally, I would recommend using bulk methods from the `System.Data.SqlServer` namespace because they are the fastest & easiest way to do this. Just be aware that using Excel files directly isn't the easiest to work with, and it's way easier to convert them to CSV first. Good luck! – JNYRanger Aug 01 '17 at 13:27

3 Answers3

6

If you are working through SSMS, use the SQL Import Export Wizard. There's enough documentation on MSDN

From code you can use the SqlBulkCopy class for importing into SQL databases. If you are importing bulk records into Oracle you can use ODP.net OracleBulkCopy. Oracle bulk copying is only supported right now through the full ODP.net provider. Managed ODP.net does not support bulk operations.

To read the Excel file you can use any number of libraries.
LinqToExcel, OleDb, closedxml

Fran
  • 6,440
  • 1
  • 23
  • 35
0

Take a look at the DocumentFormat.OpenXml.Spreadsheet namespace: MSDN Link

It will let you look at the different parts of the spreadsheet as it is stored in OpenXML.

ahampson
  • 183
  • 1
  • 8
0

You can use format file along with bulk insert. The format file maps between table columns and the fields in the file.

Refer : https://learn.microsoft.com/en-us/sql/relational-databases/import-export/use-a-format-file-to-bulk-import-data-sql-server

S.Yang
  • 106
  • 3