Import Data From Excel To Table

[niloofar norouzi]

سلام
من از دستور زیر برای import استفاده می کنم



SELECT *
FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=d:\Customer.xls;HDR=YES'

,

'SELECT * FROM [Sheet1$]'


)

خیلی search کردم تقریبا همه از این روش استفاده می کنند
اما نمی دونم کجای کارم مشکل داره کهerror زیر به وجود می یاد


Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


(sql server 2005)

[AminSobati]

http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

[ariyaei]

اول یک کلاس می سازی:
Public Class Datamanage
''' <summary />
''' Imports the selected Excel file to SQL Server
''' </summary />
Public Sub importToServer(ByVal ExcelPath As String, _
ByVal ServerName As String, _
ByVal DBName As String, ByVal UserName As String, _
ByVal Password As String, ByVal InsertedTableName As String)
Try
Dim ExceCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelPath & "; Extended Properties=Excel 8.0 "
Dim excelConnection As System.Data.OleDb.OleDbConnection = _
New System.Data.OleDb.OleDbConnection(ExceCon)
excelConnection.Open()
' Dim OleStr As String = "SELECT * INTO [ODBC; Driver={SQL Server};Server=Hoss;Database=Excel_Test;Uid=sa;Pwd =sa2008; ].[myTable] FROM [Sheet1$]"
Dim OleStr As String = "SELECT * INTO [ODBC; Driver={SQL Server};Server=" & ServerName & ";Database=" & DBName & ";Uid=" & UserName & ";Pwd=&" & Password & ";" & InsertedTableName & " FROM [Sheet1$]"
Dim excelCommand As New System.Data.OleDb.OleDbCommand(OleStr, excelConnection)
excelCommand.ExecuteNonQuery()
excelConnection.Close()
Catch ex As Exception
Throw New Exception("Error: &" & ex.Message)
End Try
End Sub

[ariyaei]

بعد در فرم از کد زیر را استفاده می کنی:

Dim dm As New Datamanage

'Private Sub Button1_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim ExcelPath As String = TxtInput.Text.ToLower()
Dim Ext As String = _
ExcelPath.Substring(ExcelPath.LastIndexOf(".") + 1)
If File.Exists(ExcelPath) AndAlso (Ext.Equals("xls") _
Or Ext.Equals("xlsx")) Then
dm.importToServer(TxtInput.Text, " txtServer.Text", "txtDbName.Text", " txtusername.Text", "txtpassword.Text", " txtInsertedTableName.Text")
ProgressBar1.Visible = False
MessageBox.Show("File imported successfully")
End If
Catch ex As Exception
ProgressBar1.Visible = False
MessageBox.Show(ex.Message)
End Try
'End Sub

End Sub

[ariyaei]

اینم فرم استفاده شده:


74738

[yaserzare]

سلام
آیا نام ستون ( سطر اول ) یه فایل Excel را میشه تشخیص داد تا کاربر هر کدومش که نیازش داره انتخاب کنه