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

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

Import an Excel sheet into a SQL Server Table

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
MikeMCSD
Asked:
MikeMCSD
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
Tony BarkdullCommented:
eliminate the extraneous data from the Excel sheet prior to importing it.
0
 
mmr159Commented:
Create a copy of the spreadsheet, remove the extra data, save-as CSV, then use the bulk copy program.
0
 
MikeMCSDAuthor Commented:
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
Independent Software Vendors: 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!

 
mmr159Commented:
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
 
Anthony PerkinsCommented:
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
 
MikeMCSDAuthor Commented:
>> 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
 
Anthony PerkinsCommented:
>>Is that the stagging table?<<
It could be.
0
 
Anthony PerkinsCommented:
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
 
MikeMCSDAuthor Commented:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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