Solved

Import Excel data into specific SQL columns

Posted on 2004-10-12
12
529 Views
Last Modified: 2010-05-19
I know I've read this somewhere, but I can't seem to find it.

I need to be able to import specific non-contiguous Excel columns into specific non-contiguous SQL table columns. For example, import data from Excel columns A, B, J, and T into SQL table columns 1, 4, 5, and 12.

What's the best way to do this?

I'm using SQL Server 2000, with an Access2000 front-end.

Thanks,
Galisteo8
0
Comment
Question by:Galisteo8
  • 8
  • 3
12 Comments
 
LVL 6

Accepted Solution

by:
acampoma earned 25 total points
ID: 12291460
create a DTS package and map the columns individually
0
 
LVL 9

Expert Comment

by:apirnia
ID: 12292254
If it is 1to1 mapping you have to go with acampoma  comment.

But if you want to do A and B to colum 1 then you have to concatenate befor insertion.
When you create your DTS package with the Wizared it walks you through these steps.
Or you can click on the Transformation link of your DTS and set the mapping over there.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12292608
Oh, no -- I'm not combining columns together.

I have a spreadsheet with about 20 columns. I need to put some of the columns into one SQL table, some of the columns into another SQL table, etc. Bu tthe Excel columns that import into the same table are not necessarily toegether in the spreadsheet. Likewise, the columns they need to import into are not necessarily next to eachother in the SQL table, either.

In Enterprise Manager, if I jut right click the table I want to import into and select All Tasks > Import, is that how a DTS package is started?  (I'm a noob at this.)

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12292738
LOL, never mind.  It says right there - Data Transformation Services. I'll give it a go and let you know if I have more questions.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12292776
Okay, I see where I can easily import it via DTS into a NEW table... But where do I actually tell it the destination table and columns for an existing table?
0
 
LVL 9

Assisted Solution

by:apirnia
apirnia earned 25 total points
ID: 12292839
When you use the WIzzard it will first ask you the Data SOurce so you select the SOurce either the DB or Excel. If it is excel from the drop down you must use the Excel connection. Then it will give you an option to Browse and locate the file. WHen you click next it will ask you for the Destination. In here you do the same thing select which ever you want to do. and then Next it should help you out step by step.

I really recommend using the wizzard if you are not using it.

Open Enterprise Manager >> Right click on your Database >> all task >> Export Data

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 8

Author Comment

by:Galisteo8
ID: 12293073
I see where to specify the destination as my SQL database. But I do *not* see where to specify an existing table into which to append the imported data...
0
 
LVL 9

Expert Comment

by:apirnia
ID: 12293107
The wizzard should show you Step by Step. It tells you to Pick Destination, Pick a Source.

Lets say the Destination is the Excel file and you found it and the Source is a Database Table.

When you want to pick the database table first you select the Server name and the Database name. at this screen you wont see anything about the tables in that database.

but if you continue to the steps at one point shows you a list of all tables in the dastabase you selected on previous steps

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12298651
Okay, I see the pulldown where I can specify the destination table, but the only "column mapping" function I see will create a NEW table...

I still need to be able to specify which columns from the imported Excel data go into which columns of the SQL table. The column names in the Excel do not match the column names in the SQL table.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12298727
Ah, wait... it's under the Transform option.  Okay, so I can now map my columns properly.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12298805
Is there a way to overwrite existing data (or nulls) during an import?  Let's say I have a SQL table that will hold information on customers -- firstname, lastname, address, telephone. And let's say I have two Excel spreadhseets, one with firstname, lastname, and address, and the other with firstname, lastname, and telephone.  After importing the addresses and names, how do I then import the telephone numbers so that they "match up" to the names?
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12321526
At this time, the boss has indicated that we will NOT be importing the data into the new SQL tables, but will rather leave it where it's at (Excel and old SQL tables) as an archive. SO, I guess my question is moot now. Thanks for steering me in the right direction for imports, although I still didn't get an answer on how to import data columns into existing records in SQL.... but if I ever have to cross that bridge, I'll re-post.  :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

762 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

21 Experts available now in Live!

Get 1:1 Help Now