Solved

Import Excel data into specific SQL columns

Posted on 2004-10-12
12
561 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

628 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