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
200 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
  • 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

910 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

20 Experts available now in Live!

Get 1:1 Help Now