How to import data from excel to UserDefined Fields

[Former Member]

Dear All,

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

With Regards

G.shankar Ganesh

[Former Member · Accepted solution]

Hi, It'is possible.

Look these threads:

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

- [Import Data to UDO|;

- [MSEXCEL TO UDT TRANSFERING DATA...|;

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

[Former Member]

Hi,

U can use the DTW provided and import the UDF values but for that u need to make the required changes in the DTW templated provided.

Vasu Natari.

[Former Member]

Hi...

It's possible.

Regards...

Billa 2007