Learn the top ten threats that are present in modern web-application development and how to protect your business from them.
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
)
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
)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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)
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)
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
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
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
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
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
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
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.
The link provided above explains step-wise direction to import all your excel data to SQL Server.
Hope this works for you.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial