excel数据非常大而且文件很多,
问题一、直接通过ADO读取excel插入SQL的话,但是有些行的单元格明明是有值的,但读取却是空值Null. 有什么办法解决?
问题二:还有一种办法就是使用BULK INSERT语句
现在的问题是如何用程序自动将EXCEL快速转换为文本(这样可以保证不为NULL),再使用BULK INSERT语句批量插入SQL。
问题一、直接通过ADO读取excel插入SQL的话,但是有些行的单元格明明是有值的,但读取却是空值Null. 有什么办法解决?
问题二:还有一种办法就是使用BULK INSERT语句
现在的问题是如何用程序自动将EXCEL快速转换为文本(这样可以保证不为NULL),再使用BULK INSERT语句批量插入SQL。
’x:\A.mdb’;’admin’;’’,A表)
这个语句以前用过,我机器上用的很好,我觉得这可能跟版本有一定关系
StrSql := 'SELECT * into hh6 FROM [sheet1$] IN "D:\hh\练习文件夹\整理\数据库相关\access数据库中插入一条数据排在制定数据后面\book1.xls" "EXCEL 5.0;"';
hehe
insert into B表
Select *
From OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$或者用sqlserver的DTS工具
Private Sub excel_Click()
On Error GoTo err
If MsgBox("絋﹚惠璶旧计沮?", vbQuestion + vbYesNo, "矗ボ癸杠よ遏") = vbYes Then
Dim temp As String
Dim yanglao As String
Dim yiliao As String
Dim xlrow As Integer
Dim i As Integer
Dim cnn As New ADODB.Connection
xlrow = 2
Screen.MousePointer = 11
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set xlWork = xlApp.Workbooks.Open(App.Path & "\把玂ゅン.xls")
Set xlSheet = xlWork.Sheets(1)
If xlSheet.Range("A" & 2).Select = "" Then
Exit Sub
Else
If cnn.State = adStateOpen Then cnn.Close
cnn.CursorLocation = adUseClient
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pay;Data Source=datasqlserver"
cnn.Execute "delete from dbo.shebao_temp"
frmmessage.Show
For i = 2 To 20000
xlSheet.Range("A" & xlrow).Select
Label1.Caption = xlApp.ActiveCell.FormulaR1C1
temp = Mid(Label1.Caption, 2, Len(Label1.Caption))
xlSheet.Range("B" & xlrow).Select
Label2.Caption = xlApp.ActiveCell.FormulaR1C1
yanglao = Label2.Caption
xlSheet.Range("C" & xlrow).Select
yiliao = xlApp.ActiveCell.FormulaR1C1
If temp = "" Then
Exit For
End If
cnn.Execute "INSERT INTO dbo.shebao_temp(personal_no, yanglao,yiliao) VALUES ('" & temp & "', '" & yanglao & "','" & yiliao & "')"
xlrow = xlrow + 1
Next i
Screen.MousePointer = 1
Unload frmmessage
MsgBox "旧计沮Θ", vbInformation SQL.Enabled = True
sql_l.Enabled = True
sql_r.Enabled = True
sql_e.Enabled = True
cnn.Close
Set cnn = Nothing
End If
Set xlSheet = Nothing
xlWork.Close
Set xlWork = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub
End If
err:
MsgBox "Error!"
Unload Me
Unload frmmessage
End Sub
可以参考以下代码:ADOQuery1.SQL.Clear;
if Trim(MyWorkBook.WorkSheets[1].Cells[i,1])<>'' then do
begin
ADOQuery1.SQL.Add('insert into B(BID,Bcaption,DFF,TF)');
ADOQuery1.SQL.Add('values('''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,1]))+''','''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,2]))+''','''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,5]))+''',cast('''+uppercase(Trim(MyWorkBook.WorkSheets[1].Cells[i,6]))+''' as int))');
ADOQuery1.ExecSQL;
i:=i+1;
end