Forced concatenation of multiple files

[thanhng34]

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.

[brunobertels · Accepted solution]

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 😉

[thanhng34]

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 ? 

[thanhng34]

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...

[PrashantSangle]

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

[thanhng34]

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

[PrashantSangle · Accepted solution]

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

[thanhng34]

Yes finally it worked ! Thanks so much for your help.

Regards,

Thanh