Solved

Import an Excel sheet into a SQL Server Table

Posted on 2011-09-24
9
482 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

867 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

24 Experts available now in Live!

Get 1:1 Help Now