Import and maping fields from csv

enoahmedspahic

Hi!
I try to create data port which will import data from csv file. Before importing I need to find record with key (in code, onBeforeImportRecord) which is in csv file. When it find record with key it should replace key with another value which is also (second data) in same csv file.
Questions:

CSV has this template

oldKey1;newKey1
oldKey2;newKey2
....
....
How can I assign (map)values from csv whith some variable which I can use in code (to find record with oldKey1 value) ?
What I should do to enable changing some key-value?
(I know that I must disable any unpredictable situation during import, in code)

I appreciate any help....

nuno_almeida

Hi there,

I belive that your problem you want to solve is renaming the record key, right?

Try it this way.

Create 2 code variables
OldKey
NewKey

And a Record variable of the table to wich you want to import
For example:
ImportRecord

On the dataport fields assign the Oldkey variable to the first line, and the NewKey variable to the second line

Now on the onBeforeImportRecord

Use this code:

IF ImportRecord.GET(OldKey) THEN
ImportRecord.RENAME(NewKey);

It's as simple as that

enoahmedspahic

Thanks man...I will try tomorrow that...and mark this as SOLVED (I hope).

enoahmedspahic

It work fine but....After import...it create one empty line in table and also skip last record in csv file.To solve that, I was add one row (temp;temp) and it change all keys before temp and this is acceptable for me...

I will continue with this solution but if anybody has some better solution I will appreciate.

And thanks Nuno, it was helpful.

Savatage

Where are you putting your code & does your file have a header line?

enoahmedspahic

I put my code in On before import section of ITEM which is selected in dataport. I select one table in which i want to change keys.
I try to replace key value (field No.) in Item table. I have csv file in format

ExistingKey1; newKey1
ExistingKey2; newKey2
ExistingKey3; newKey3

so I need to replace if I find in ITEM item with Existing key with newKEY.

Also I find solution for my previous problem with empty row. I set on ITEM of dataport properties AUTOSAVE and AUTOUPDATE on "NO"....AFTHER THAT IT WORK FINE...If I find solution for my second problem...I will present it here...

Savatage

I believe if you put your code
OnAfterImportRecord()
it should not skip or add any lines - have you tried you dataport using that trigger?

You want to do stuff "after" you get it

enoahmedspahic

You are right...i resolve that.
Now I have another problem. I have three companies with one shared table Item. I want to change Items key in all of them.Companies which I need has value "share Items" set on true in Company table. If I want to change some Key I must change "share Item" on FALSE. So I create next code...(in on after import record section)...


T_company.SETFILTER("Share Items", '%1', TRUE);
IF T_company.FIND('-') THEN REPEAT

CHANGECOMPANY(T_company.Name);

T_company."Share Items":=FALSE;

//******when I start...it dont go in next statement******
IF ((oldKey<>'') AND T_item.GET(oldKey)) THEN BEGIN
T_item.RENAME(newKey);
//MESSAGE('Kljuc'+oldKey+' is replaced: '+newKey);
COMMIT;
END
ELSE BEGIN
MESSAGE(oldKey+' not found');
END;

UNTIL T_company.NEXT=0;

I don't know why it go only first time... in "IF ((oldKey<>'') AND T_item.GET(oldKey)) THEN BEGIN" statement. For other companies it don't go. In many cases RENAME don't work...(it work without changing company)

enoahmedspahic

FINAL code


I create DataPort with one item "Item (Table)".
Afther that I create global variables:
Name DataType Subtype Length
T_item Record Item
oldKey Code 20
newKey Code 20
T_company Record Company

Mapping records within CSV file and dataport is created in Dataport fields:
Enabled SourceExpr StartPos Width
Yes oldKey 0 0
Yes newKey 0 0

After that, next code was created but in OnAftherImportSection.
!!!Before import records,I must set in table Company variable "Share Items" on false manually .

//EST01 start

T_company.SETFILTER(Name, 'CompanyName1|CompanyName2|Companyname3' );
IF T_company.FIND('-') THEN REPEAT

CHANGECOMPANY(T_company.Name);
T_item.CHANGECOMPANY(T_company.Name);

IF ((oldKey<>'') AND T_item.GET(oldKey)) THEN BEGIN
T_item.RENAME(newKey);
COMMIT;
//MESSAGE('Key'+oldKey+' is replaced with: '+newKey);
END
ELSE BEGIN
MESSAGE(oldKey+' is not found in '+T_company.Name);
END;


UNTIL T_company.NEXT=0;


//Est01 end;


Thanks for help...