Solved

Import an Excel sheet into a SQL Server Table

Posted on 2011-09-24
9
514 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
[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
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

691 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