excel file

[airesh]

How to import an Excel file into SQL Server 2005..

anyone?

[lolafuertes]

Do you want to save the file inside the SQL server? or do you want to open the file, read the first sheet and copy the content to an existing table in a SQL server database?

Please can you be so kind to clarify?

Thanks in advance

[sandeepparekh9]

Use Import/Export Wizard in sql 2005

[airesh]

Do you want to save the file inside the SQL server? or do you want to open the file, read the first sheet and copy the content to an existing table in a SQL server database?

Please can you be so kind to clarify?

Thanks in advance

yes..i want to open the file, read the first sheet and copy the content to an existing table in a SQL server database

u have any code for that?

thanks a lot

[sandeepparekh9]

http://support.microsoft.com/?kbid=321686

http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx

[airesh]

http://support.microsoft.com/?kbid=321686

http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx

do you have any sample code?

[sandeepparekh9]

//file upload path
string path = "c:\demo.xls"
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();

sorry it's in c#

[sandeepparekh9]

here in vb.net

'file path
Dim path As String = "c:\demo.xls"
'Create connection string to Excel work book
Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;Persist Security Info=False"
'Create Connection to Excel work book
Dim excelConnection As New OleDbConnection(excelConnectionString)
'Create OleDbCommand to fetch data from Excel
Dim cmd As New OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection)
excelConnection.Open()
Dim dReader As OleDbDataReader
dReader = cmd.ExecuteReader()
Dim sqlBulk As New SqlBulkCopy(strConnection)
'Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table"
sqlBulk.WriteToServer(dReader)
excelConnection.Close()