web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to read excel row ...
Microsoft Dynamics AX (Archived)

How to read excel row object and store in the container or other format?

(0) ShareShare
ReportReport
Posted on by Tony Mok 577

I know how to read cell value eg. sheet.cells().item(3,2).value()

but, how can I get row and store in container?

eg. container con;

str value;

con = sheet.row(3);

value = conpeek(con, 2);

*This post is locked for comments

I have the same question (0)
  • MYGz Profile Picture
    MYGz 2,162 on at

    How often do you import data from excel? Every half an hour or couple of times a day?

    Does the schema remains the same everytime?

  • Tony Mok Profile Picture
    Tony Mok 577 on at

    The schema remains the same.

    It should be imported once but there are millions of record. So, I can just import 7 thousand records every day for couples of hours due to memory problem.

    Instead of reading each cell, I want to read a row for improvement so that I can import more records every day.

  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    Why don't you use DIXF? It uses much more efficient approach.

    If your code uses way too much memory, you could find the bug and fix it (e.g. that you put all the data into memory, maybe even several times). But if you use DIXF, you won't have to worry about that anymore and you'll get a tested solution supported by Microsoft.

    To answer your question - no, there is no conversion from an Excel row to X++ container. You could get values one by one and put them into container. But if you use DIXF, you don't have to worry about these things.

  • Tony Mok Profile Picture
    Tony Mok 577 on at

    Martin Dráb, Thank you very much.

  • MYGz Profile Picture
    MYGz 2,162 on at

    Alternate way can be using SQL Server Management Studio (You can import Millions of records in single process this way):

    1. Using SQL Server Import Export Wizard (64-Bit) upload the entire excel file to a table (SQLTable1) in MicrosoftDynamicsAX database or any other database on the same database server. If some cells are empty then they will be imported as NULL. You have to handle that with ISNULL() function in below T-SQL script.

    2. Create a Table(AXTable1) in AOT with required fields. Enter 1 record manually in this table so that RecId gets loaded into cache and System Sequences. You can delete this record later. Enter uniquely identifiable data in the rows to help you pin it down afterwards.

    3. Import the data from the table created in step1 (SQLTable1) to table created in Step2(AXTable1). There are 4 additional DynamicsAX columns that you will have to manually insert in AXTable1.

    i.e DataAreaId, RecVersion, Partition, RecId

    The Only AX Column that has to be dealt carefully in AXTable1 is RecId column. You have to get the Next RecId from system Sequences and update it in SystemSequences after inserting rows to AXTable1.

    Warning!!

    You have to check the values of DataAreaId, Partition according to your system. Try it on test server. If you know T-SQL you can figure out the below script. But if you don't know then kindly review it with someone who knows it.

    Short example:

    DECLARE	@V_MAX_VAL  BigInt
DECLARE	@CURRENTRECID  BigInt

SET @CURRENTRECID = SELECT ISNULL(MAX(NEXTVAL),1) - 1 
    FROM SYSTEMSEQUENCES
WHERE DATAAREAID = 'DAT' AND NAME = 'SEQNO' 
       AND TABID = (SELECT TABLEID FROM SQLDICTIONARY WHERE NAME='AXTABLE1' AND FIELDID=0)
 
INSERT INTO AXTABLE1(
        Column1,
        Column2,
    DATAAREAID,
    RECVERSION,
    PARTITION,
    RECID
)
	
SELECT        
    ISNULL(Column1,''),
        Column2,
    'FTT' as DATAAREAID,
    1 as RECVERSION,
    5637144576 as PARTITION,
    @CURRENTRECID + ROW_NUMBER() Over(Order by (SELECT NULL)) RECID
FROM    SQLTABLE1


set @V_MAX_VAL = (select max(recid) from AXTABLE1)

update SYSTEMSEQUENCES
    set NEXTVAL = @V_MAX_VAL + 1
WHERE DATAAREAID = 'DAT' AND NAME = 'SEQNO' 
      AND TABID = (SELECT TABLEID FROM SQLDICTIONARY WHERE NAME='AXTABLE1' AND FIELDID=0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    All right, so one option is using the standard feature of AX 2012, which is supported by Microsoft, it respects security setup, validations written in business logic, can deal companies, surrogate keys, financial dimensions and so on and it doesn't require any coding.

    Or we can give somebody access for writing to the production database and write everything by hand in T-SQL code, without any option to reuse existing business logic, bypassing validations and security, not taking caching into account and so on and so on. Nobody should approve such a thing.

  • MYGz Profile Picture
    MYGz 2,162 on at

    Would this appropriate for integrating data from other systems which requires lot of customised code to create Journal Batch and then post them. I was suggesting from that angle.

    For eg if we have to create 1 journal per staging record and also run settlements and other custom code?

    I haven't explored DMF much but just opening balance entity where we have to manually create a journal batch before we import lines. Can journal batch and voucher creation per record be automated somehow?

  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    I'm not sure what you mean by "this" in "would this appropriate"?

    If you mean custom T-SQL against production database, the is "absolutely not". The business logic must be executed in the application layer.

    If you mean DIXF, then yes, it's can be used for integration with other systems (it's an excellent choice for processing a large amounts of data in batch) and it can execute any business logic. Just note that I would avoid mixing data import with too many other steps (following single-responsibility principle makes things easier to track and maintain). Consider splitting your logic into more steps, e.g. data import + an additional steps (based on a schedule or event) posting journals and doing things like that.

  • MYGz Profile Picture
    MYGz 2,162 on at

    By this I meant custom staging tables. Not out of the box tables provided by Microsoft. After inserting into staging, using batch jobs to process them.

    But I guess touching the number sequence in production could possibly make the system unstable.

    Can you link any material on how to create for eg. Multiple general journals (LGtable and LGtrans) with a single process using excel file?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans