Importing Excel file in to AX table delimiting Comma

(0) Share

Report

Posted on by Sreedhar V

Hi all,

I want to import excel file in to AX table but while importing the field containing Comma in Excel file should be removed and should be imported in to our AX Table.

ex: Excel field is like r,o,b,e,r,t import as robert & 15,000.00 import as 15000

Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (21)

Answers (1)

Martin Dráb 236,192 Most Valuable Professional on at

Like (0)

Report

RE: Importing Excel file in to AX table delimiting Comma

How can an integer contain commas? It doesn't make sense.

And what's the point of converting real to string instead of interpreting it as real? Does it mean that you have a problem with interpreting the number format?

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report

RE: Importing Excel file in to AX table delimiting Comma

Hi,

You can use function int2str to convert integer and num2str to convert real data types to string such that strreplace can be used to remove commas.

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report

RE: Importing Excel file in to AX table delimiting Comma

Hi,

You can use int2str for integers and num2str for real data types to convert to string.Again you have to use strreplace for them to remove commas.

Was this reply helpful? Yes No
Sreedhar V 20 on at

Like (0)

Report

RE: Importing Excel file in to AX table delimiting Comma

Hi Chaitanya,

You have given me a good way...thank you.

But one more thing I want to ask you is when I am using this strReplace() function it is only converting string values but my file also contains Integer/Real values like currency.

So how should I convert these.

Was this reply helpful? Yes No
Verified answer

Chaitanya Golla 17,225 on at

Like (0)

Report

RE: Importing Excel file in to AX table delimiting Comma

Hi,

If your business requirement is "Excel file ---> remove all comma from excel data ---> convert into .csv file", then you need to perform following steps

1. Read the data from excel file.

Refer link:

(X++ code to Read/Write Dynamics Ax data to excel - Check for Reading Data from Excel File)

learnax.blogspot.com/.../x-code-to-readwrite-dynamics-ax-data-to.html

(How to Read/Write an Excel file through X++ code - 2. Read from an Excel through X++ code)

community.dynamics.com/.../how-to-read-write-an-excel-file-through-x-code

2. On a given record, remove the commas(from required columns) using strreplace or strrem function

Refer links:

(Global::strReplace Method [AX 2012])

msdn.microsoft.com/.../global.strreplace.aspx

(strRem Function [AX 2012])

msdn.microsoft.com/.../aa629339.aspx

3. Create a another csv file using commaIo/textIo by defining required outfiled delimiters and write data to it

Sample Code:

TextIO textIO;

FileName fileName;

fileName = @"C:\FileName.csv";

textIO = new TextIO(filename , #io_write, 1252);

textIO.outFieldDelimiter("|");

if( !fileName || commaIO.status() != IO_Status::Ok)

{

throw error("File Cannot be opened");

}

line = [data1, data2]

textIO.write(line);

Was this reply helpful? Yes No
Sreedhar V 20 on at

Like (0)

Report

RE: Importing Excel file in to AX table delimiting Comma

Hi Martin,

I want to convert Excel to CSV in non-batch mode.

Was this reply helpful? Yes No
Martin Dráb 236,192 Most Valuable Professional on at

Like (0)

Report

RE: Importing Excel file in to AX table delimiting Comma

If you use code for working with Excel that doesn't work in batch and you require doing it in batch, then you aren't able to open the file and remove commas. You have to change something.

By the way, rather than developing all the stuff by yourself, you could simply use DIXF to import the Excel file.

How to deal with commas depends on your answers to my questions. I can't suggest a solution without more information from you.

Was this reply helpful? Yes No
Sreedhar V 20 on at

Like (0)

Report

RE: Importing Excel file in to AX table delimiting Comma

Thanks Martin for quick response .

Actually we developed one custom functionality, in which we are importing .csv file through batch job from network drive.

As we cannot import Excel in batch mode , thats why we need to convert excel into csv using some custom code.

Thanks .

Was this reply helpful? Yes No
Martin Dráb 236,192 Most Valuable Professional on at

Like (0)

Report

RE: Importing Excel file in to AX table delimiting Comma

I would still appreciate if you answered my questions, such as how you got into this situation, how many fields you're talking about etc.

Your design can be done, but what's the point of changing the Excel file, converting it to CSV and importing CSV instead of importing the Excel file directly?

Was this reply helpful? Yes No
Sreedhar V 20 on at

Like (0)

Report

RE: Importing Excel file in to AX table delimiting Comma

Hi Martin,

sorry if i couldn't make requirement clear.

My requirement is simple - we have excel file with data, and from that data we need to remove comma(,) wherever its there.

And after removing all comma(,) I need to convert same file into .csv file.

Excel file ---> remove all comma from excel data ---> convert into .csv file

Thanks

Was this reply helpful? Yes No

Community site session details

Importing Excel file in to AX table delimiting Comma

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Importing Excel file in to AX table delimiting Comma

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates