如何批量导入CSV文件到ACCESS

Grant

Public Function ReadCSVFile(ByVal strFilePath As String, ByVal strFileName As String)
Dim I As Integer
Dim intCount As Long

'打开数据链接关键
conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
          "DBQ=" & strFilePath & ";Extensions=asc,csv,tab,txtersist Security Info=False"

With rs
    .CursorType = 2
    .LockType = 3
    .CursorLocation = 3
    .ActiveConnection = conn
End With

SQLcsv = "SELECT * FROM [" & strFileName & "]"
rs.Open SQLcsv
rs.Move (1)          '移动到指定记录
intCount = DCount("[ship]", "info", "[ship]='" & rs(16) & "'")      '获取表中记录重复数
If intCount > 1 Then  '进行判断

    Response = MsgBox("是否要覆盖原有［" & rs(16) & "］,点是将更新所有［" & rs(16) & "］数据?", 4 + 32 + 256, "系统提示")
    If Response = vbNo Then   ' 当用户按下“否”。
       rs.Close
        conn.Close
         Set rs = Nothing
           Set conn = Nothing
                  Exit Function
    End If
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * from info where ship='" & rs(16) & "'"           '删除重复数据
    DoCmd.SetWarnings True
  End If
      Set conn1 = CurrentProject.Connection
    sql = "Select * From info "
    rs1.Open sql, conn1, 1, 3
Do Until rs.EOF   '循环更新数据表

   
    rs1.AddNew
    For I = 0 To 26
     rs1(I) = rs(I)
    Next
     
rs.MoveNext
Loop

rs1.UpdateBatch
rs1.Close
conn1.Close
Set rs1 = Nothing
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Function

自写的函数,楼主更改一下便可以批量导入csv文件了

Grant

同理的,我只是把它函数化而已

Grant

请上传你的示例说明文件我给你改改吧.这样也方便别人使用,现在这个函数只是我给一网友临时写的而已.

Grant

时间不是很充裕,只能晚上来弄了见谅

Grant

错了,是小睡一下继续工作.

Grant

Do Until rs.EOF
    Set conn1 = CurrentProject.Connection
    sql = "Select * From tbl出口名细 where [VENDOR NO]='" & rs(0) & "'"
    rs1.Open sql, conn1, 1, 3

If rs1.RecordCount = 1 Then
表中有纪录
Else

rs1.AddNew
    For I = 0 To 7
      rs1(I + 1) = rs(I)
    Next

End If
rs.MoveNext
Loop

Grant

或者可用 Dcount 等方法来判断..

Grant

主要是For I = 0 To 10 原来是 For I = 0 To 7 字段数量不同 而你有几个字段是必填,所以出现这个情况


下次认真看看即可.