Load multiple files

[evansabres]

I have a series of files in a directory that I want to import into my application. All of the files have the same column names, but have different names. For example abc_123_xyz, def_456_uvw and so on are the file names. In the data itself, the differentiation between files is a column named 'Event_Type'

Looking for recommendations on how to establish my script to load this data in the most efficient manner.

[uroboros]

Hi Evan, I suggest you that use an excel sheet, on it you can include the file names what do you need to load.

Or, another way, you can do a recursive load, moving all files on the same folder, and makin load * from path/*.txt (or the file extension).

Regards,

-M

[evansabres]

Sorry for the confusion. This directory will house an infinite number of files, so I need to load all files in the directory

[andrey_krylov]

Maybe this

for each File in 'abc_123_xyz', 'def_456_uvw'

    [Table]: Load * From $(File)

next File

or this

for each FoundFile in filelist( Root & '\*.' & FileExtension)

     IF Condition THEN

: Load * From $(FoundFile)

     ENDIF

next FoundFile

[uroboros · Accepted solution]

Sure Evan, if there in your folder, you can load all files, indifferent their names; try to load with a recursive load,

the statement is:

LOAD * FROM (PATH)*.(File extension);

If all fields have the same name, it will be create one table. if not, for every different field create a new table.

To force it to be concatenate it on one table, in the second case, you need one cycle, just like this.

TbTemp:

Load 1 as tempFielda autogenerate 0;

For each File in filelist (<Path, for example C:\documents\*>)

     Concatenate(TbTemp)

     load * from file;

Next File;

[evansabres]

I used the following, which seemed to work

Directory 'Q:\Data';

For each ExcelFile in filelist ('*.xlsx')

Data:

Load * From $(ExcelFile) (ooxml, embedded labels, table is [Data]);

Next ExcelFile;

STORE Data INTO (qvd);