Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

How to import data from excel to UserDefined Fields

Dear All,

Is it possible to import data for UDFs from excel??

With Regards

G.shankar Ganesh

Former Member
Former Member replied

Hi, It'is possible.

Look these threads:

- [Export & Import UDO's to/from XML|Export & Import UDO's to/from XML;

- [Import Data to UDO|Import Data to UDO;

- [MSEXCEL TO UDT TRANSFERING DATA...|MSEXCEL TO UDT TRANSFERING DATA(It's URGENT);

Regards

Sierdna S.

P.S. Some usefull notes & functions.

Compose your SQL insert string...

...
            ' *** SAP Fields
            Dim xCode As String = GetStrNextCode(sTableName)
            Dim xName As String = xCode
            'Dim xDocEntry As String = xCode 
            Dim xDocEntry As String = Get_Next_DocEntry(oSqLTrans)  
            Dim xCanceled As String = "N"
            ' +++++++++++++++++++++++++++++++++++++++++++
            Dim xObject As String = "UDOOBJECTNAMEHERE"
            ' +++++++++++++++++++++++++++++++++++++++++++
            Dim xLogInst As String = "NULL"
            Dim xUserSign As String = SBO_Company.UserSignature
            Dim xTransfered As String = "N"
            Dim xCreateDate As String = "NULL"
            Dim xCreateTime As String = "NULL"
            Dim xUpdateDate As String = "NULL"
            Dim xUpdateTime As String = "NULL"
            Dim xDataSource As String = "I"
            ' *** Add here UDF values
            ' *** UDF
            Dim xU_Field1 As String = value1
            Dim xU_Field2 As String = value2
            Dim xU_Field3 As String = value3
            ...

            '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
            ' Create sql string for INSERT STMT
            '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
            Dim sbSQL As New StringBuilder
            ' INSERT INTO *****************************
            sbSQL.Append("INSERT INTO " & sTableName & " (")
            sbSQL.Append("[Code]")
            sbSQL.Append(",[Name]")
            sbSQL.Append(",[DocEntry]")
            sbSQL.Append(",[Canceled]")
            sbSQL.Append(",[Object]")
            sbSQL.Append(",[LogInst]")
            sbSQL.Append(",[UserSign]")
            sbSQL.Append(",[Transfered]")
            sbSQL.Append(",[CreateDate]")
            sbSQL.Append(",[CreateTime]")
            sbSQL.Append(",[UpdateDate]")
            sbSQL.Append(",[UpdateTime]")
            sbSQL.Append(",[DataSource]")
            sbSQL.Append(",[U_Field1]")
            sbSQL.Append(",[U_Field2]")
            sbSQL.Append(",[U_Field3]")
            ' VALUES ***********************************
            sbSQL.Append(") VALUES (")
            sbSQL.Append("" & xCode)
            sbSQL.Append("," & xName)
            sbSQL.Append("," & xDocEntry)
            sbSQL.Append("," & xCanceled)
            sbSQL.Append("," & xObject)
            sbSQL.Append("," & xLogInst)
            sbSQL.Append("," & xUserSign)
            sbSQL.Append("," & xTransfered)
            sbSQL.Append("," & xCreateDate)
            sbSQL.Append("," & xCreateTime)
            sbSQL.Append("," & xUpdateDate)
            sbSQL.Append("," & xUpdateTime)
            sbSQL.Append("," & xDataSource)
            sbSQL.Append("," & xU_Field1)
            sbSQL.Append("," & xU_Field2)
            sbSQL.Append("," & xU_Field3)
            sbSQL.Append(")")

            ' INSERT PARENT **************************************************************
            Dim sSQL As String = sbSQL.ToString
            Dim sqlCommand As New SqlClient.SqlCommand(sSQL, oSqlConnection, oSqLTransaction)
            sqlCommand.ExecuteNonQuery()
...

You need to have max of Code column.

Note: it may be "00000001", "00000002", "000000AA", "000000AB", "0000AAAZ",... so you need some function what make convertion...

Public Function getStrNextCode(ByVal sTableName As String) As String
  Dim oRecordset As SAPbobsCOM.Recordset
  oRecordset = SBO_Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
  Dim sRetValue As Long = -1
  Dim code As String
  Try
      oRecordset.DoQuery("SELECT COALESCE(MAX(code), '00000000') AS code FROM " & _
                          sTableName & " WHERE LEN(Code) = 8 ")
      If oRecordset.RecordCount > 0 Then
	  oRecordset.MoveFirst()
	  code = oRecordset.Fields.Item(0).Value
	  If code = "" Then code = "0"
	  sRetValue = code
      End If
  Catch ex As Exception
      sRetValue = -1
  Finally
      oRecordset = Nothing
  End Try
  Return (sRetValue + 1).PadLeft(8, "0")
End Function

Function to set Next DocEntry...

Function Get_Next_DocEntry( _
	ByVal oSqlTransaction As System.Data.SqlClient.SqlTransaction, _
	sUDOName As String _
) As Integer

  Dim iCode As Integer
  Try

    ' SELECT ****************
    Dim sSQL As String = "SELECT AutoKey FROM ONNM WHERE ObjectCode = '" & sUDOName & "'"
    Dim oSqlCommand As New SqlClient.SqlCommand(sSQL, oSqlConnection, oSqlTransaction)
    iCode = oSqlCommand.ExecuteScalar

    ' UPDATE ****************
    sSQL = "UPDATE ONNM SET AutoKey = " & (iCode + 1) & " WHERE ObjectCode = '" & sUDOName & "'"
    oSqlCommand = New SqlClient.SqlCommand(sSQL, oSqlConnection, oSqlTransaction)
    oSqlCommand.ExecuteNonQuery()

  Catch ex As Exception
    Throw New Exception("next DocEntry key error for " & sUDOName & ": " & ex.Message)
  Finally
  End Try

  Return iCode + 1

End Function

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question