Reading CSV files & converting specific columns into rows in Ax table

(0) Share

Report

Posted on by Mav

Hi Experts,

I am reading a CSV of 180 columns into a container( Using container instead of temp table as there are 180 columns to be read) which would eventually populate custom Ax table having Axtable column1 to Axtable column 15

Please suggest how can i achieve the following .

1. Create a new row in Ax table for each column of csv file,starting from column#11 /conpeek(myContainer,11) to column#180/conpeek(myContainer,180).

2.Each new row in Ax table will have Ax table's column 1 to column 10 with same values . i.e. conpeek(myContainer,1) to conpeek(myContainer,10) are constant for each new row.

Please share code/psuedo code.

Thanks

Mav.

All responses (15)

Answers (1)

Mav on at

Like (1)

Report

RE: Reading CSV files & converting specific columns into rows in Ax table

Yep Gunjan, thanks a lot , i already marked the post which helped me the most :-).

Thanks once again.
Gunjan Bhattachayya 35,421 on at

Like (1)

Report

RE: Reading CSV files & converting specific columns into rows in Ax table

Mav,

Any luck with this?
Verified answer

Gunjan Bhattachayya 35,421 on at

Like (0)

Report
RE: Reading CSV files & converting specific columns into rows in Ax table

You can try something like this -

In main class // All csv reading code here i = 11; j = 47; k = for (i= 11; i <= 46 ; i = i 2) { ttsBegin; this.myTableInit(record); myTable.Col11 = record[i]; myTable.Col12 = record[1 1]; myTable.col13 = record[j]; myTable.col14 = record[j 1]; myTable.col15 = record[k]; myTable.insert(); ttsCommit; j = j 2; k ; } protected void myTableInit(container _record) { myTable.clear(); myTable.col1 = conpeek(_record,1); myTable.col10 = conpeek(_record,10); }
Mav on at

Like (0)

Report

RE: Reading CSV files & converting specific columns into rows in Ax table

HI Gunjan,

I was able to reduce the file size from 180 to 151 just now, however col1-col10 still is constant & remaining col 11-151 is what new rows will be created in Ax table.

Shared below is the correct structure

Identiefier CSV Col # CSV Col count

Constant 1 to 10 10

Cat1 11 to 46 36

Cat2 47 to 104 68

Cat3 105 to 151 37

Total 151

Thanks

Mav
Gunjan Bhattachayya 35,421 on at

Like (0)

Report

RE: Reading CSV files & converting specific columns into rows in Ax table

Mav, per my calculation,

There are 180-10 = 170 columns and groups of 5 columns per record. Hence 34 groups.

Cat1 should be columns 11 - 78

Cal2 should be columns 79 - 146

Cat3 should be columns 147-180

Does this look right?
Mav on at

Like (0)

Report

RE: Reading CSV files & converting specific columns into rows in Ax table

HI Gunjan,

Yes, per record 15 columns, 10 constant & 5 of those you mentioned above.

It is like this below, hope this grid helps.

Thanks

Mav
Gunjan Bhattachayya 35,421 on at

Like (0)

Report

RE: Reading CSV files & converting specific columns into rows in Ax table

Hi Mav,

So, you have columns 11 and 12 in Cat 1, 37 and 38 in Cat 2, 105 in Cat3. That doesn't cover for all the 180 columns. Per record you have 5 column values. Is that correct?
Mav on at

Like (0)

Report

RE: Reading CSV files & converting specific columns into rows in Ax table

Nopes, i do have myTable.Orderid but cannot use this as primary key because it will get repeated for all 170 rows for 1 row of CSV file.
Gunjan Bhattachayya 35,421 on at

Like (0)

Report

RE: Reading CSV files & converting specific columns into rows in Ax table

Hi Mav, do you have a primary index for the table other than RecId?
Mav on at

Like (0)

Report

RE: Reading CSV files & converting specific columns into rows in Ax table

Hi Gunjan,

I used == & not assignment operator in real code :-) :-).

The issue is not in if , else if nested statement in for but in the myTableInsertMethod() where some logic has to be there so that

It creates new row for all cat1, cat2 & cat 3 values while reading from CSV file.

For example when it reads i = 11, go to first if condition & calls this.mytabbleinstertMethod();

//reads value like this which works fine for i = 11

mytable.coll11 = conpeek(_record,11); //Cat1

mytable.coll12 = conpeek(_record,12); //Cat1

mytable.coll13 = conpeek(_record,37); //Cat2

mytable.coll14 = conpeek(_record,38); //Cat2

mytable15 = conpeek(_record,105); //Cat3

//Should skip i = 12 as we have already read that in mytable.coll12 above & go to i =13

mytable.coll11 = should change from conpeek(_record,11); TO conpeek(_record,13) //CAT1

mytable.coll12 = should change from conpeek(_record,12); TO conpeek(_record,14) //CAT1

mytable.coll13 = should change from conpeek(_record,37) TO conpeek(_record,39); //CAT2

mytable.coll14 = should change from conpeek(_record,38) TO conpeek(_record,40) //CAT2

mytable15 = should change from conpeek(_record,105) TO conpeek(_record,106; //CAT3

and so on for all CAT1, CAT2& CAT3 .

I hope this helps in understanding the issue, please suggest a fix for this.

Thanks

Mav