Import Data from Excel

Objective: Import the content of the excel to a table in the production database.

First, import the content of the excel file to a staging database, massage the data, and then export the data as a data script.

Steps

1. Create new excel file and copy the content to the new excel file.

Original excel file may contain extra data. Copy to new file to make sure only the data is copied over.

2. Import excel file to database.

Import all columns as text. Ex. NVARCHAR(255)

3. Massage the data.

4. Generate data script to export the data.

Database -> Tasks -> Generate Scripts

Select object and go to “Advanced”.

Set “Types of data to script” to “Data only”