Import Data to UDO

[Former Member]

Ok this is a tricky one and I hope someone can help

I need to import data into a user defined object. The Object consists of two tables (one Document the other Document lines).

So far I have managed to import data into the header table. This is done by placing a tab delimited txt file under C:\Users\Costas-1\AppData\Local\Temp\TEMP_LS

eg. @TEST.txt would be placed there and contains:

U_Code U_Name

1 test1

When the UDO is registered the data is imported into the object. My problem is how to import the document lines data. Any ideas?

Edited by: Costas Ioannou on Sep 10, 2008 11:24 AM

[Former Member · Accepted solution]

Hy Costas.

In additon to all writed...

To load data in my UDO I has used this method.

Work fine on my UDO and is only some guidelines for you.

So.

1) I have Excel file with 2 sheets:

1° for parent data with DocEntry column as primary key.

2° for child data with DocEntry, LineNum columns as primary key.

Note, relation between 2 tables is DocEntry column in every sheet.

2) Read Excel data into 2 collections:

cParent = New Collection ' READ data from 1" sheet

cChild = New Collection ' READ data from 2" sheet

3) Import data into your UDO tables:

For Each strP In cParent  
  ' Create DocEntry for UDO Parent
  ' Insert record into UDO Parent
  For Each strC In cChild ' get only what have usualy key DocEntry
    ' Create LineNum for each Child 
    ' Insert Child
  Next
Next

Ofcouse, your need to respect all SAP rules.

HTH

BR

Sierdna S.

P.S. How to read data from Excel.

' Excel File Connection Srtring ***
Dim sConnString As String = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
		"Data Source=" & sFileXls & ";" & _
		"Extended Properties=Excel 8.0;"
Dim oConnection As New OleDbConnection(sConnString)
oConnection.Open()

' READ MASTER DATA ***
Dim sSqlP As String = "SELECT * FROM [MASTER_DATA_Sheet$] ORDER BY 1"
Dim oCmdP As New OleDbCommand(sSqlP, oConnection)
oCmdP.CommandType = CommandType.Text
Dim oDRP As OleDbDataReader
oDRP = oCmdP.ExecuteReader(CommandBehavior.CloseConnection)
While oDRP.Read
  ' fill cParent collection
End While

' READ MASTER DATA ROWS ***
Dim sSqlC As String = "SELECT * FROM [MASTER_DATA_ROWS_Sheet$] ORDER BY 1"
Dim oCmdC As New OleDbCommand(sSqlC, oConnection)
oCmdR.CommandType = CommandType.Text
Dim oDRC As OleDbDataReader
oDRC = oCmdC.ExecuteReader(CommandBehavior.CloseConnection)
While oDRC.Read
  ' fill cChild collection
End While

[Former Member · Accepted solution]

Thanks but this involves writing sql queries to write directly into the SAP database. I wanted to avoid this as I am not 100% sure how UDOs work with the entire SAP system. I will leave this option as a last resort. Thank you

[former_member201110]

Hi Costas,

The only 'official' way I know to load data in to a UDO (rather than manual data entry) is to use the SDN's Test Composer tool. You can write a script that opens your UDO form, enters data and then closes the form. You can then replicate this script to generate a larger script for all the data you want to enter. Effectively this is just like a fast typist. It's not brilliant but it's certainly a lot easier than having to manually enter the data As the Test Composer uses the User Interface to enter data, it doesn't involve any direct SQL input.

Kind Regards,

Owen

[Former Member]

Hey thanks for the replies guys. Aravind the Document UDO contains DocEntry instead of Code. I have tried that method but I get an error when doing it with a UDO of type Document

[former_member191896]

Hi Costas,

To import data to 2 UDO tables (e.g. a Master data + Master data rows), you would have to create 2 text files that correspond to TABLE1 and TABLE2 as shown below. The child records (master data rows) in @TABLE2.txt must specify the 'Code' of the parent record and its own 'LineId'.

@TABLE1.txt

Code	Name	U_Field1
1	1	Val1
2	2	Val2

@TABLE2.txt

Code	LineId	U_Field2
1	1	LineVal1
1	2	LineVal2
2	3	LineVal3
2	4	LineVal4

This example populates 2 child records for each parent record in TABLE1

I believe a similar procedure would work in case you want to import to Document + Document rows

HTH

Aravind

Edited by: Aravind Gadagottu on Sep 10, 2008 9:41 PM

[Former Member]

Costas,

What do you mean by "Import"?

Add record with SQL statements?

Or fill the form fields?

Regards,

Vítor Vieira

[Former Member]

I mean that I have created a UDO and I have data that I would like to enter. I could do it manually by filling in the form fields of the UDO and adding but that would take too long as there is a lot of data to enter. So I would like a way to automatically bring this data in.

[Former Member]

Costas,

If you are looking for a method, like the GetBusinessObject() that enables you to import udos, it doesn't exist.

The way I do it is by opening a new form and setting the new values. I use DBDataSources. It's faster then setting the values on the form/matrix.

Regards,

Vítor Vieira

[Former Member]

I am aware that there is no official way of importing the data into the UDO. I was just looking for suggestions on the easiest way to do it or if there is any unofficial way to do it. Can you please explain what you mean by

"The way I do it is by opening a new form and setting the new values. I use DBDataSources."

[Former Member]

Costas,

I use the code bellow to generate my UDO document, based on an Purchase Order:

Public Function GenerateNP(ByRef oCompany As SAPbobsCOM.Company, ByRef oApplication As SAPbouiCOM.Application, _
        ByRef oBaseForm As SAPbouiCOM.Form) As Boolean
        GenerateNP = False

        Dim oFormNP As SAPbouiCOM.Form = LoadForm(oCompany, oApplication, "frmNP_" & GetLanguageCode(oApplication) & ".srf")
        oFormNP.Freeze(True)
        oBaseForm.Freeze(True)

        Try
            If FormNPLoadDefaultValues(oCompany, oApplication, oFormNP) = False Then
                oApplication.StatusBar.SetText(TranslateStr(oApplication, GenNPFailed), BoMessageTime.bmt_Short)
            End If

            Dim ORDR_DS As SAPbouiCOM.DBDataSource = oBaseForm.DataSources.DBDataSources.Item("ORDR")
            Dim RDR1_DS As SAPbouiCOM.DBDataSource = oBaseForm.DataSources.DBDataSources.Item("RDR1")
            Dim oItem As SAPbobsCOM.Items = oCompany.GetBusinessObject(BoObjectTypes.oItems)
            Dim MatrixNPRowCount As Integer = 0
            Dim PesoBrutoLinha As String = ""
            Dim PesoLiquidoLinha As String = ""
            Dim TaraPalete As String = ""
            Dim TaraCaixa As String = ""
            Dim TaraEmbalagem As String = ""

            Dim TotalTaraPalete As Double
            Dim TotalTaraCaixa As Double
            Dim TotalTaraEmbalagem As Double
            Dim QtdCaixas As Integer
            Dim TotalPesoBruto As Double
            Dim TotalPesoLiquido As Double


            '// Preencher o Cabeçalho da Nota de Produção
            With oFormNP.DataSources.DBDataSources.Item("@ADAT_ONP")
                .SetValue("U_CardCode", .Offset, Trim(ORDR_DS.GetValue("CardCode", ORDR_DS.Offset)))
                .SetValue("U_CardName", .Offset, Trim(ORDR_DS.GetValue("CardName", ORDR_DS.Offset)))
                .SetValue("U_ContPrsn", .Offset, Trim(oBaseForm.Items.Item("85").Specific.Selected.Description))
                .SetValue("U_RefNum", .Offset, Trim(ORDR_DS.GetValue("NumAtCard", ORDR_DS.Offset)))
                .SetValue("U_BaseNum", .Offset, Trim(ORDR_DS.GetValue("DocNum", ORDR_DS.Offset)) & " - " & DateFromDS(oCompany, oApplication, Trim(ORDR_DS.GetValue("TaxDate", ORDR_DS.Offset))))
                .SetValue("U_BaseEntry", .Offset, Trim(ORDR_DS.GetValue("DocEntry", ORDR_DS.Offset)))
            End With

            Dim LastLote As Integer = CInt(GetLastLote(oCompany, oApplication))

            '// Preencher as linhas da Nota de Recepção
            With oFormNP.DataSources.DBDataSources.Item("@ADAT_NP1")
                For i As Integer = 0 To RDR1_DS.Size - 2 '// DataSource é 0 (zero) based e a última linha está sempre vazia
                    If RDR1_DS.GetValue("U_QtdPlt", i) <> "" Then

                        '// Calcular os Pesos Brutos e Liquidos e de cada linha (cada linha corresponde a 1 palete)
                        PesoBrutoLinha = GetPesoBrutoLinha(oCompany, oApplication, RDR1_DS, i) '// PesoBruto = QtdEmbal*CapacidadeEmbal*QtdCaixas + QtdCaixas*TaraCaixa + TaraPalete
                        PesoLiquidoLinha = GetPesoLiquidoLinha(oCompany, oApplication, RDR1_DS, i) '// PesoLiquido = QtdEmbal*CapacidadeEmbal*QtdCaixas
                        TaraPalete = GetTaraPalete(oCompany, oApplication, Trim(RDR1_DS.GetValue("U_PltCode", i)))
                        TaraCaixa = GetTaraCaixa(oCompany, oApplication, Trim(RDR1_DS.GetValue("U_CaixaCod", i)))
                        TaraEmbalagem = GetTaraEmbalagem(oCompany, oApplication, Trim(RDR1_DS.GetValue("U_EmbalCod", i)))
                        QtdCaixas = RDR1_DS.GetValue("U_QtdPlt", i)


                        For j As Integer = 1 To QtdCaixas

                            .InsertRecord(MatrixNPRowCount)
                            .SetValue("LineId", MatrixNPRowCount, MatrixNPRowCount + 1)
                            .SetValue("U_BaseDoc", MatrixNPRowCount, Trim(ORDR_DS.GetValue("DocEntry", ORDR_DS.Offset)))
                            .SetValue("U_CardRef", MatrixNPRowCount, Trim(oBaseForm.Items.Item("14").Specific.Value))
                            .SetValue("U_ItemCode", MatrixNPRowCount, Trim(RDR1_DS.GetValue("ItemCode", i)))
                            .SetValue("U_ItemName", MatrixNPRowCount, Trim(RDR1_DS.GetValue("Dscription", i)))
                            '.SetValue("U_Calibre", ;AtrixNPRowCount, Trim(RDR1_DS.GetValue("Calibre", i)))
                            If oItem.GetByKey(Trim(RDR1_DS.GetValue("ItemCode", i))) Then .SetValue("U_BarCode", MatrixNPRowCount, oItem.BarCode)
                            .SetValue("U_QtdPlt", MatrixNPRowCount, 1)
                            .SetValue("U_PltCode", MatrixNPRowCount, Trim(RDR1_DS.GetValue("U_PltCode", i)))
                            .SetValue("U_PltName", MatrixNPRowCount, Trim(RDR1_DS.GetValue("U_PltName", i)))
                            .SetValue("U_TrPlt", MatrixNPRowCount, TaraPalete)
                            TotalTaraPalete += NumFromDS(oApplication, TaraPalete)
                            .SetValue("U_QtdCaixa", MatrixNPRowCount, Trim(RDR1_DS.GetValue("U_QtdCaixa", i)))
                            .SetValue("U_CaixaCod", MatrixNPRowCount, Trim(RDR1_DS.GetValue("U_CaixaCod", i)))
                            .SetValue("U_CaixaNam", MatrixNPRowCount, Trim(RDR1_DS.GetValue("U_CaixaNam", i)))
                            .SetValue("U_TrCaixa", MatrixNPRowCount, TaraCaixa)
                            TotalTaraCaixa += QtdCaixas * NumFromDS(oApplication, TaraCaixa)
                            .SetValue("U_QtdEmbal", MatrixNPRowCount, Trim(RDR1_DS.GetValue("U_QtdEmbal", i)))
                            .SetValue("U_EmbalCod", MatrixNPRowCount, Trim(RDR1_DS.GetValue("U_EmbalCod", i)))
                            .SetValue("U_EmbalNam", MatrixNPRowCount, Trim(RDR1_DS.GetValue("U_EmbalNam", i)))
                            .SetValue("U_TrEmbal", MatrixNPRowCount, TaraEmbalagem)
                            TotalTaraEmbalagem += QtdCaixas * RDR1_DS.GetValue("U_QtdEmbal", i) * NumFromDS(oApplication, TaraEmbalagem)
                            .SetValue("U_PesoBrut", MatrixNPRowCount, PesoBrutoLinha)
                            .SetValue("U_PesoLiq", MatrixNPRowCount, PesoLiquidoLinha)
                            '.SetValue("U_TTCode", MatrixNPRowCount, ????)
                            .SetValue("U_Lote", MatrixNPRowCount, LastLote + 1)
                            .SetValue("U_NumPltEx", MatrixNPRowCount, MatrixNPRowCount + 1)

                            TotalPesoBruto += NumFromDS(oApplication, PesoBrutoLinha)
                            TotalPesoLiquido += NumFromDS(oApplication, PesoLiquidoLinha)
                            MatrixNPRowCount += 1
                            LastLote += 1
                        Next
                    End If
                Next

                '// Numerar a última linha
                .SetValue("LineId", MatrixNPRowCount, MatrixNPRowCount + 1)

            End With

            With oFormNP.DataSources.DBDataSources.Item("@ADAT_ONP")
                '// Calcular totais do documento
                .SetValue("U_TotPalet", .Offset, MatrixNPRowCount)
                .SetValue("U_TotCaixa", .Offset, GetTotalCaixasOV(oCompany, oApplication, ORDR_DS.GetValue("DocEntry", .Offset)))
                .SetValue("U_TotEmbal", .Offset, GetTotalEmbalagensOV(oCompany, oApplication, ORDR_DS.GetValue("DocEntry", .Offset)))
                .SetValue("U_TTrPalet", .Offset, TotalTaraPalete)
                .SetValue("U_TTrCaixa", .Offset, TotalTaraCaixa)
                .SetValue("U_TTrEmbal", .Offset, TotalTaraEmbalagem)
                .SetValue("U_TPesBrut", .Offset, TotalPesoBruto)
                .SetValue("U_TPesLiq", .Offset, TotalPesoLiquido)
            End With

            With oFormNP.Items.Item("mtx_NP").Specific
                .LoadFromDataSource()
                .AutoResizeColumns()
            End With

            oFormNP.Visible = True
            GenerateNP = True
        Catch ex As Exception
            oApplication.MessageBox("GenerateNP(): " & oCompany.GetLastErrorCode.ToString & ", " & ex.Message)
        Finally
            oBaseForm.Freeze(False)
            oFormNP.Freeze(False)
        End Try

    End Function

Setting the value in the DataSource

With oFormNP.DataSources.DBDataSources.Item("@ADAT_ONP")
                '// Calcular totais do documento
                .SetValue("U_TotPalet", .Offset, MatrixNPRowCount)
End With

is much faster then setting the values in the form

oForm.Items.Item("FORM_ITEM").Specific.Value = ""

Regards,

Vítor Vieira

[Former Member]

That's very good but in my case the data I want to enter is not in SAP. I have a txt file with all the data.

You have however given me an idea. I could import the data into another user defined table using workbench tools. I could then use a similar method to yours to bring the data into my object. I may try it if I have the time.

Thank you

[Former Member]

Costats,

as you can find on these links where support from SAP answered

/message/5897344#5897344 [original link is broken]

you cannot add record into UDO through DI API - SAP is working on it, but its working on it for many months already. Somethink like DTW, which from your text file will import datas into proper tables doesnt work for UDO.

As you can read in links above, inserting records in UDO isnt only filling proper tables, it has more dependencies (like numbering, ...). So I suggest you create some functionality, where you will read in loop datas from text file and through UI API fill it to proper items on your form and add datas in this way. I think that this is only correct way (In this case I dont recomend direct inserts into tables, because it isnt enough).