Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
235 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

688 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