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

[priyan]

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.

[Jim Doherty]

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

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

SELECT * INTO test.dbo.mytable 
FROM XLWorkbook...[Sheet1$]

will take that data and place into a database called 'test' having a physical table called 'mytable'

or

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.x ls located at the mentioned filepath and from Sheet1$ of that workbook

 
INSERT INTO dbo.MyTable 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\Documents and Settings\MyName\Desktop\myspreadsheet.xls', [Sheet1$])

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 :)

[priyan]

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

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

SELECT * INTO test.dbo.mytable 
FROM XLWorkbook...[Sheet1$]

will take that data and place into a database called 'test' having a physical table called 'mytable'

or

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.x ls located at the mentioned filepath and from Sheet1$ of that workbook

 
INSERT INTO dbo.MyTable 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\Documents and Settings\MyName\Desktop\myspreadsheet.xls', [Sheet1$])

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

 
INSERT INTO dbo.MyTable 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\Documents and Settings\MyName\Desktop\myspreadsheet.xls', [Sheet1$])

my error is
[code=text]
Msg 15281, Level 16, State 1, Line 1

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.
[/code]
and for the second command

EXEC sp_addlinkedserver 'XLWorkbook', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', '\\MyServer\MyShare\MySpreadsheets\workbook.xls', NULL, 'Excel 8.0'

SELECT * INTO test.dbo.mytable 
FROM XLWorkbook...[Sheet1$]

my error is
[code=text]
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.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet. OLEDB.4.0" for linked server "XLWorkbook " reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet. OLEDB.4.0" for linked server "XLWorkbook ".
[/code]

[Jim Doherty]

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

 
INSERT INTO dbo.MyTable 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\Documents and Settings\MyName\Desktop\myspreadsheet.xls', [Sheet1$])

my error is
[code=text]
Msg 15281, Level 16, State 1, Line 1

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.
[/code]
and for the second command

EXEC sp_addlinkedserver 'XLWorkbook', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', '\\MyServer\MyShare\MySpreadsheets\workbook.xls', NULL, 'Excel 8.0'

SELECT * INTO test.dbo.mytable 
FROM XLWorkbook...[Sheet1$]

my error is
[code=text]
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.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet. OLEDB.4.0" for linked server "XLWorkbook " reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet. OLEDB.4.0" for linked server "XLWorkbook ".
[/code]

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 :)

[priyan]

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....... ...

[Jim Doherty]

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 :)