3 Replies - 244 Views - Last Post: 23 May 2019 - 11:23 AM Rate Topic: -----

#1 DarenR   User is offline

  • D.I.C Lover

Reputation: 615
  • View blog
  • Posts: 4,058
  • Joined: 12-January 10

is there a way to add columns to excel dataset

Posted 23 May 2019 - 09:56 AM

ok i have a multi sheet (10+) excel spread sheet that is being uploaded to sql

the sheet must now include a session user id on upload to populate a new userid field in sql

the original sheets can not be touched for they do something else with them

so far the only things i have found is to create tables for 1 sheet and dispose of the original

Im thinking this is probably not possible and i may have to do a table lock and run an update after the info was placed in there

my partial excel get from sheet and mappings

 _OleDbConnection = New OleDbConnection(_excelConString)
            _OleDbConnection.Open()
            Dim _dt As  DataTable  = New DataTable
            _dt = _OleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            Dim _ds as DataSet = new DataSet()
            _dbConstring = ConfigurationManager.AppSettings("strDBConnectionString")
            Dim _sqlBulk As SqlBulkCopy =New SqlBulkCopy(_dbConstring)


            For Each row As System.Data.DataRow In _dt.rows
                 _ds  = new DataSet()
                Dim _sheetName As String = string.empty
                 _sheetName  = row("table_name").tostring()

                _oleDbCommand = new OleDbCommand
                _oleDbCommand.CommandText = _oleExcelSelect & "[" & _sheetName & "]"
                _oleDbCommand.CommandType = CommandType.Text
                _oleDbCommand.Connection = _OleDbConnection
               
                _oleDataAdapter = New OleDbDataAdapter(_oleDbCommand)

                _oleDataAdapter.Fill(_ds,"excelData")
                
                _dtRead = _oleDbCommand.ExecuteReader()
                _sqlBulk.DestinationTableName ="tRouteFrequency"
                _sqlBulk.ColumnMappings.Clear()
                _sqlBulk.ColumnMappings.Add("CustID","CustID")


Is This A Good Question/Topic? 0
  • +

Replies To: is there a way to add columns to excel dataset

#2 DarenR   User is offline

  • D.I.C Lover

Reputation: 615
  • View blog
  • Posts: 4,058
  • Joined: 12-January 10

Re: is there a way to add columns to excel dataset

Posted 23 May 2019 - 10:29 AM

this is the way to add to an excel about to be uploaded

                _oleDataAdapter.Fill(_ds,"excelData")
                _ds.Tables("excelData").Columns.Add("UserID", GetType(string))
                For each _dr As DataRow  in _ds.Tables("excelData").Rows
                        _dr("UserID") = _userID
                Next

Was This Post Helpful? 0
  • +
  • -

#3 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2853
  • View blog
  • Posts: 11,185
  • Joined: 03-December 12

Re: is there a way to add columns to excel dataset

Posted 23 May 2019 - 11:15 AM

To clarify, are you wanting to add a column to the sheet, or to what it sends to the data adapter?
Was This Post Helpful? 0
  • +
  • -

#4 DarenR   User is offline

  • D.I.C Lover

Reputation: 615
  • View blog
  • Posts: 4,058
  • Joined: 12-January 10

Re: is there a way to add columns to excel dataset

Posted 23 May 2019 - 11:23 AM

it was to send to the sql db -- i solved it above
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1