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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Import from Excel file
Microsoft Dynamics AX (Archived)

Import from Excel file

(0) ShareShare
ReportReport
Posted on by Lakshmi Karambakkam 1,307

Hi,

I have data in a excel file which has to be inserted into a custom table. End users sometime does bulk insertion (hundreds of rows) through excel rather than key-in the data.

Custom table has string, int, enum fields.

data is inserted into fields as below

table1.field1 = any2str(cells.item(row,1).value().bstr());  --> String field

table1.field2 = any2str(cells.item(row,2).value().bstr());  --> String field

table1.field3 = any2int(cells.item(row,3).value().int());   --> Integer field

table1.field4 = any2int(cells.item(row,4).value().int());   --> Enum  field

The data type in excel file for field 3 and field 4 is integer.

Still no value is getting populated into integer and enum fields.

Could some one guide me how to resolve this?

Thanks,

Lakshmi

*This post is locked for comments

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 236,279 Most Valuable Professional on at
    RE: Import from Excel file

    Do you have any reason trying to develop something that DIXF can already do for you?

  • Lakshmi Karambakkam Profile Picture
    Lakshmi Karambakkam 1,307 on at
    RE: Import from Excel file

    Martin,

    This is not data migration. Every day end users will be using a new spreadsheet to import the transactions rather than keyin into a custom table as there will be hundreds of rows.

    Could you please let me know the bug in my code as why integer values are not getting inserted into the table?

    Thanks,

    Lakshmi

  • Martin Dráb Profile Picture
    Martin Dráb 236,279 Most Valuable Professional on at
    RE: Import from Excel file

    Why do you think that you can't use DIXF for anything except one-off data migration? You can use it for any data export and import.

    If you insist of reinventing the wheel (and omitting quite a few features that DIXF offers and you likely won't imlement), start with debugging your code. You have no idea where your code fails because you're trying to do too many things at once.

    First check if your code successfully fetches the values. If it does, you have a problem with writing to your table and you can ignore the whole Excel thing. If it doesn't, the problem has nothing to do with writing to the table and you can ignore this part. In either case, it will immediately make the problem simpler.

  • Lakshmi Karambakkam Profile Picture
    Lakshmi Karambakkam 1,307 on at
    RE: Import from Excel file

    Hi,

    The data will be imported from excel only when the cell values are changed into string values in excel (like add apostrophe before number and the number gets converted to string).

    table.fieldID3   = str2int(cells.item(row,3).value().bStr());

    table.fieldID5  = str2num( cells.item(row,5).value().bStr());

    why can't the int and real values get populated directly like

    cells.item(row,3).value().int() and

    cells, item(row,3).value().decimal();

    Also if apostrophe is the only option, then what is the alternative of adding apostrophe to all excel cell values in one go as there will be some thousands of rows in excel?

    Thanks,

    Lakshmi

  • Verified answer
    Community Member Profile Picture
    Community Member on at
    RE: Import from Excel file

    How are values formatted in Excel columns 3 and 4 – numbers or text?

    When reading data from Excel documents you can check the data type in Excel cell using the variantType() method (SysExcelCell.value().variantType()). Then use the appropriate value method to get the data directly from Excel and convert where needed.

    In your example, it could be something like the following:

    COMVariant 	value;
COMVariantType 	valueType;	
...

value = cells.item(row,1).value();	
valueType = value.variantType();
if (value == COMVariantType::VT_BSTR)
	table1.field3 = any2int(value.bStr()); 
else
	table1.field3 = value.int();

    You can expand that if statement and e.g. use switch statement to handle conversions depending on COMVariantType (e.g. VT_I* and VT_U* integers, etc.). Also do some refactoring and place this in a separate method so you don't have heaps of copy/paste code for multiple Excel cells/columns.

  • Verified answer
    Community Member Profile Picture
    Community Member on at
    RE: Import from Excel file

    Just a small correction - that if statement should check valueType (not value), so:

    if (valueType == COMVariantType::VT_BSTR)


  • Lakshmi Karambakkam Profile Picture
    Lakshmi Karambakkam 1,307 on at
    RE: Import from Excel file

    Hi Jovan,

    Thanks for the valuable tips.

    The int value was actually an enum variable holding values from 1 to 10. So I was trying to pull the values as int but looks like AX saves it as a STR value but not as int value so either I should use str2int or str2enum.

    COMVarianttype of real values is shown as VT_R8. To pull the values I should use

    double() rather than decimal().

    table1. field4 = cells.item(row, 20).value().double(); //works

    Thanks,

    Lakshmi

  • Lakshmi Karambakkam Profile Picture
    Lakshmi Karambakkam 1,307 on at
    RE: Import from Excel file

    For those who are not aware of this tip ,

    Select the data from a form and press CTRL+T, an excel sheet will be automatically open with the form data pasted and also with a green triangle on the top left for those excel cells containing numbers which means these cell values are string values in dynamics AX

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#2
Guy Terry Profile Picture

Guy Terry 2 Moderator

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans