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 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