SSMS Imprt data from Excel

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
)
LVL 1
Allen PittsBusiness analystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
When you chose the destination (either by using Microsoft OLE DB or sql native client ), there is a option to choose the database  under the authentication section
Once you click Next and again next, it will ask you choose the source and destinations,  Source is the Excel sheet, once you check on the sheet number on the destination, it will show you a table name similar to the sheet name, click there and choose the appropriate target table (SFA.tempCrossInsurance)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Allen PittsBusiness analystAuthor 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
0
Allen PittsBusiness analystAuthor 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
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
Jason clarkDBA FreelancerCommented:
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.
0
Allen PittsBusiness analystAuthor 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.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Also you need to upgrade SQL 2005 server too,  MS wont support SQL 2005 any more :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.