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.

I have the same question (0)

All responses (15)

Answers (1)

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi Mav,

Could you please confirm the following?

1. You have an AX table with 15 fields.

2. the csv file has 180 columns. You are going to insert values into the table from column 11 through to column 180. So, per row of csv you would have 170 records.

3. How many rows do you have in the csv?

Was this reply helpful? Yes No
Mav on at

Like (0)

Report

Hi Gunjan,

Thanks for your reply.

1>Yes.

2>Yes.

3>1032 ,so 175,440 rows in my Ax table.

Thanks

Mav

Was this reply helpful? Yes No
Suggested answer

Gunjan Bhattachayya 35,423 on at

Like (0)

Report
Hi Mav,

Please try this code and see if that works for you.

static void TestcsvImport(Args _args) { AxTable axTable; container record; str Delimiter = ","; int totalRecords; AsciiIo importFile; FileName fileName = @"C:\Test\test.csv"; int i; importFile = new AsciiIo(fileName, 'R'); importFile.inFieldDelimiter(Delimiter); if((!importFile) || (importFile.status() != IO_Status::Ok)) { warning("Error in opening import file"); throw(Exception::Error); } try { ttsBegin; while(importFile.status() == IO_Status::Ok) { i = 11; record = importFile.read(); for (i=11; i <= 180; i ) { if (record(conPeek, i)) { insertIntoTable(); } } } ttsCommit; } catch(Exception::Error) { throw(Exception::Error); } insertIntoTable() { axTable.clear(); axTable.Field1 = conPeek(record, 1); axTable.Field2 = conPeek(record, 2); ... axTable.insert(): } }

Was this reply helpful? Yes No
Mav on at

Like (0)

Report
In main class // All csv reading code here for (i= 11;i <= 36) //cat1 this.myTableInsertMethod(record); else if ( i = 37 && i < = 104) //cat2 this.myTableInsertMethod(record); else //cat3 this.myTableInsertMethod(record); } protected void myTableInsertMethod(container _record) { myTable.clear(); myTable.col1 = conpeek(_record,1); myTable.col10 = conpeek(_record,10); myTable.col11 = //cat1 ( i = 11 && i <= 36 myTable.col12 = //cat1 i = 11 && i <= 36 myTable.col13 =//cat2 i = 37 && i < = 104 myTable.col14 = //cat2 i = 37 && i < = 104 myTable.col15 = //cat3 remaining i values) ttsBegin; myTable.insert(); ttsCommit; }

Thanks for your note, however it is not working , i think there has to be a logic to create new rows for different cat1 /cat2 /cat 3 (ranges of i) like shown above may be using switch case in myTableInsertMethod or something which ensures that a new row is created for all values of i falling under cat 1 , cat 2 & cat3 .

Can you pls suggest a fix.

Thanks

Mav

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report
Hi Mav,

Did you write "i=11" or "i == 11"? I think it should be like this -

if (i >= 11 && i <= 36) //cat1 this.myTableInsertMethod(record); else if ( i >= 37 && i < = 104) //cat2 this.myTableInsertMethod(record); else //cat3 this.myTableInsertMethod(record);

Was this reply helpful? Yes No
Mav on at

Like (0)

Report

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

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Mav on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Mav on at

Like (0)

Report

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

Was this reply helpful? Yes No