Solved

Import an Excel sheet into a SQL Server Table

Posted on 2011-09-24
9
470 Views
Last Modified: 2012-05-12
I'm using the Import Wizard to import an Excel sheet into SQL Server 2008.
I don't want to import all the fields from the sheet.

There is an option  "Write a query to specify the data to transfer". But not sure if I can use it with the Excel sheet.
I tried "Edit Mapping . ." but you can't change anything there. "Edit SQL" is greyed out also.
Also it's importing all the number fields as "float".

I basically just want to Import 3 fields from the Excel Sheet and make a table in SQL Server.
Is there another or better way of doing this? thanks
0
Comment
Question by:MikeMCSD
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 10

Accepted Solution

by:
Tony Barkdull earned 167 total points
ID: 36593404
eliminate the extraneous data from the Excel sheet prior to importing it.
0
 
LVL 7

Assisted Solution

by:mmr159
mmr159 earned 167 total points
ID: 36593421
Create a copy of the spreadsheet, remove the extra data, save-as CSV, then use the bulk copy program.
0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 36593519
I forgot to mention that I have to do this once a week, maybe twice.
I could delete the columns, but I was hoping to make this as automated as possible.



0
 
LVL 7

Expert Comment

by:mmr159
ID: 36593809
There are several ways to do this in an automated fashion.  I believe one or more of the options listed here should provide you with what you need.

http://support.microsoft.com/kb/321686

They vary in their execution.  Give it a quick read to determine which would be best in your situation.  If you need help with one, let us know.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36593831
You are going about it the wrong way.  Import the spreadsheet with all the columns and all the rows into a staging table.  When that is done you can:
1. Validate the data.
2. Import the data in your staging table into your production table making any necessary conversions and/or transformations.
0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 36593979
>> Import the spreadsheet with all the columns and all the rows into a staging table.

When I use the Import wizard, it creates a table. Is that the stagging table?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36593982
>>Is that the stagging table?<<
It could be.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 36593987
A staging table is just a scratch/temporary table that you use over and over again, by first deleting all the data before importing a new file.
0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 36593995
I'm having trouble with one of the fields in the Import. It fails and  I get this message :

 The "output column "Product Description" (24)" failed because truncation occurred, and the truncation row disposition on "output column "Product Description" (24)" specifies failure on truncation.

I have that column marked as "Not Mapped" too.
I tried making the field  nvarchar(MAX) but that didn't help either.
Why is it doing this even though it is marked as not mapped?

If I go in the sheet and delete that whole columns data, it works.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

706 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

14 Experts available now in Live!

Get 1:1 Help Now