• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1887
  • Last Modified:

IMPORT EXCEL files into SQL SERVER database


I have two models of EXCEL files with many sheets and thousands of EXCEL files filled according these two models.

I have an SQL SERVER Database.

I don’t know what the best solution for importing the data from EXCEL to SQL SERVER is. I planned to write a C# code because of several reasons:
• The import has to be automatic by verifying whether files with proper name are present in a given directory;
• The data have to be checked before being imported and the import of the entire EXCEL file has to be abandoned  if some data is not good
• There are many foreign keys:
o      Each sheet of the first EXCEL file has a column named hole_name
o      Each sheet of the second file has a column named hole_name and a column named sample_name.
o      The database has the following structure:
¿      table HOLE : hole_id (PK IDENTITY column), hole_name, …
¿      table SAMPLE: sample_id (PK IDENTITY column), hole_id (foreign key), …
¿      Many tables linked to HOLE: table_id (identity column), hole_id (foreign key)
¿      Many tables linked to SAMPLE
• Some data have to be transformed, for example:
o      Yes and No in EXCEL transformed in bit 0 or 1 (boolean) in SQL SERVER
o      Some code of six characters in EXCEL sheet transformed in the ID corresponding to the code in a dictionary table in SQL SERVER
• A report has to be written in order to say whether the import was successful or not and if not, why …

If C# is used, which are the best components, to connect to EXCEL, to connect to the database, ... ?

Could SSIS be used? Or would another way be more convenient?

Thank you for your help.
4 Solutions
You could do this with SSIS - although probably not without some custom scripts.

You could use an OLEDB connection to connect to the excel files (and database) from c#.

You have your work cut out for you no matter which avenue you choose, so
I would suggest playing to your strengths.
You could use SSIS, but due to difficulty and .NET code, it is not be best choice.  The other option would be to use the Data Import Wizard.  Right click the database you want to import it too, go to tasks, and the Import.  It will start a wizard.  You select Excel as the source and the destination would be your database.  It will ask what you want to import, what fields need to be imported, and what field types you will want to use.

I would suggest to use SSIS but do it in two packages

1) Package one. Use .net to validate excel but export data to a file (SSIS is not very good with Excel....) use unique delimited like | SSIS sometimes has problems with ". Save file

2) SSIS Package to import file.

This is my preferred approach and I would suggest to do two types of packages especially if you deal with large amounts of spreadsheet....it is easier in long term.

If you need any info about SSIS below is link to our tutorial (simple stuff)

SSIS Tutorial for beginners

p.s. Fixing source might be easier....This would be a change but I always check if Master Data Services (MDS) can help which is available in SQL Server 2012 (recommended with Excel ad-in) and SQL Server 2008 R2 (hidden and I don't recommend it)

Hope that helps
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

While SSIS is for sure the right place where you can perform this task, you can also think about developing a c# program that does the job.
I would strongly suggest you to take a look at: http://exceldatareader.codeplex.com.
This is a library that can let you easily import a XLS file (both '97/2003 and 2007/2010 versions) into a structured data type, such as a System.Data.DataSet.
Once imported you can check the DataSet as for your checklist.
I would suggest you to use a System.IO.FileSystemWatcher object when you need to look for a file dropped in a folder. This can avoid you to do continuous file-system checks.

Alpesh PatelAssistant ConsultantCommented:
SSIS is the best way to import excel file in SQL.

You can transfer error rows to different destination other than where you want to move or

Validate excel file data before load using Data profiling task.
moluneAuthor Commented:
Hello. Thank you for your answers. I don't know which solution to choose ... but I learned some things and it encourages me to continue .

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now