Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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
Sorry for the confusion. This directory will house an infinite number of files, so I need to load all files in the directory
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
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;
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);