How to Import data from Excel to Table and Export data table to excel

Question

Post reply

How to Import data from Excel to Table and Export data table to excel

chandrasekaran.ganapathy

SSCommitted

Points: 1738
More actions
April 5, 2010 at 12:43 am

#139326

Hi,
How to Import data from Excel to Table and Export data table to excel
I am having the table userinformation. I just wanted insert the data from the excel sheet.
Like wise
I am having the table userprivilege. I just wanted export the data from this table to excel.
Can I check directly in the SQL server. Will it work? or I need to write the code in javascript and should execute the file.
Please give me full details. As I am new to this.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 1

Hi I find out how to import from table to excel.

Please any let me know the process to import from excel to table.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

1.You can use the SQL Server integration services ( sql 2005 ) or DTS ( sql 2000) to import Excel data into SQL Server tables.

2.Use Distributed QueriesIf you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

3.Use a Linked Server

To simplify queries, you can configure an Excel workbook as a linked server in SQL Server.

The following code imports the data from the Customers worksheet on the Excel linked server "EXCELLINK" into a new SQL Server table named XLImport1:

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]

You can also execute the query against the source in a passthrough manner by using OPENQUERY as follows:

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,

'SELECT * FROM [Customers$]')

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 3

hi,

Right click on the Database. Go to "Tasks".Select Import data option.

SQL Server Import Export wizrd screen will welcomes you.

1.Click Next

2.Choose Datasource to "Microsoft Excel" and specify path where it existed.

3.Choose Destination Select "SQL Native Client"

Use SQL or windows Authentication.

Select Corresponding Database.

Choose option [ for system generation select 1 , for user specific select query option]

4. Click next if source & destination matched or select as per ur requirement by clicking edit button.

5. Click Finish>>| button.

The same can be used for Export also.

but the Datasource is SQL Native client & destination is Microsoft Excel.

🙂

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 4

I am getting this error.

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Table1$". The table either does not exist or the current user does not have permissions on that table.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

chandrasekaran.ganapathy (4/5/2010)
I am getting this error.
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Table1$". The table either does not exist or the current user does not have permissions on that table.

BOL helps :

To create a linked server against an Excel spreadsheet:

The Microsoft OLE DB Provider for Jet 4.0 can be used to access Microsoft Excel spreadsheets.

To create a linked server that accesses an Excel spreadsheet, use the format of this example.

sp_addlinkedserver N'Excel', N'Jet 4.0',

N'Microsoft.Jet.OLEDB.4.0',

N'c:\data\MySheet.xls', NULL, N'Excel 5.0'

GO

sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL

GO

To access data from an Excel spreadsheet, associate a range of cells with a name. A named range can be accessed by using the name of the range as the table name. The following query can be used to access a named range called SalesData using the linked server set up in the previous example.

SELECT *

FROM EXCEL...SalesData

GO

When you insert a row into a named range of cells, the row will be added after the last row that is part of the named range of cells. Thus, if you want to insert row rA after the column heading, associate the column heading cells with a name and use that name as the table name. The range of cells will grow automatically as rows are inserted.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

refer these links also

http://sql-server-performance.com/Community/forums/t/10004.aspx

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/77b86d4e-5322-4611-97f6-49e5e2013ee9

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 7

My Excel is in the location: C:\testing.xls

I want to insert in the table: Table1

Database Name: WADB

Is this the following changes are correct?

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\testing.xls;Extended Properties=Excel 8.0')...[Table1$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\testing.xls', [Table1$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\testing.xls', 'SELECT * FROM [Table1$]')

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

have you tried them ? did you get any error ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 9

Msg 7314, Level 16, State 1, Line 4

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Table1$". The table either does not exist or the current user does not have permissions on that table.

Same error

subbu_e SSCrazy Points: 2431 More actions · Answer 10

here the table name should be the Workbook name of your excell file. make sure that you are using the workbook name as [TAble1].

Thanks

Regards,
Subbu
Click here to Get Speedy answer or solution

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 11

chandrasekaran.ganapathy (4/6/2010)
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\testing.xls;Extended Properties=Excel 8.0')...[Table1$]

See this is happening because you have placed your excel in C: drive "C:\testing.xls" , put it in folder and then try to do that.

MS dont allow to do these things when you have Excel in C: drive. i think this will resolve the issue.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

--Excel 2003

SELECT [Folder names],[Names] FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=D:\Office\RFP tables outside of RFP.xls;Extended Properties=Excel 8.0')...[sheet$]

--Excel 2007

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',

'Data Source=D:\Office\RFP.xls;Extended Properties=Excel 8.0')...[sheet1$]

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 13

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

Hi Here Customers$ refers the table name?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 14

Yes, Customer is table

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)