CODEWITHSUNDEEP
sqlexcelvb.netimport-from-excel
Kai Harmony
Kai Harmony

Reputation: 31

How can I combine two excel columns during import to a database?

I am trying to combine a date and time columns in excel and map it to one column in a database using sqlbulkcopy. I am getting an error:

Date][time does not match any column mapping

See my sample code below. Any ideas on how to accomplish this without copying it to a Datatable?

Dim sSourceConstr As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath)

Dim sDestConstr As String = ConfigurationManager.ConnectionStrings("SolCards").ConnectionString
Dim sSourceConnection As New OleDbConnection(sSourceConstr)
Using sSourceConnection
    Dim sql As String = String.Format("Select [Customer Cod],[Customer],[PAN],[Vehicle],[Date],[Station],[Driver],[Authorized],[Product]" &
                                          ", [Pump], [Tran No], [Odo], [Metric], [UPrice], [Qty], [Amount], [TimeFormat] FROM [{0}$]", "trans")
    Dim command As New OleDbCommand(sql, sSourceConnection)

    sSourceConnection.Open()
    Using dr As OleDbDataReader = command.ExecuteReader()
        Using bulkCopy As New SqlBulkCopy(sDestConstr)
            bulkCopy.DestinationTableName = "FuelInformation"
            'column mapping               

          bulkCopy.ColumnMappings.Add("[Date] [Time]", "DatePurchased")

            bulkCopy.WriteToServer(dr)
        End Using
    End Using
End Using

Upvotes: 0

Views: 258

Answers (3)

Martin H
Martin H

Reputation: 100

The way I got around this was by using excel actual formula of CONCATENATE. You can either put it in your code or in the spreadsheet add a new column and call the value of that cell.

Upvotes: 1

Kai Harmony
Kai Harmony

Reputation: 31

I got it to work by using CStr() as below, the issue with combining the two columns was the column header names having keywords as their names.

 Dim sSourceConstr As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""", sPath)

    Dim sDestConstr As String = ConfigurationManager.ConnectionStrings("SolCards").ConnectionString
    Dim sSourceConnection As New OleDbConnection(sSourceConstr)
    Using sSourceConnection
        Dim sql As String = String.Format("SELECT CStr([Date]) + ' ' + CStr([Time]) as [DatePurchased] ,[Customer Cod],[Customer],[PAN],[Vehicle],[Station],[Driver],[Authorized],[Product], [Pump], [Tran No], [Odo], [Metric], [UPrice], [Qty], [Amount], [TimeFormat] FROM [{0}$]", "trans")
        Dim command As New OleDbCommand(sql, sSourceConnection)

        sSourceConnection.Open()
        Using dr As OleDbDataReader = command.ExecuteReader()
            Using bulkCopy As New SqlBulkCopy(sDestConstr)
                bulkCopy.DestinationTableName = "FuelInformation"
                'column mapping 
                bulkCopy.ColumnMappings.Add("[DatePurchased]", "DatePurchased")

                bulkCopy.WriteToServer(dr)
            End Using
        End Using

    End Using

Upvotes: 0

Aousaf Rashid
Aousaf Rashid

Reputation: 5738

The fix for the error is :

 "Select ([date],[time])values(@date,@time)"
   bulkCopy.ColumnMappings.Add("@Date", "DatePurchased")
   bulkCopy.ColumnMappings.Add("@time", "DatePurchased")

Upvotes: 0

Related Questions