Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I need your help to add some calculated fields to my files, before concatenating all of my 8 files.
I would like to :
- Add an "IP" field in all the files:
IP = 'CAPI "if the name of the file starting with' capi ';
IP = 'CPCEA' if the name of the file starting with 'cpcea'.
- Add an "Activity" field in all files:
Activity = 'adhesion' if the file name contains 'adh'
Activity = 'affiliation' if the file name contains 'affil'
Activity = 'accompte' if the name of the file contains 'acpte'
Activity = 'reglement' if the name of the file contains 'rglt'
- Concatenate all these 8 files.
How can I do it in script?
Thanks so much in advance for your help.
Hello
Add this in your script :
load
subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activity,
...
from etc
Et çà devrait marcher 😉
use force concatenate
Test:
Load 'dummy' as id;
For each vFile in FileList('lib://$(vConnexionFichiersDAP)/DSN/Suivi Anomali/Suivi/*.csv')
concatenate
LOAD
*,
subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activité
From [$(vFile)] (txt, codepage is 28591, embedded labels, delimiter is ';', msq);
Next vFile;
then you can remove the 1st dummy line from your table;
Regards,
Prashant Sangle
Hello
Add this in your script :
load
subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activity,
...
from etc
Et çà devrait marcher 😉
Hi,
Thanks for your solution. I tried adding this to my script but it did'nt work (so many "$Syn"), while the concatenation of one by one file (by "CONCATENATE" function) worked .
Here is my script :
For each vFile in FileList('lib://$(vConnexionFichiersDAP)/DSN/Suivi Anomali/Suivi/*.csv')
LOAD
*,
subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activité
From [$(vFile)] (txt, codepage is 28591, embedded labels, delimiter is ';', msq);
Next vFile
Maybe I made a mistake in my script ?
I think It didn't work because my files do not have exactly the same set of fields (they have some common fields, but also different ones), so I have to make a forced concatenation.
But I don't know how to correct my script as I have a lot of files, and I don't want to repeat the CONCATENATE functions...
for adding field IP use filename()
like Load *, if(wildmatch(filename(),'capi*','CAPI','CPCEA') as IP
for activity field() also use combination of wildmatch() and filename()
like
if(wildmatch(filename(),'*adh*'),'adhesion'),if(wildmatch(filename(),'*affil*'),'affiliation'))) as activity
regards,
Prashant Sangle
Hi @PrashantSangle and @brunobertels ,
Thanks for your solution. I managed to create the fields 'IP' and 'Activity" with your suggestions, but the problem is that I don't know how to concatenate my 8 files which do not have the same set of fields. I want to make a forced concatenation.
Here is my script :
For each vFile in FileList('lib://$(vConnexionFichiersDAP)/DSN/Suivi Anomali/Suivi/*.csv')
LOAD
*,
subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activité
From [$(vFile)] (txt, codepage is 28591, embedded labels, delimiter is ';', msq);
Next vFile
use force concatenate
Test:
Load 'dummy' as id;
For each vFile in FileList('lib://$(vConnexionFichiersDAP)/DSN/Suivi Anomali/Suivi/*.csv')
concatenate
LOAD
*,
subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activité
From [$(vFile)] (txt, codepage is 28591, embedded labels, delimiter is ';', msq);
Next vFile;
then you can remove the 1st dummy line from your table;
Regards,
Prashant Sangle
Yes finally it worked ! Thanks so much for your help.
Regards,
Thanh