Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

Import Excel data into specific SQL columns

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
Galisteo8
Asked:
Galisteo8
  • 8
  • 3
2 Solutions
 
acampomaCommented:
create a DTS package and map the columns individually
0
 
apirniaCommented:
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
 
Galisteo8Author Commented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
apirniaCommented:
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
 
Galisteo8Author Commented:
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
 
apirniaCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
Ah, wait... it's under the Transform option.  Okay, so I can now map my columns properly.

0
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now