Import transaction date from excel to table.

Finance and Operations/AX Developer Forum
1

Hi all,

how to import date from excel to table in ax 2009.

I have tried using EDT “TransDate”, and Date, but getting an error: Error in variable assignment.

Can any one solve this issue?

2

Jasmine,

I tried with the format “30-07-2011” in excel and I am able to import the record in table.

I suggest you to export few records from the table so that you will know the exact format your system is using for excel imports.

I could not attach the excel file with this post as such types are restricted here.

Let me know for further clarifications.

Regards,

Anitha

3

Hi Lilly,

Ax cannot read the date format directly from excel. u ve to to use the "str2date " function to overcome this issue.

Just declare a string variable to read the date from excel. Then convert that to date using “str2date”

i.e, TransDate = str2date(string variable, 123);

Hope this helps you.

Regards,

Madhan

4

Hi Madan,

I have tried what u posted already… but i did not get the output when i assign the table field with that variable name.

5

Hi Anitha,

Will try out as your suggestion and let u know.

6

Hi Anitha & Madan

I tried by exporting the datevalues from table and imported with the same type.

the value is read when using string variable name, but value is not read when converting from str2date(variablename, 231);

7

Jasmine,

What is the table name ? Is it customized or system table?

Regards,

Anitha

8

Hi anitha,

It is ledgerJournalTrans table, systemtable

9

Jasmine,

I had used the same table for testing.

I have attached my file in RAR format. Check and let me know if I could help you out.
test21.rar (43.9 KB)

10

HI Anitha ,

sorry i dont have the facility to download from here. can you plz post it here

11

Jasmine,

This is an excel file.

Do you have your personal email to access my file.

12

Hi Anitha,

I downloaded it, and used the same type. but could not import to table.

The date is been read as a string , but is not read when i assign it to a edt.

str tdate;

transdate td;

;

tdate = (conpeek(data[row-1], col));

//after that i am inserting it to my table;

mytable.transDate = str2date(tdate , 231); // the value is not read here…

how to solve this? i have tried many ways but could not solve it.

13

Jasmine,

In Excel, right click on the date field, and check the format. If it is in string, convert it into date.

Regards,

Anitha

14

Hi ,

sorry , i could not get the result.

Thanks for your support anitha.

15

Hi Lilly,

In excel let it be in string format itself. aft tat check ur date format in system. whether it is mm/dd/yyyy (213) dd/mm/yyyy(123).

Example,

Str dueDate;

dueDate = conpeek(con,15);
ledgerJournalTrans.Due = str2date(dueDate,213);

Regards,

Madhan

16

Hi all,

Thanks a lot for your replies.

got the solution

17

can anyone tell me how to export time entry from excel to table?