Concatenate question

[#1]

Hello,

I have an issue that hopefully you can help me with. I'm loading about 100 files from a folder using this automated script:

For each vFile in FileList('t:\CONTABILITATE\Detaliutichete*.dbf')

QUALIFY *;

$(vFile):

load

  if(left(right(filebasename(),6),1), 1, 0) &

  left(right(filebasename(),5),1) as cifra2,

  date(date#(right(filebasename(),4), 'YYYY'),'YYYY') as an

FROM $(vFile);

join

$(vFile):

  SQL SELECT *

FROM $(vFile);

next vFile;

First part creates 2 fields from the file name (month and year) while the second part loads the existing fields from the tables .

Any idea how I can concatenate the files?

[#2]

Hello Henric,

I think your solution with the preceding load will work best. The only piece missing from this puzzle is the name of the file. You were right, filebasename() only fires if I use the statement from the first post.

If I use preceding load, filebasename() stops firing.

For each vFile in FileList('t:\CONTABILITATE\Detaliutichete*.dbf')
     Let vFileBaseName = filebasename();

     Data:
     LOAD *,
         '$(vFileBaseName)' as an;
     SQL SELECT *
           FROM $(vFile);

Next vFile;

Subfield is out of the question I presume because my filenames do not have a delimiter.

So what can I use instead of filebasename that will give me the same thing?

[hic · Accepted solution]

I think you're almost there. If you use

For each vFile in FileList('t:\CONTABILITATE\Detaliutichete*.dbf')

     Let vFileBaseName = SubField(Subfield(vFile, '\', -1), '.', 1);

     Let vFileNumber = KeepChar(vFileBaseName,'0123456789');

     Data:

     LOAD *,

         '$(vFileNumber)' as an;

     SQL SELECT *

           FROM $(vFile);

Next vFile;

Then you will indeed load all files with the number in the field "an".

You should use Subfield to get vFileBaseName, but you cannot use subfield to just get the number. But you can get the number by using Mid() or KeepChar().

HIC

[#4]

Henric,

This is gold. I've been struggling with this for quite some time now, but with your help I managed to make it work. I'm in your debt. Thanks a million

Bogdan