excel如果是一个有规则的数据,可以把期当作数据源导入 select * into Table FROM openrowset( 'Microsoft.Jet.OLEDB.4.0', 'EXCEL 5.0;HDR=YES;IMEX=1; DATABASE=yourPath',Sheet1$) 如果不规则的,就需要按实际的需求一个个写了.可以随便找个VB如何使用Excel的例子来看看,就行了
首先是EXCEL的格式与数据库的表的格式一样,然后选择EXCEL,IMPORT去SQL Private Sub cmdImport_Click() Dim sExcelPath As String Dim sExcelCustomerNumber As String Dim sExcelID As String Dim sExcelCustomerName As String Dim iExcelCompanyName As String Dim cExcelOpenDate As Date Dim cExcelLastDate As Date Dim cExcelBirthday As Date Dim cExcelExpiryDate As Date Dim sExcelAddress As String Dim sExcelPostalCode As String Dim sExcelCity As String Dim sExcelStateOrProvince As String Dim sExcelCountry As String Dim sExcelTel1 As String Dim sExcelTel2 As String Dim sExcelMobile As StringDim iExcelX As Integer Dim iExcelY As IntegerDim xlsApplication As Object Dim xlsWorkBook As Object Dim xlsWorkSheet As ObjectOn Error GoTo FileError If txtExcelPath.Text = "" Then Exit Sub sExcelPath = txtExcelPath.Text
Set xlsApplication = CreateObject("Excel.Application") Set xlsWorkBook = xlsApplication.Workbooks.Open(sExcelPath) Set xlsWorkSheet = xlsWorkBook.Worksheets(1)
If sExcelCustomerNumber = "" Then Exit Do If CheckCustomerNumber(sExcelCustomerNumber) Then If CheckID(sExcelID) Then 'Insert SaveCustomerProc sExcelCustomerNumber, sExcelID, sExcelCustomerName, iExcelCompanyName, cExcelOpenDate, cExcelLastDate, cExcelBirthday, cExcelExpiryDate, sExcelAddress, sExcelPostalCode, sExcelCity, sExcelStateOrProvince, sExcelCountry, sExcelTel1, sExcelTel2, sExcelMobile End If Else If CheckID(sExcelID) Then 'Update UpdateCustomerProc sExcelCustomerNumber, sExcelID, sExcelCustomerName, iExcelCompanyName, cExcelOpenDate, cExcelLastDate, cExcelBirthday, cExcelExpiryDate, sExcelAddress, sExcelPostalCode, sExcelCity, sExcelStateOrProvince, sExcelCountry, sExcelTel1, sExcelTel2, sExcelMobile End If End If
iExcelY = iExcelY + 1 Loop
xlsWorkBook.Close xlsApplication.Quit
MsgBox "Import date successfully!" Exit Sub FileError: xlsWorkBook.Close xlsApplication.Quit MsgBox "File Error!" End Sub
INSERT INTO t1 SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'EXCEL 8.0;HDR=NO;IMEX=1; DATABASE=c:\book1.xls', Sheet1$) Rowset_1UPDATE b SET b.fname = a.f2, b.fstatus = a.f3 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'EXCEL 8.0;HDR=NO;IMEX=1; DATABASE=c:\book1.xls', Sheet1$) AS a INNER JOIN t1 AS b ON a.f1 = b.fnumber WHERE b.fstatus = 1234以上是最简单的办法,以上这此SQL语句可以在VB的ADO中使用(能不能看懂,全凭你自己的造化)。另一种方法就是建立excel对象,并读出数据,然后用ADO读出SQL数据库的数据,然后逐字段赋值,最后再更新记录,这是比较笨的做法。
运行后为何会提示:"OLE DB 提供程序 MICROSOFT.JET.OLEDB.4.0" 报错?
select * into Table FROM openrowset( 'Microsoft.Jet.OLEDB.4.0',
'EXCEL 5.0;HDR=YES;IMEX=1; DATABASE=yourPath',Sheet1$)
如果不规则的,就需要按实际的需求一个个写了.可以随便找个VB如何使用Excel的例子来看看,就行了
Private Sub cmdImport_Click()
Dim sExcelPath As String
Dim sExcelCustomerNumber As String
Dim sExcelID As String
Dim sExcelCustomerName As String
Dim iExcelCompanyName As String
Dim cExcelOpenDate As Date
Dim cExcelLastDate As Date
Dim cExcelBirthday As Date
Dim cExcelExpiryDate As Date
Dim sExcelAddress As String
Dim sExcelPostalCode As String
Dim sExcelCity As String
Dim sExcelStateOrProvince As String
Dim sExcelCountry As String
Dim sExcelTel1 As String
Dim sExcelTel2 As String
Dim sExcelMobile As StringDim iExcelX As Integer
Dim iExcelY As IntegerDim xlsApplication As Object
Dim xlsWorkBook As Object
Dim xlsWorkSheet As ObjectOn Error GoTo FileError If txtExcelPath.Text = "" Then Exit Sub
sExcelPath = txtExcelPath.Text
Set xlsApplication = CreateObject("Excel.Application")
Set xlsWorkBook = xlsApplication.Workbooks.Open(sExcelPath)
Set xlsWorkSheet = xlsWorkBook.Worksheets(1)
iExcelY = 2
sExcelCustomerNumber = xlsWorkSheet.Cells(iExcelY, 1).Value
Do While sExcelCustomerNumber <> ""
sExcelCustomerNumber = xlsWorkSheet.Cells(iExcelY, 1).Value
sExcelID = xlsWorkSheet.Cells(iExcelY, 2).Value
sExcelCustomerName = xlsWorkSheet.Cells(iExcelY, 3).Value
iExcelCompanyName = xlsWorkSheet.Cells(iExcelY, 4).Value
cExcelOpenDate = xlsWorkSheet.Cells(iExcelY, 5).Value
cExcelLastDate = xlsWorkSheet.Cells(iExcelY, 6).Value
cExcelBirthday = xlsWorkSheet.Cells(iExcelY, 7).Value
cExcelExpiryDate = xlsWorkSheet.Cells(iExcelY, 8).Value
sExcelAddress = xlsWorkSheet.Cells(iExcelY, 9).Value
sExcelPostalCode = xlsWorkSheet.Cells(iExcelY, 10).Value
sExcelCity = xlsWorkSheet.Cells(iExcelY, 11).Value
sExcelStateOrProvince = xlsWorkSheet.Cells(iExcelY, 12).Value
sExcelCountry = xlsWorkSheet.Cells(iExcelY, 13).Value
sExcelTel1 = xlsWorkSheet.Cells(iExcelY, 14).Value
sExcelTel2 = xlsWorkSheet.Cells(iExcelY, 15).Value
sExcelMobile = xlsWorkSheet.Cells(iExcelY, 17).Value
If sExcelCustomerNumber = "" Then Exit Do
If CheckCustomerNumber(sExcelCustomerNumber) Then
If CheckID(sExcelID) Then
'Insert
SaveCustomerProc sExcelCustomerNumber, sExcelID, sExcelCustomerName, iExcelCompanyName, cExcelOpenDate, cExcelLastDate, cExcelBirthday, cExcelExpiryDate, sExcelAddress, sExcelPostalCode, sExcelCity, sExcelStateOrProvince, sExcelCountry, sExcelTel1, sExcelTel2, sExcelMobile
End If
Else
If CheckID(sExcelID) Then
'Update
UpdateCustomerProc sExcelCustomerNumber, sExcelID, sExcelCustomerName, iExcelCompanyName, cExcelOpenDate, cExcelLastDate, cExcelBirthday, cExcelExpiryDate, sExcelAddress, sExcelPostalCode, sExcelCity, sExcelStateOrProvince, sExcelCountry, sExcelTel1, sExcelTel2, sExcelMobile
End If
End If
iExcelY = iExcelY + 1
Loop
xlsWorkBook.Close
xlsApplication.Quit
MsgBox "Import date successfully!"
Exit Sub
FileError: xlsWorkBook.Close
xlsApplication.Quit
MsgBox "File Error!"
End Sub
INSERT INTO t1
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'EXCEL 8.0;HDR=NO;IMEX=1; DATABASE=c:\book1.xls', Sheet1$) Rowset_1UPDATE b
SET b.fname = a.f2, b.fstatus = a.f3
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'EXCEL 8.0;HDR=NO;IMEX=1; DATABASE=c:\book1.xls', Sheet1$) AS a INNER JOIN
t1 AS b ON a.f1 = b.fnumber
WHERE b.fstatus = 1234以上是最简单的办法,以上这此SQL语句可以在VB的ADO中使用(能不能看懂,全凭你自己的造化)。另一种方法就是建立excel对象,并读出数据,然后用ADO读出SQL数据库的数据,然后逐字段赋值,最后再更新记录,这是比较笨的做法。
1. 是否引用excel对象? office 2003 工程中引用 Microsoft office 11.0 Object;
2. excel文件是否含有Sheet1工作博?
3. 确认被导入的表已经存在?