Solved

SQL 2005 How to?  I would like to import an excel spreadsheet as a table, merge it with another table and export.

Posted on 2008-10-27
6
229 Views
Last Modified: 2012-05-05
What I am trying to do:

1. Import an excel file full of users into SQL.
2. SQL compares that file to an existing database and adds new users only.  Some of the users will be duplicates but I do not want it to re-add old users.  I only want their names entered once of course.
3.  SQL changes the headers to match my required headers like lastname changes to 'last names' for example.
4. SQL exports table already in proper AD format (per number 3 above) to csv file.

I need as much of this to be automated after initial set up.  Can this be done?  How?  Thanks experts.
0
Comment
Question by:Sp0cky
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22811715
You should fo that using SSIS (SQL Server Integration Services). SSIS is the new version of old DTS
Attach here the examples and I can try you more, doing a example.
Regards!
Pedro
0
 

Author Comment

by:Sp0cky
ID: 22811806
For starters, I thought I could execute the change of headers by doing this:

SELECT lastname AS surname, firstname, [phone number] as phone, email as mail
FROM yourtable


If you're creating a new table:
INSERT INTO newtable
SELECT lastname AS surname, firstname, [phone number] as phone, email as mail
FROM yourtable

But it is giving me an "invalid object " error next to "yourtable"
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22811826
You need also to add a lookup to your transformations... If you want to automatize, why you dont use SSIS? The components in SSIS are optimized to do this job.
Regards
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Sp0cky
ID: 22812174
Sorry what is a lookup and transformation.?
0
 

Author Comment

by:Sp0cky
ID: 22812565
Also, how does it know which databases to pull the tables from?
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 500 total points
ID: 22813161
You have several components in SSIS.
Basically you have SOURCES, TRANSFORMATIONS and DESTINATIONS.
You should take a look to some articles about SSIS. It's easy, intuitive, flexible and optimized.
Lookup is a component to make lookups in datasets... is a transformation type...
Check also WROX books...
I can try to search for a book here and send to your email.
Regards!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

710 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