By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,461 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

how to import a data to table from file and export data from table to file

priyan
P: 54
hi all,
I am newbie to sql server 2005. I want to import a bulk data from an excel file to the table..........pls suggest me a solution on how to do this................pls reply me as soon as possible...........


thanks in advance,
priyan.
Nov 19 '07 #1
Share this Question
Share on Google+
5 Replies


Jim Doherty
Expert 100+
P: 897
hi all,
I am newbie to sql server 2005. I want to import a bulk data from an excel file to the table..........pls suggest me a solution on how to do this................pls reply me as soon as possible...........


thanks in advance,
priyan.
various ways to do this...one way is to setup a linked server to the spreadsheet if its a static file on disk

Expand|Select|Wrap|Line Numbers
  1. EXEC sp_addlinkedserver 'XLWorkbook', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', '\\MyServer\MyShare\MySpreadsheets\workbook.xls', NULL, 'Excel 8.0'
then you can query it as though it were in your database for instance

Expand|Select|Wrap|Line Numbers
  1. SELECT * INTO test.dbo.mytable 
  2. FROM XLWorkbook...[Sheet1$]
will take that data and place into a database called 'test' having a physical table called 'mytable'

or

Expand|Select|Wrap|Line Numbers
  1. SELECT field1,field2,field3 FROM XLWorkbook.Sheet1$
will selected directly from the linked server spreadsheet itself


and if you don't want to use a linked server arrangement at all... then you can use OPENROWSET where the following will insert into a table called MyTable values from a spreadsheet called myspreadsheet.xls located at the mentioned filepath and from Sheet1$ of that workbook

Expand|Select|Wrap|Line Numbers
  1.  
  2. INSERT INTO dbo.MyTable 
  3. SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
  4. 'Excel 8.0;Database=C:\Documents and Settings\MyName\Desktop\myspreadsheet.xls', [Sheet1$])
  5.  

You might also want to look at commands BCP and BULK INSERT to give you general ideas how to import into SQL Server there are lots of references on the web and also in SQL Server Books online

Regards

Jim :)
Nov 19 '07 #2

priyan
P: 54
various ways to do this...one way is to setup a linked server to the spreadsheet if its a static file on disk

Expand|Select|Wrap|Line Numbers
  1. EXEC sp_addlinkedserver 'XLWorkbook', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', '\\MyServer\MyShare\MySpreadsheets\workbook.xls', NULL, 'Excel 8.0'
then you can query it as though it were in your database for instance

Expand|Select|Wrap|Line Numbers
  1. SELECT * INTO test.dbo.mytable 
  2. FROM XLWorkbook...[Sheet1$]
will take that data and place into a database called 'test' having a physical table called 'mytable'

or

Expand|Select|Wrap|Line Numbers
  1. SELECT field1,field2,field3 FROM XLWorkbook.Sheet1$
will selected directly from the linked server spreadsheet itself


and if you don't want to use a linked server arrangement at all... then you can use OPENROWSET where the following will insert into a table called MyTable values from a spreadsheet called myspreadsheet.xls located at the mentioned filepath and from Sheet1$ of that workbook

Expand|Select|Wrap|Line Numbers
  1.  
  2. INSERT INTO dbo.MyTable 
  3. SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
  4. 'Excel 8.0;Database=C:\Documents and Settings\MyName\Desktop\myspreadsheet.xls', [Sheet1$])
  5.  

You might also want to look at commands BCP and BULK INSERT to give you general ideas how to import into SQL Server there are lots of references on the web and also in SQL Server Books online

Regards

Jim :)


hi Jim thanks for u suggestion but i am getting an error when i used the syntax as if u r given
when i used ur first command

Expand|Select|Wrap|Line Numbers
  1.  
  2. INSERT INTO dbo.MyTable 
  3. SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
  4. 'Excel 8.0;Database=C:\Documents and Settings\MyName\Desktop\myspreadsheet.xls', [Sheet1$])
  5.  

my error is
Expand|Select|Wrap|Line Numbers
  1. Msg 15281, Level 16, State 1, Line 1
  2.  
  3. SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. 
  4.  
and for the second command

Expand|Select|Wrap|Line Numbers
  1. EXEC sp_addlinkedserver 'XLWorkbook', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', '\\MyServer\MyShare\MySpreadsheets\workbook.xls', NULL, 'Excel 8.0'
Expand|Select|Wrap|Line Numbers
  1. SELECT * INTO test.dbo.mytable 
  2. FROM XLWorkbook...[Sheet1$]
my error is
Expand|Select|Wrap|Line Numbers
  1. OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLWorkbook" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
  2. Msg 7399, Level 16, State 1, Line 1
  3. The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLWorkbook" reported an error. Authentication failed.
  4. Msg 7303, Level 16, State 1, Line 1
  5. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLWorkbook".
  6.  
Nov 20 '07 #3

Jim Doherty
Expert 100+
P: 897
hi Jim thanks for u suggestion but i am getting an error when i used the syntax as if u r given
when i used ur first command

Expand|Select|Wrap|Line Numbers
  1.  
  2. INSERT INTO dbo.MyTable 
  3. SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
  4. 'Excel 8.0;Database=C:\Documents and Settings\MyName\Desktop\myspreadsheet.xls', [Sheet1$])
  5.  

my error is
Expand|Select|Wrap|Line Numbers
  1. Msg 15281, Level 16, State 1, Line 1
  2.  
  3. SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. 
  4.  
and for the second command

Expand|Select|Wrap|Line Numbers
  1. EXEC sp_addlinkedserver 'XLWorkbook', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', '\\MyServer\MyShare\MySpreadsheets\workbook.xls', NULL, 'Excel 8.0'
Expand|Select|Wrap|Line Numbers
  1. SELECT * INTO test.dbo.mytable 
  2. FROM XLWorkbook...[Sheet1$]
my error is
Expand|Select|Wrap|Line Numbers
  1. OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLWorkbook" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
  2. Msg 7399, Level 16, State 1, Line 1
  3. The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLWorkbook" reported an error. Authentication failed.
  4. Msg 7303, Level 16, State 1, Line 1
  5. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLWorkbook".
  6.  

Workgroup information file? that is generally the system MDW for an access database. Are you sure you are referring to an excel spreadsheet in your syntax?

Jim :)
Nov 20 '07 #4

priyan
P: 54
Workgroup information file? that is generally the system MDW for an access database. Are you sure you are referring to an excel spreadsheet in your syntax?

Jim :)

hi Jim
I am sure i am using excel file only i have to import data from excel file.....please suggest me one solution as soon as possible..........
Nov 21 '07 #5

Jim Doherty
Expert 100+
P: 897
hi Jim
I am sure i am using excel file only i have to import data from excel file.....please suggest me one solution as soon as possible..........
Have you tried the Import wizard using SQL Management Studio?

Select your database node....right click....import/export (you click import) and then following the on screen prompts and the wizardry logic of it all select an excel file from a given path and import it into a table that way.

(I have no idea of what you have your end configuration wise are you on a network? do you have control over the server, do you have system admin permissions etc. etc. .... my earlier suggestions whilst valid obviously are not working for you.... rather than fault find per se on these alone try my last suggestion. I am researching my earlier posts myself now, simply to see for myself what a 'workgroup file' error message has to do at ALL with an EXCEL file because that relates to an Access database system)

Jim :)
Nov 21 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.