topic Re: Forced concatenation of multiple files in App Development

Forced concatenation of multiple files

thanhng34 — Sat, 16 Nov 2024 17:46:41 GMT

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.

Re: Add a field with conditions in file name on multiple files

brunobertels — Wed, 25 Nov 2020 17:57:07 GMT

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 😉

Re: Add a field with conditions in file name on multiple files

thanhng34 — Thu, 26 Nov 2020 09:13:59 GMT

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 ?

Re: Add a field with conditions in file name on multiple files

thanhng34 — Thu, 26 Nov 2020 10:31:21 GMT

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

Re: Add a field with conditions in file name on multiple files

PrashantSangle — Thu, 26 Nov 2020 10:49:53 GMT

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()

if(wildmatch(filename(),'*adh*'),'adhesion'),if(wildmatch(filename(),'*affil*'),'affiliation'))) as activity

regards,

Prashant Sangle

Re: Add a field with conditions in file name on multiple files

thanhng34 — Thu, 26 Nov 2020 11:04:44 GMT

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

Re: Forced concatenation of multiple files

PrashantSangle — Thu, 26 Nov 2020 11:20:44 GMT

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

Re: Forced concatenation of multiple files

thanhng34 — Thu, 26 Nov 2020 11:43:08 GMT

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

Regards,

Thanh