vb.net用额外的列将csv导入数据库 [英] vb.net import csv into database with extra column

问题描述

下面是我的代码，将.csv文件导入sql服务器数据库.虽然从.csv文件导入的记录没有问题，但是在导入时需要添加额外的列.即使我已经硬编码了额外列的值(MetNo)，但导入后该字段还是空的.我在这里做错了什么?MetNo列不是.csv文件的一部分，需要添加为标签值

Below is my code to import a .csv file into sql server database. While the records from the .csv file import without issues, there is a need to add an extra column while importing. Even though I have hard coded the value for the extra column (MetNo), the field is empty after importing. What am I doing wrong here? The MetNo column is not a part of the .csv file and needs to be added as a label value

        Private Sub ReadAndInsertCSVData()
        Dim table As New DataTable()
        table.Columns.Add("ColumnA")
        table.Columns.Add("ColumnB")
        table.Columns.Add("ColumnC")
        table.Columns.Add("ColumnD")
        table.Columns.Add("ColumnE")
        table.Columns.Add("ColumnF")
        table.Columns.Add("ColumnG")
        table.Columns.Add("ColumnH")
        table.Columns.Add("ColumnI")
        table.Columns.Add("ColumnJ")
        table.Columns.Add("ColumnK")
        table.Columns.Add("ColumnL")
        table.Columns.Add("ColumnM")
        table.Columns.Add("ColumnN")
        table.Columns.Add("ColumnO")
        table.Columns.Add("ColumnP")
        table.Columns.Add("ColumnQ")
        table.Columns.Add("ColumnR")
        table.Columns.Add("ColumnS")
        table.Columns.Add("ColumnT")
        table.Columns.Add("ColumnU")
        table.Columns.Add("ColumnV")
        table.Columns.Add("ColumnW")
        table.Columns.Add("MetNo")
        Dim parser As New FileIO.TextFieldParser("C:\S07_180004.CSV")
        parser.Delimiters = New String() {","}
        parser.HasFieldsEnclosedInQuotes = True
        parser.TrimWhiteSpace = True
        Do Until parser.EndOfData = True
            table.Rows.Add(parser.ReadFields())
        Loop
        Dim strSql As String = "INSERT INTO dbo.MetData " _
                & "(ColumnA, ColumnB, ColumnC, ColumnD, ColumnE, ColumnF, ColumnG, ColumnH, ColumnI, ColumnJ, ColumnK, ColumnL, ColumnM, ColumnN, ColumnO, " _
                & "ColumnP, ColumnQ, ColumnR, ColumnS, ColumnT, ColumnU, ColumnV, ColumnW, MetNo) VALUES (@ColumnA, @ColumnB, @ColumnC, " _
                & "@ColumnD, @ColumnE, @ColumnF, @ColumnG, @ColumnH, @ColumnI, @ColumnJ, @ColumnK, @ColumnL, @ColumnM, @ColumnN, @ColumnO, @ColumnP, " _
                & "@ColumnQ, @ColumnR, @ColumnS, @ColumnT, @ColumnU, @ColumnV,  @ColumnW, @MetNo)"
        Dim SqlconnectionString As String = "Data Source=ServerAddress;Initial Catalog=CatalogName;Integrated Security=SSPI;"
        Using connection As New SqlClient.SqlConnection(SqlconnectionString)
            Dim cmd As New SqlClient.SqlCommand(strSql, connection)
With cmd.Parameters
                cmd.Parameters.Add("@ColumnA", SqlDbType.Int, 4, "ColumnA")
                cmd.Parameters.Add("@ColumnB", SqlDbType.DateTime, 10, "ColumnB")
                cmd.Parameters.Add("@ColumnC", SqlDbType.Time, 8, "ColumnC")
                cmd.Parameters.Add("@ColumnD", SqlDbType.VarChar, 50, "ColumnD")
                cmd.Parameters.Add("@ColumnE", SqlDbType.VarChar, 50, "ColumnE")
                cmd.Parameters.Add("@ColumnF", SqlDbType.Decimal, 28, "ColumnF")
                cmd.Parameters("@ColumnF").Precision = 28
                cmd.Parameters("@ColumnF").Scale = 10
                cmd.Parameters.Add("@ColumnG", SqlDbType.VarChar, 50, "ColumnG")
                cmd.Parameters.Add("@ColumnH", SqlDbType.VarChar, 50, "ColumnH")
                cmd.Parameters.Add("@ColumnI", SqlDbType.Decimal, 28, "ColumnI")
                cmd.Parameters("@ColumnI").Precision = 28
                cmd.Parameters("@ColumnI").Scale = 10
                cmd.Parameters.Add("@ColumnJ", SqlDbType.VarChar, 50, "ColumnJ")
                cmd.Parameters.Add("@ColumnK", SqlDbType.Decimal, 28, "ColumnK")
                cmd.Parameters("@ColumnK").Precision = 28
                cmd.Parameters("@ColumnK").Scale = 10
                cmd.Parameters.Add("@ColumnL", SqlDbType.VarChar, 50, "ColumnL")
                cmd.Parameters.Add("@ColumnM", SqlDbType.Int, 10, "ColumnM")
                cmd.Parameters.Add("@ColumnN", SqlDbType.VarChar, 50, "ColumnN")
                cmd.Parameters.Add("@ColumnO", SqlDbType.Int, 4, "ColumnO")
                cmd.Parameters.Add("@ColumnP", SqlDbType.VarChar, 50, "ColumnP")
                cmd.Parameters.Add("@ColumnQ", SqlDbType.Int, 4, "ColumnQ")
                cmd.Parameters.Add("@ColumnR", SqlDbType.VarChar, 50, "ColumnR")
                cmd.Parameters.Add("@ColumnS", SqlDbType.Int, 4, "ColumnS")
                cmd.Parameters.Add("@ColumnT", SqlDbType.VarChar, 50, "ColumnT")
                cmd.Parameters.Add("@ColumnU", SqlDbType.Int, 4, "ColumnU")
                cmd.Parameters.Add("@ColumnV", SqlDbType.VarChar, 50, "ColumnV")
                cmd.Parameters.Add("@ColumnW", SqlDbType.Int, 4, "ColumnW")
                cmd.Parameters.Add("@MetNo", SqlDbType.VarChar, 6, "MetNo")
                cmd.Parameters("@MetNo").Value = "087826"
            End With
            Dim adapter As New SqlClient.SqlDataAdapter()
            adapter.InsertCommand = cmd
            Dim intRowsAdded As Int32 = adapter.Update(table)
        End Using
    End Sub

推荐答案

用parser.ReadFields()方法填充表格后，您需要将数据添加到新列中

After filling the table with parser.ReadFields() method you need to add the data to the new column

        For Each row As DataRow In table.Rows
            row("MetNo") = "new data"
        Next
​

这篇关于vb.net用额外的列将csv导入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！