利用VBA创建ACCESS数据库并导入CSV数据

方法一:利用VBA创建ACCESS数据库

Sub CR_DB()
   Dim AC As Object   
   Set AC = CreateObject("ACCESS.APPLICATION")    
    Db = ThisWorkbook.Path & "\test.accdb"
    If Dir(Db) <> "" Then
        Kill Db       
    End If
    
    With AC
         .NEWCURRENTDATABASE Db
         .CloseCurrentDatabase
    End With
    Set AC = Nothing
    
  方法二:利用VBA导入CSV数据到ACCESS数据库
  
    Dim cnn As Object
    Dim myPath$, MyFile$, Sql$, s$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb" '连接数据库
   
    myPath = ThisWorkbook.Path & "\"
    MyFile = Dir(myPath & "*.CSV")
   While MyFile <> ""
        s = "[" & MyFile & "]" & vbCrLf & "ColNameHeader = TRUE" & vbCrLf & "Format = CSVDelimited" & vbCrLf & "MaxScanRows=0"
 ‘’‘若表已经存在,如TEST,则直接导入如下:
         Sql = " INSERT INTO TEST SELECT DATE_ID,EUTRANCELLTDD AS CELL,InterferencePwrPusch AS PUSCH干扰,InterferencePwrPucch AS PUCCH干扰  FROM [TEXT;HDR=NO;FMT=Delimited;DATABASE=" & myPath & ";].[" & MyFile & "];"
’‘’若表不存在则先创建,TEST表,如下:
         Sql = " SELECT DATE_ID,EUTRANCELLTDD AS CELL,InterferencePwrPusch AS PUSCH干扰,InterferencePwrPucch AS PUCCH干扰 INTO TEST  FROM [TEXT;HDR=NO;FMT=Delimited;DATABASE=" & myPath & ";].[" & MyFile & "];"

        Open myPath & "schema.ini" For Output As #1
        Print #1, s
        Close #1
        cnn.Execute Sql
        MyFile = Dir()
     Wend   
    
    cnn.Close
    Set cnn = Nothing
    Kill ThisWorkbook.Path & "\schema.ini"


      
End Sub

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

qq_44390640

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值