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

x
?
Solved

Import Excel data into specific SQL columns

Posted on 2004-10-12
12
Medium Priority
?
567 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
[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
  • 8
  • 3
12 Comments
 
LVL 6

Accepted Solution

by:
acampoma earned 75 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
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.

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

704 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