We help IT Professionals succeed at work.

SSMS Imprt data from Excel

190 Views
Last Modified: 2017-04-07
Hello expert,

Have built table in SQL Server Management Studio with create table statement
copied herewith below.
Now trying to get data into it from Excel spreadsheet.

So I Googled it and got some direction. The instructions say to open the Import Export Wizard
by going to the database icon in the tree (not the Table icon)  and choosing Tasks>Import Data.
This gets me to the Wizard. It asks me about the Data Source and the path to the Excel Worksheet.
(I work for a healthcare company so we have old tools, SSMS ver 9 and SQL Server 2005. So
I and to convert the Excel from xlsx to xls because the SSMS ver 9 doesn't know that xlsx files
exist.)
Then it goes to Choose a Destination and this where the problem is. It allows a choice for
destination like Microsoft OLE DB Provider for SQL Server and SQL Native Client. I chose the former.
And the Server Name can be chosen and the Database. But it does not allow me to say which table in which to
write the data. It then asks me about the table to use in the table Export Worksheet$.
On the Complete the Wizard dialogue box it says it will copy the Worksheet to [TempData].['Export Worksheet$']
and the new table will be created.
A new table is not wanted. The data should be copied into the existing table.

Where does it let me choose the table?

Thanks.

Allen in Dallas

CREATE TABLE SFA.tempCrossInsurance
(
EMPLOYER_ID int,
EMPLOYER_NAME varchar(50),
SERV_PCKG_NAME varchar(50),      
SERV_PCKG_ID int,      
PAYER_PACKAGE_ID int,      
PYR_PCKG_NAME varchar(50),      
MARKET_ID int,      
MARKET_NAME varchar(50),      
PAYER_PACKAGE_ROLODEXID int
)
Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Allen PittsBusiness analyst

Author

Commented:
Aneesh,

By accident I chose, as Data Source = SQL Native Client  The Server NAme and the Database
then
Copy data from one or more tables or views
Then it loaded all the tables and I picked
SFA.tempCrossInsurance
Then Execute Immediately > Finish
But it says '0 rows transferred
This is because I transferred from the wrong source, the empty  SFA.tempCrossInsurance table.

So I started over and picked as Data Source = Microsoft Excel
Oh. I just found it. As you said after I check the Export Worksheet box the existing
table becomes available under the Destination heading.
So I click thru to Finish. But alas no joy. I get in the Validating section
- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - 'Export Worksheet$'" (1).
 (SQL Server Import and Export Wizard)
 
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (67).
 (SQL Server Import and Export Wizard)

Is there a way around the validation error?

Thanks

Allen
Allen PittsBusiness analyst

Author

Commented:
Aneesh,

 By accident I chose, as Data Source = SQL Native Client  The Server NAme and the Database
 then
 Copy data from one or more tables or views
 Then it loaded all the tables and I picked
 SFA.tempCrossInsurance
 Then Execute Immediately > Finish
 But it says '0 rows transferred
 This is because I transferred from the wrong source, the empty  SFA.tempCrossInsurance table.

 So I started over and picked as Data Source = Microsoft Excel
 Oh. I just found it. As you said after I check the Export Worksheet box the existing
 table becomes available under the Destination heading.
 So I click thru to Finish. But alas no joy. I get in the Validating section
 - Validating (Error)
 Messages
 Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - 'Export Worksheet$'" (1).
  (SQL Server Import and Export Wizard)
 
 Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (67).
  (SQL Server Import and Export Wizard)

 Is there a way around the validation error?

 Thanks

 Allen
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
You were able to import all the rows into a table onto a temp table initially right ?  If not can you try ? I dont think its a validation error, when I googled  it, MS support people were mentioning they will fix it with SQL 2005 SP1. If thats the case you need to patch your server.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/289d0bbb-1d06-4856-9c66-8d3e540d7be9/product-level-is-insufficient-message-during-flat-file-import?forum=sqlintegrationservices

Also you could try using the OPENROWSET method mentioned below

http://sequelserver.blogspot.ca/2006/04/update-table-from-excel-sheet-using.html
Jason clarkDBA Freelancer

Commented:
You may be doing some thing wrong, to import data from excel to sql server this might be helpful to you.

The link provided above explains step-wise direction to import all your excel data to SQL Server.

Hope this works for you.
Allen PittsBusiness analyst

Author

Commented:
I believe I am getting the Insufficient Product Level error because I am using such an ole version of SQL Server client with the SSMS. Will try upgrading the my SLQ Server Client.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
Also you need to upgrade SQL 2005 server too,  MS wont support SQL 2005 any more :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions