Private Sub Command1_Click() Dim cn As New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\1.xls;Extended Properties='Excel 8.0;HDR=Yes'"If rs.State = adStateOpen Then rs.Close
rs1.Open "select max(id) from app_ponderation", conn, 1, 1
sql = "SELECT * FROM [Sheet1$]"
Set rs = cn.Execute(sql)
While Not rs.EOF
sql = "insert into app_ponderation(ID,IDcode,Mnumber,Cuscode,Truck,Re,Mdriver,Pdriver,OverFlag,Mweight,Pweight,Krate,Kweight,Convert(smalldatetime,Mtime),Convert(smalldatetime,Ptime),Convert(smalldatetime,BillDate),gradecode,exportcode,addresscode,Casecode) " _
& "values('" & (Val(fixsql(rs(0))) + Val(rs1.Fields(0).value)) & "', " _
& "'" & fixsql(rs(1)) & "' ," _
& "'" & ("00" & fixsql(rs(2))) & "' ," _
& "'" & ("00" & fixsql(rs(3))) & "' ," _
& "'" & fixsql(rs(4)) & "' ," _
& "'" & fixsql(rs(5)) & "' ," _
& "'" & fixsql(rs(6)) & "' ," _
& "'" & fixsql(rs(7)) & "' ," _
& "'" & fixsql(rs(8)) & "' ," _
& "'" & fixsql(rs(9)) & "' ," _
& "'" & fixsql(rs(10)) & "' ," _
& "'" & fixsql(rs(11)) & "' ," _
& "'" & fixsql(rs(12)) & "' ," _
& "'" & fixsql(rs(14)) & "' ," _
& "'" & fixsql(rs(15)) & "' ," _
& "'" & fixsql(rs(16)) & "' ," _
& "'" & ("00" & fixsql(rs(17))) & "' ," _
& "'" & ("00" & fixsql(rs(18))) & "' ," _
& "'" & ("00" & fixsql(rs(19))) & "' ," _
& "'" & ("00" & fixsql(rs(20))) & "')"
conn.Execute (sql)
rs.MoveNext
Wend
conn.Close
Set conn = Nothing
cn.Close
Set cn = Nothing 'addsqldata
End SubFunction fixsql(str)
Dim newstr
newstr = str
If IsNull(newstr) Then
newstr = ""
Else
newstr = Replace(newstr, "'", "''")
End If
fixsql = newstr
End Function我是想把EXCEL里面的的数据一个一个导入到SQL中,但是出现问题,提示转换DATETIME格式时出现问题,因为里面有三个字段的时间格式都是DATETIME格式,怎么把EXCEL中的字付串转换到DATETIME格式
三个时间字段分是PTIME,MTIME,BILLDATE它们的格式分别是2006-1-1 下午 04:40:00,2006-1-1 下午 04:40:00,2006-1-1
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\1.xls;Extended Properties='Excel 8.0;HDR=Yes'"If rs.State = adStateOpen Then rs.Close
rs1.Open "select max(id) from app_ponderation", conn, 1, 1
sql = "SELECT * FROM [Sheet1$]"
Set rs = cn.Execute(sql)
While Not rs.EOF
sql = "insert into app_ponderation(ID,IDcode,Mnumber,Cuscode,Truck,Re,Mdriver,Pdriver,OverFlag,Mweight,Pweight,Krate,Kweight,Convert(smalldatetime,Mtime),Convert(smalldatetime,Ptime),Convert(smalldatetime,BillDate),gradecode,exportcode,addresscode,Casecode) " _
& "values('" & (Val(fixsql(rs(0))) + Val(rs1.Fields(0).value)) & "', " _
& "'" & fixsql(rs(1)) & "' ," _
& "'" & ("00" & fixsql(rs(2))) & "' ," _
& "'" & ("00" & fixsql(rs(3))) & "' ," _
& "'" & fixsql(rs(4)) & "' ," _
& "'" & fixsql(rs(5)) & "' ," _
& "'" & fixsql(rs(6)) & "' ," _
& "'" & fixsql(rs(7)) & "' ," _
& "'" & fixsql(rs(8)) & "' ," _
& "'" & fixsql(rs(9)) & "' ," _
& "'" & fixsql(rs(10)) & "' ," _
& "'" & fixsql(rs(11)) & "' ," _
& "'" & fixsql(rs(12)) & "' ," _
& "'" & fixsql(rs(14)) & "' ," _
& "'" & fixsql(rs(15)) & "' ," _
& "'" & fixsql(rs(16)) & "' ," _
& "'" & ("00" & fixsql(rs(17))) & "' ," _
& "'" & ("00" & fixsql(rs(18))) & "' ," _
& "'" & ("00" & fixsql(rs(19))) & "' ," _
& "'" & ("00" & fixsql(rs(20))) & "')"
conn.Execute (sql)
rs.MoveNext
Wend
conn.Close
Set conn = Nothing
cn.Close
Set cn = Nothing 'addsqldata
End SubFunction fixsql(str)
Dim newstr
newstr = str
If IsNull(newstr) Then
newstr = ""
Else
newstr = Replace(newstr, "'", "''")
End If
fixsql = newstr
End Function我是想把EXCEL里面的的数据一个一个导入到SQL中,但是出现问题,提示转换DATETIME格式时出现问题,因为里面有三个字段的时间格式都是DATETIME格式,怎么把EXCEL中的字付串转换到DATETIME格式
三个时间字段分是PTIME,MTIME,BILLDATE它们的格式分别是2006-1-1 下午 04:40:00,2006-1-1 下午 04:40:00,2006-1-1
select convert(smalldatetime,'2006-1-1 04:40:00'),convert(smalldatetime,'2006-1-1')
依此,可以推断出,还是你的excel数据格式的问题,你的'2006-1-1 04:40:00'中间的空格是中文的还是英文的等等都会有影响的