Solved

Import Excel data into specific SQL columns

Posted on 2004-10-12
12
558 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create a Calendar table 29 45
Using a SUBQUERY for the set variable 10 30
When are cursors useful? 8 62
VM SQL server license. 1 67
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 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