Skip to Content
avatar image
Former Member

how to copy more than 5000 records from excel to itemmatrix

Hi experts,

I have excel sheet with 5000 records.and need to be add these records with UDO.

so i want to show 5000 record to show in itemmatrix.

i am trying to show these records through looping.

for that initially i am copying excel data into C#.net datatable and then datatable to itemmatrix

but it is taking 20 min.

is there any way to fill excel to itemmatrix in faster way

here is my code:

for (int i = 0; i < dt.Rows.Count; i++)//here datatable contains 5000 rows

            {

            

                SAPbouiCOM.EditText Id = (SAPbouiCOM.EditText)oMatrix.Columns.Item("V_-1").Cells.Item(i + 1).Specific;

                SAPbouiCOM.EditText oEdit1 = (SAPbouiCOM.EditText)oMatrix.Columns.Item("V_1").Cells.Item(i + 1).Specific;

                SAPbouiCOM.EditText oEdit2 = (SAPbouiCOM.EditText)oMatrix.Columns.Item("V_2").Cells.Item(i + 1).Specific;

                Id.Value = (i + 1).ToString();

                oEdit1.Value = dt.Rows[i][0].ToString();

                oEdit2.Value = dt.Rows[i][1].ToString();

            }

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jan 02, 2015 at 06:55 AM

    Hi Lakkshmi,

    Writing to matrix directly is very slow.

    Try writing to the base DataSource of this matrix and use the Matrix.LoadFromDataSource method.

    Regards
    Edy

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 02, 2015 at 07:01 AM

    Hi Lakkshmi,

    Try it as follows:

    this is just for a sample. assuming you are getting value from a RecordSet.

    DBDataSource oDBDataSource;

    oForm.DataSources.DBDataSources.Item("@TABLENAME").Clear();

    SAPbouiCOM.DBDataSource oDB = (SAPbouiCOM.DBDataSource)oForm.DataSources.DBDataSources.Item("@TABLENAME");

                                                             oForm.DataSources.DBDataSources.Item("@MAKEINVDLVL5").InsertRecord(oDB.Size);

                                                             oDB.SetValue("U_LineID", oDB.Size - 1, Convert.ToString(oRecSet.Fields.Item("LineID").Value));

                                                             oDB.SetValue("U_LVL5C", oDB.Size - 1, Convert.ToString(oRecSet.Fields.Item("Code").Value));

                                                             oDB.SetValue("U_LVL5N", oDB.Size - 1, Convert.ToString(oRecSet.Fields.Item("Name").Value));

                                                             oDB.SetValue("U_UOM", oDB.Size - 1, Convert.ToString(oRecSet.Fields.Item("UOM").Value));

    In the end don't forget oMatrix.LoadFromDataSource();

    Hope it Helps.

    Thanks & regards

    Ankit Chauhan

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 02, 2015 at 09:54 AM

    Hi,

    I was having similar issue as in ur case, only that the input source was sql data and need to show about 20000 rows.

    I did the following:-

    1. unbind matrix columns

    2. populate datasource from datatable

    3. rebind matrix columns

    4. loadmatrix

    oForm.DataSources.DataTables.Item("My").Clear();

                    string Query = "EXEC TIS_45A_POPULATE";

                    oForm.DataSources.DataTables.Item("My").ExecuteQuery(Query);

                    oMatrix = (SAPbouiCOM.Matrix)oForm.Items.Item("mtx_0").Specific;

    step 1. unbind matrix columns

                    oMatrix.Columns.Item("V_11").DataBind.UnBind();//U_SONum

    step 2. populate datasource from datatable

                    SAPbouiCOM.DataTable oDataTable = oForm.DataSources.DataTables.Item("My");

                    oDbDataSource = oForm.DataSources.DBDataSources.Item("@TIS_45A1");

                    oDbDataSource.Clear();

                    for (int row = 0; row <= oDataTable.Rows.Count - 1; row++)

                    {

                        int offset = oDbDataSource.Size;

                        oDbDataSource.InsertRecord(row);

                        double qty = Convert.ToDouble(oDataTable.GetValue("U_QtyNos", row).ToString());

                        oDbDataSource.SetValue("U_SOEn", offset, oDataTable.GetValue("U_sorderen", row).ToString());

    }

    step 3. rebind matrix columns

    oMatrix.Columns.Item("V_11").DataBind.SetBound(true, "@TIS_45A1", "U_SONum");//done

    step 4. loadmatrix

    oMatrix.LoadFromDataSource();

    hope this helps

    Regards

    Trupti M

    Add comment
    10|10000 characters needed characters exceeded