IMPORT EXCEL files into SQL SERVER database

Posted on 2012-09-19
Last Modified: 2012-09-20

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.
Question by:molune
    LVL 12

    Assisted Solution

    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.
    LVL 3

    Expert Comment

    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.

    Assisted Solution


    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 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
    LVL 13

    Accepted Solution

    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:
    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.

    LVL 21

    Assisted Solution

    by:Alpesh Patel
    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.

    Author Closing Comment

    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

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now