Importing Data into MSSQL 2012

Question

I have an excel file (.xls) that contains important data (SSNsThe company providing the data to us is sending it in the format of 000-00-0000 but I would like to find an easy automated way of importing it into SQL2012 without the dashes. Is there a built in way, or an application that can easily automate importing the data on a regular basis (hourly hopefully)? I have experimented with the import/export wizard but at the moment have not found an easy way to remove the dashes easily. Scheduling it to automatically important seems to not be available in express.

I guess my question is - 1. How can I import the SSNs without the dashes, Do I need an external application, or can the import/export wizard handle it in a way I'm not yet aware of? 2. If Import/export wizard can handle it, is it worth spending the money on the full version to allow auto import scheduling?

I appreciate any advice. Thanks!

Community · Accepted Answer · 2017-05-23 12:29:34Z

1

The missing piece of the puzzle is SQL Server Agent, the job scheduling service included in the full version(s) of SQL Server. It's what handles recurring tasks, including import/export tasks.

You can remove the dashes using Import and Export Wizard if you select the "Write a query to specify the data to transfer" option. In your script, use this method to remove the hyphens.

As for whether or not it's worth the money, that's something only you can decide. To me, the ability to run scheduled jobs against a database is essential.

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Oct 5, 2015 at 20:56

Matt Perkins

464 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt Gibson · Accepted Answer · 2015-10-05 21:02:10Z

1

The most standard way to import data to SQL Server, transforming it along the way, is SQL Server Integration Services. You'd create a data transformation package in SSIS and schedule it with SQL Server Agent. SSIS can read data directly from an Excel file, transform it pretty much however you want, and write it directly to a SQL Server table.

But for something as simple as that, I might be tempted to import it with the dashes, then just run a simple SQL update statement to strip them out:

UPDATE imported_table SET ssn = REPLACE(ssn, '-', '')

answered Oct 5, 2015 at 21:02

Matt Gibson

38.3k10 gold badges104 silver badges130 bronze badges

Collectives™ on Stack Overflow

Importing Data into MSSQL 2012

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related