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
Solved

Import an Excel sheet into a SQL Server Table

Posted on 2011-09-24
9
500 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

861 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