首页 文章

如何使用vba按列名将excel excel导入数据库

提问于
浏览
0

有没有人知道使用vba按列名将excel数据导入sql数据库的方法?

例如:我有一个包含5列的excel文件:column1,column2,column3,column 4,column 5.这些列的位置是随机生成的我只想导入3列数据:column1,column3,column5 in sql database使用vba

我浪费了很多天在谷歌搜索,但无法找到解决方案 . 有人请帮我解决这个问题 .

谢谢!

3 回答

  • -1

    您可以使用ADO连接 .

    您可以打开与目标服务器的连接,循环数据,然后将其插入目标数据库 . 这可以帮助您入门:http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/Database_Beginner_ADO_DAO.asp

    否则,根据您的目标数据库,它可能有一些专有的导入工具 - 这通常要求您将excel导出为它识别的某种格式 .

    对于完整的解决方案,您可以考虑使用ETL工具

  • 0

    因此,您将从变量名称中看到这是一个莲花文件但是,我重用它为csv . 因此excel文件的结构将是相同的 . 如果你想将它保存为CSV和快乐的日子 . 我知道已经6年了,所以我想你不再需要这个了,但可能会帮助其他遇到这个问题的人 .

    Dim LotusCn As Object
        Dim rsLotus As Object
        Dim strSql, CombFileName, GotoRange As String
        Dim rsLotusFiles As DAO.Recordset
        Dim rsDAO1 As DAO.Recordset
        Dim strName1 As String
    
        Set LotusCn = CreateObject("ADODB.Connection")
        Set rsLotus = CreateObject("ADODB.Recordset")
    
        strSql = "Provider=" & _ 'This is where the file is located
        CurrentProject.Connection.Provider & _
        ";Data Source=" & Directory & _
        ";Extended Properties='text;HDR=YES;FMT=Delimited'"
    
        LotusCn.Open strSql
        Dim fld1 As ADODB.Field
    
        strSql = "SELECT * FROM NUMBDATM.CSV" 'This is the file name and please open it
        rsLotus.Open strSql, LotusCn, adOpenFowardOnly, adLockReadOnly, adCmdText
    
    
        Set rsDAO1 = CurrentDb.OpenRecordset("NUMBDATM", _ 'This here is the table you 
        dbOpenTable, dbAppendOnly + dbFailOnError)          want to import into 
    
        Do Until rsLotus.EOF 'Here tell it what values you want from the excel Sheet
              RegNumber = rsLotus![Reg# Number]
              CompanyName = rsLotus![Company Name]
              SalesGrowth1 = rsLotus![Sales Growth % 1 ]
              FixedAssets1 = rsLotus![Fixed Assets 1 ]
              PeriodEnding1 = rsLotus![Period Ending 1 ]
              TotalSales1 = rsLotus![Total Sales 1 ]
              SalesGrowth2 = rsLotus![Sales Growth % 2 ]
              SalesGrowth3 = rsLotus![Sales Growth % 3 ]
              PreTaxProfit3 = rsLotus![Pretax Profit 3 ]
              PreTaxProfit2 = rsLotus![Pretax Profit 2 ]
              PreTaxProfit1 = rsLotus![Pretax Profit 1 ]
              PrProfitMarg = rsLotus![Pr#Profit Margin  % 1 ]
              Week1 = rsLotus![Weeks 1 ]
              Week2 = rsLotus![Weeks 2 ]
              Week3 = rsLotus![Weeks 3 ]
            rsDAO1.AddNew 'Here please add the values from above into the relevant table
                rsDAO1![Reg# Number] = RegNumber
                rsDAO1![Company Name] = CompanyName
                rsDAO1![Sales Growth % 1 ] = SalesGrowth1
                rsDAO1![Fixed Assets 1 ] = FixedAssets1
                rsDAO1![Period Ending 1 ] = PeriodEnding1
                rsDAO1![Total Sales 1] = TotalSales1
                rsDAO1![Sales Growth % 2 ] = SalesGrowth2
                rsDAO1![Sales Growth % 3 ] = SalesGrowth3
                rsDAO1![Pretax Profit 3 ] = PreTaxProfit3
                rsDAO1![Pretax Profit 2 ] = PreTaxProfit2
                rsDAO1![Pretax Profit 1 ] = PreTaxProfit1
                rsDAO1![Pr#Profit Margin  % 1 ] = PrProfitMarg
                rsDAO1![Weeks 1 ] = Week1
                rsDAO1![Weeks 2 ] = Week2
                rsDAO1![Weeks 3 ] = Week3
            rsDAO1.Update
            rsLotus.MoveNext
        Loop
        rsDAO1.Close
        Set rsDAO1 = Nothing
        rsLotus.Close
        Set rsLotus = Nothing
        LotusCn.Close
    
  • 0
    Protected Sub just_Click(sender As Object, e As EventArgs) Handles just.Click
    
    
    Dim cnn As SqlConnection
    
    Dim sql As String
    
    Dim i, j As Integer
    
    Dim xlApp As Excel.Application
    
    Dim xlWorkBook As Excel.Workbook
    
    Dim xlWorkSheet As Excel.Worksheet
    
    Dim misValue As Object = System.Reflection.Missing.Value
    
    xlApp = New Excel.ApplicationClass
    
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    
    xlWorkSheet = xlWorkBook.Sheets("sheet1")
    
    cnn = New SqlConnection("***your connection string***")
    
    cnn.Open()
    
    sql = "SELECT * FROM exceltable"
    
    Dim dscmd As New SqlDataAdapter(sql, cnn)
    
    Dim ds As New DataSet
    
    dscmd.Fill(ds)
    
    For j = 0 To ds.Tables(0).Columns.Count - 1
    
    xlWorkSheet.Cells(i + 1, j + 1) = _
    
    ds.Tables(0).Columns(j).ColumnName
    
    Next
    
    For i = 0 To ds.Tables(0).Rows.Count - 1
    
    
    For j = 0 To ds.Tables(0).Columns.Count - 1
    
    xlWorkSheet.Cells(i + 2, j + 1) = _
    
    ds.Tables(0).Rows(i).Item(j)
    
    
    Next
    
    Next
    
    xlWorkSheet.SaveAs("D:\pappy.xlsx")
    
    xlWorkBook.Close()
    
    xlApp.Quit()
    
    cnn.Close()
    
    End Sub
    

相关问题